Category: Date & Time

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!

Find Max and Min value of dates in a row in SQL Server

Problem Statement:
How to identify the Max and Min date value in a row in SQL Server?

Code:
Here is a solution to identify Max and Min date value with the help of CROSS APPLY in SQL Server.


create Table FindMaxMindate
(
    ID int, 
    Firstdate datetime,
    Seconddate datetime, 
    ThirdDate datetime
)

Insert into FindMaxMindate 
Values
(1,'2018-01-26 19:14:07.587','2018-04-26 19:14:07.587','2018-12-26 19:14:07.587'),
(2,'2018-12-26 19:14:07.587','2018-01-26 19:14:07.587','2018-03-26 19:14:07.587')

Select ID,Min(Dates) MinDate,Max(Dates) MaxDate
From FindMaxMindate A
Cross apply (values (Firstdate),(SecondDate),(ThirdDate))B (Dates)
Group by ID

Drop Table FindMaxMinDate

CURRENT_TIMESTAMP in SQL Server

CURRENT_TIMESTAMP is a system function to retrieve the current system date and time. CURRENT_TIMESTAMP is an ANSI SQL function whereas GETDATE is the T-SQL version of that same function. That says it is a non-deterministic function. If you are very specific to ANSI comaptibility or thinking of heterogenous system support, I would suggest to use CURRRENT_TIMESTAMP instead of GETDATE(). As an envision, I prefer to use CURRENT_TIMESTAMP over GETDATE() personally wherever possible.

Interesting facts:

1. When we create a default constaint with CURRENT_TIMESTAMP, by default, SSMS properties are showing GETDATE().


My thought: This could be because SSMS properties have nothing to do with ANSI compatibility nor hetrogeneous support.

2. Tool tip text of the function adds parenthesis – ()

My thought: I have no clue why Microsoft has made this so.Its more concerned to me the function with “()” gives an error as below.

First and Last funda with dates in SQL Server

This post is to provide scripts to identify FIRST and LAST usages for a given date.


--How to get first day of a month 
Declare @Date Datetime = GETDATE()
SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0);

--How to get Last day of a month 
Declare @Date Datetime = GETDATE()
SELECT DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(m,0,@Date)+1,0));

--How to get first day of a Calendar Year 
DECLARE @DATE DATETIME = GETDATE()
SELECT DATEADD(YY, DATEDIFF(YY,0,@DATE), 0) AS STARTOFYEAR

--How to get Last day of a Calendar Year 
DECLARE @DATE DATETIME = GETDATE()
SELECT DATEADD(YY, DATEDIFF(YY,0,@DATE) + 1, -1) AS ENDOFYEAR

I will add more usages of FIRST/LAST day to the above list as I come across in future.