How to format a date time in different ways

Problem: How to convert date time to different formats, change a value to different datatype.

Solution: CAST and CONVERT (Transact-SQL).

This two system functions help us to convert an expression of one data type to another.

Cast is ANSI Standard

CONVERT is SQL Specific.

Examples:

In this article I am going to show you how we can use cast and convert with some pretty good use cases.

Example 1:  Converting dates to different formats.

Convert Function gives us ability to style to different formats please see the below code

SELECT GETDATE(), CONVERT(VARCHAR(10),GETDATE(),1)   AS [mm/dd/yy]

SELECT GETDATE(), CONVERT(VARCHAR(10),GETDATE(),2)   AS [yy.mm.dd]

SELECT GETDATE(), CONVERT(VARCHAR(10),GETDATE(),3)   AS [dd/mm/yy]

SELECT GETDATE(), CONVERT(VARCHAR(10),GETDATE(),4)   AS [dd.mm.yy]

SELECT GETDATE(), CONVERT(VARCHAR(10),GETDATE(),5)   AS [dd-mm-yy]

SELECT GETDATE(), CONVERT(VARCHAR(10),GETDATE(),6)   AS [dd mon yy]

SELECT GETDATE(), CONVERT(VARCHAR(10),GETDATE(),7)   AS [Mon dd, yy]

SELECT GETDATE(), CONVERT(VARCHAR(10),GETDATE(),8)    AS [hh:mm:ss]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),9)    AS [mon dd yyyy]

SELECT GETDATE(), CONVERT(VARCHAR(10),GETDATE(),10)  AS [mm-dd-yy]

SELECT GETDATE(), CONVERT(VARCHAR(10),GETDATE(),11)  AS [yy/mm/dd]

SELECT GETDATE(), CONVERT(VARCHAR(10),GETDATE(),12)  AS [yymmdd]

SELECT GETDATE(), CONVERT(VARCHAR(24),GETDATE(),13)  AS [dd mon yyyy hh:mi:ss:mmm (24h)]

SELECT GETDATE(), CONVERT(VARCHAR(10),GETDATE(),14)  AS [hh:mi:ss:mmm]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),101)AS [mm/dd/yyyy]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),102)AS [yyyy.mm.dd]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),103)AS [dd/mm/yyyy]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),104)AS [dd.mm.yyyy]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),105)AS [dd-mm-yyyy]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),106)AS [dd mon yyyy]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),107)AS [Mon dd, yyyy]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),108)AS [hh:mm:ss]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),109)AS [mon dd yyyy]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),110)AS [mm-dd-yyyy]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),111)AS [yyyy/mm/dd]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),112)AS [yyyymmdd]

SELECT GETDATE(), CONVERT(VARCHAR(24),GETDATE(),113)AS [dd mon yyyy hh:mi:ss:mmm (24h)]

SELECT GETDATE(), CONVERT(VARCHAR(14),GETDATE(),114)AS [hh:mi:ss:mmm]

  

OUTPUT:

Example 2: casting a GUID to VARCHAR

declare @stguid UNIQUEIDENTIFIER

SET @stguid ='352C4D33-DAFC-48D0-928E-2175AED720ED'

select CAST(@stguid as VARCHAR(36))

I am Specifying 36 as a varchar length when casting to fit the default size of a GUID. If you go any lower this can give you insufficient space error while converting.

OUTPUT: