Tuesday, January 17, 2012

Some simple datetime functions to find a specific date

Right now I am going to give some examples that I have been using a lot to determine a specific date in SQL Server.

The most used and I think everyone should know is the function to get the date for today.

SELECT GETDATE())  AS 'Today'


Today
-------------------------
2012-01-17 12:54:33.167


What day was it yesterday?

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



Yesterday
-------------------------
2012-01-16 12:54:33.167



Select the first day of the current week.

SELECT DATEADD(wkDATEDIFF(wk0GETDATE()), 0AS 'First Day of Current Week'


First Day of Current Week
-------------------------
2012-01-16 00:00:00.000

Select the last day of the current week.

SELECT DATEADD(wkDATEDIFF(wk0GETDATE()), 6AS 'Last Day of Current Week'

Last Day of Current Week
-------------------------
2012-01-22 00:00:00.000

Select the first Day of the current month.

SELECT DATEADD(mmDATEDIFF(mm0GETDATE()), 0AS 'First Day of Current Month'



First Day of Current Month
-------------------------
2012-01-01 00:00:00.000

Select the last day the the current month.

SELECT DATEADD(ms-3, DATEADD(mm0DATEADD(mmDATEDIFF(mm0GETDATE()), 0))) AS 'Last Day of Current Month'

Last Day of Current Month
-------------------------
2012-01-31 23:59:59.997


Select the first day of the current year.

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

First Day of Current Year
-------------------------
2012-01-01 00:00:00.000

Select the last day of the current year.

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

Last Day of Current Year
------------------------
2012-12-31 23:59:59.997

Select the first day of the last year.

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

First Day of Last Year
-----------------------
2011-01-01 00:00:00.000

Select the last day of the last year.

SELECT  DATEADD(ms-3, DATEADD(yy0, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)))  AS 'Last Day of Last year'
Last Day of Last Year
-----------------------
2011-12-31 23:59:59.997





No comments:

Post a Comment