Ready reckoner – Date Functions in SQL Server

This blog post tries to provide few of very common scenarios and its solutions that developers would come across in development phase.

Standard Date CONVERT formats

Declare @Date Datetime = GETDATE()
Select @Date
SELECT DATEADD(month, DATEDIFF(month, '', @date), '');

Select CONVERT(Varchar(25),@date,100) as 'Converted Value', 'Mon DD YYYY HH:MIAM (or PM)' as 'Converted Format' union all
Select CONVERT(Varchar(25),@date,1),'MM/DD/YY' union all
Select CONVERT(Varchar(25),@date,101),'MM/DD/YYYY' union all
Select CONVERT(Varchar(25),@date,2),'YY.MM.DD' union all
Select CONVERT(Varchar(25),@date,102),'YYYY.MM.DD' union all
Select CONVERT(Varchar(25),@date,3),'DD/MM/YY' union all
Select CONVERT(Varchar(25),@date,103),'DD/MM/YYYY' union all
Select CONVERT(Varchar(25),@date,4),'DD.MM.YY' union all
Select CONVERT(Varchar(25),@date,104),'DD.MM.YYYY' union all
Select CONVERT(Varchar(25),@date,5),'DD-MM-YY' union all
Select CONVERT(Varchar(25),@date,105),'DD-MM-YYYY' union all
Select CONVERT(Varchar(25),@date,6),'DD Mon YY' union all
Select CONVERT(Varchar(25),@date,106),'DD Mon YYYY' union all
Select CONVERT(Varchar(25),@date,7),'Mon DD, YY' union all
Select CONVERT(Varchar(25),@date,107),'Mon DD, YYYY' union all
Select CONVERT(Varchar(25),@date,108),'HH:MM:SS' union all
Select CONVERT(Varchar(25),@date,109),'Mon DD YYYY HH:MI:SS:MMMAM (or PM)' union all
Select CONVERT(Varchar(25),@date,10),'MM-DD-YY' union all
Select CONVERT(Varchar(25),@date,110),'MM-DD-YYYY' union all
Select CONVERT(Varchar(25),@date,11),'YY/MM/DD' union all
Select CONVERT(Varchar(25),@date,111),'YYYY/MM/DD' union all
Select CONVERT(Varchar(25),@date,12),'YYMMDD' union all
Select CONVERT(Varchar(25),@date,112),'YYYYMMDD' union all
Select CONVERT(Varchar(25),@date,113),'DD Mon YYYY HH:MM:SS:MMM(24h)' union all
Select CONVERT(Varchar(25),@date,114),'HH:MI:SS:MMM(24H)' union all
Select CONVERT(Varchar(25),@date,120),'YYYY-MM-DD HH:MI:SS(24h)' union all
Select CONVERT(Varchar(25),@date,121),'YYYY-MM-DD HH:MI:SS.MMM(24h)' union all
Select CONVERT(Varchar(25),@date,126),'YYYY-MM-DDTHH:MM:SS:MMM' union all
Select CONVERT(nVarchar(25),@date,130),'DD Mon YYYY HH:MI:SS:MMMAM' union all
Select CONVERT(Varchar(25),@date,131),'DD/MM/YYYY HH:MI:SS:MMMAM'

How to separate date from a string

Create table testing(id int identity(1,1), enddate varchar(40))

insert into testing values('End Date: 04/30/2021')
insert into testing values('06/30/2027')
insert into testing values('something something something 06/30/2027')
insert into testing values('13.a')
insert into testing values('Section 23')
insert into testing values('End Date: 06/30/2027')
insert into testing values('End Date: 12/31/2016 a as asdadasd')

;With cte as
(
select ID, enddate,
SUBSTRING(enddate, patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%', enddate), 10) AS Actualdate
FROM testing
)
select id,enddate,
case when Isdate(right(Actualdate,10)) = 1 then Actualdate
else null end Actualdate from cte

Drop table testing

How to calculate age from a date

create table students(student_id int,DOB date)

Insert into students Values(1,'19790111'),(2,'19790219'),(3,'20200408')

Select student_id,DOB,Cast(getdate() as date),
cast((DATEDIFF(m, DOB, GETDATE())/12) as varchar) as years,
cast((DATEDIFF(m, DOB, GETDATE())%12) as varchar) as ' Months',
cast((DATEDIFF(d, DOB, GETDATE())%365) as varchar) as ' Days'
From students

Drop table students

If you enjoyed this blog post, feel free to share it with your friends!

One thought on “Ready reckoner – Date Functions in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s