SQL server dates in different formats

Here is the list of conversions that facilitates retrieving date time in different formats in T - SQL.

Replace Date in Convert with your field Name.

'YYYYMMDD'

CONVERT(CHAR(8), Date, 112)

'YYYY-MM-DD'

CONVERT(CHAR(10), Date, 23)

'YYMMDD'

CONVERT(VARCHAR(8), Date, 12)

'YY-MM-DD'

STUFF(STUFF(CONVERT(VARCHAR(8), Date, 12),
5, 0, '-'), 3, 0, '-')

'MMDDYY'

REPLACE(CONVERT(CHAR(8), Date, 10), '-', SPACE(0))

'MM-DD-YY'

CONVERT(CHAR(8), Date, 10)

'MM/DD/YY'

CONVERT(CHAR(8), Date, 1)

'MM/DD/YYYY'

CONVERT(CHAR(10), Date, 101)

'DDMMYY'

REPLACE(CONVERT(CHAR(8), Date, 3), '/', SPACE(0))

'DD-MM-YY'

REPLACE(CONVERT(CHAR(8), Date, 3), '/', '-')

'DD/MM/YY'

CONVERT(CHAR(8), Date, 3)

'DD/MM/YYYY'

CONVERT(CHAR(10), Date, 103)

'HH:MM:SS 24'

CONVERT(CHAR(8), Date, 8)

'HH:MM 24'

LEFT(CONVERT(VARCHAR(8), Date, 8), 5)

'HH:MM:SS 12'

LTRIM(RIGHT(CONVERT(VARCHAR(20), Date, 22), 11))

'HH:MM 12'

LTRIM(SUBSTRING(CONVERT(
VARCHAR(20), Date, 22), 10, 5)
+ RIGHT(CONVERT(VARCHAR(20), Date, 22), 3))

For more SQL tips and tricks , Subscribe here or click here to get updates via email

Posted in: Microsoft,SQL server