SQL format date

 

Author: myssh

 

-- Use of style when converting character to date

-- 1. When style = 101, the date string is in mm/DD/YYYY format.
Select convert (datetime, '2014/1/123', 11/1)
-- Result: 00:00:00. 000

-- 2. When style = 101, the date string is in the DD/MM/YYYY format.
Select convert (datetime, '2014/1/123', 11/1)
-- Result: 00:00:00. 000

/* = Convert date to string = */
Declare @ DT datetime
Set @ dt = '2017-1-11'

-- 1. When style = 101, it indicates converting the date to mm/DD/YYYY format.
Select convert (varchar, @ DT, 101)
-- Result: 01/11/2003

-- 2. When style = 103, it indicates converting the date to the DD/MM/YYYY format.
Select convert (varchar, @ DT, 103)
-- Result: 11/01/2003

/* = This is a common mistake that many people make. For non-date conversions, use the date style = */
Select convert (varchar, '2017-1-11 ', 2003)
-- Result: 2003-1-11

 

 

 

 

author: myssh

declare @ DT datetime
set @ dt = getdate ()

-- 1. short Date Format: yyyy-m-D
select Replace (convert (varchar (10), @ DT, 120), N'-0 ','-')

-- 2. long Date Format: Mm DD, yyyy
--. method 1
select stuff (convert (char (8), @ DT, 112), 5, 0, N 'Year'), 8, 0, N 'month ') + N'
-- B. method 2
select datename (year, @ DT) + N 'Year' + datename (month, @ DT) + N 'month' + datename (day, @ DT) + N'

-- 3. long Date Format: yyyy-m-D
select datename (year, @ DT) + N 'Year' + Cast (datepart (month, @ DT) as varchar) + N 'month' + datename (day, @ DT) + N'

-- 4. complete date + time format: yyyy-mm-dd hh: MI: SS: Mmm
select convert (char (11), @ DT, 120) + convert (char (12), @ DT, 114)