:::: MENU ::::

Posts Categorized / Date and Time

  • Nov 21 / 2008
  • 0
Date and Time, dbDigger, SSMS tips and tricks, System Functions, T-SQL Scripts

T-SQL DateTime functions to find various dates

Recently i found a very handy collection of various T-SQL DateTime functions to find different dates. I would list those here as it as for my daily use and you may also take benefit of this handy collection.

 --Today  
 SELECT GETDATE() 'Today'  


 --Yesterday  
 SELECT  
 DATEADD(d,-1,GETDATE())  
 AS 'Yesterday'  


 --First Day of Current Week  
 SELECT  
 DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)  
 AS 'First Day of Current Week'

  
 --Last Day of Current Week  
 SELECT  
 DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)  
 AS 'Last Day of Current Week'  


 --First Day of Last Week  
 SELECT  
 DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)  
 AS 'First Day of Last Week'  


 --Last Day of Last Week  
 SELECT  
 DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)  
 AS 'Last Day of Last Week'  


 --First Day of Current Month  
 SELECT  
 DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)  
 AS 'First Day of Current Month'  


 --Last Day of Current Month  
 SELECT  
 DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))  
 AS 'Last Day of Current Month'

  
 --First Day of Last Month  
 SELECT  
 DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))  
 AS 'First Day of Last Month'  


 --Last Day of Last Month  
 SELECT  
 DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))  
 AS 'Last Day of Last Month'  


 --First Day of Current Year  
 SELECT  
 DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)  
 AS 'First Day of Current Year' 

 
 --Last Day of Current Year  
 SELECT  
 DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))  
 AS 'Last Day of Current Year'  


 --First Day of Last Year  
 SELECT  
 DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))  
 AS 'First Day of Last Year' 

 
 --Last Day of Last Year  
 SELECT  
 DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))  
 AS 'Last Day of Last Year'  
  • Oct 22 / 2008
  • 4
Date and Time, dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Get years, month and days difference togeather between two dates

Suppose you have two dates and you are required to get difference in following form
00 years, 00 months and 00 days.
Normally we may get year, month, days etc alone through
datediff(yy/mm/dd, firstDate, secondDate) function. But in this case we are required to get Year, month and days difference is required together.
So get date in above format use following T-SQL script

 
SELECT '2007-04-25' as [FirstDate], getdate() as [SecondDate],  
 CONVERT(varchar(6),datediff(yy,'2007-04-25',getdate())) +  
 ' Years, '+  
 CONVERT(varchar(4),datediff(mm, '2007-04-25',getdate())%12)+  
 ' Months and '+  
 CONVERT(varchar(4),datediff(dd, '2007-04-25',getdate())%30)+  
 ' Days' as [RequiredDifference]  
 GO  
Consult us to explore the Databases. Contact us