Click here to Skip to main content
Click here to Skip to main content

Useful DateTime Functions

By , 3 Jan 2014
Rate this:
Please Sign up or sign in to vote.

Reminder.

Source

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

--Start of Current Day
SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) 'Start of Current Day'
--End of Current day
SELECT DATEADD(ms,-3,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)) 'End of Current day' 

--Start of Yesterday
SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1) 'Start of Yesterday'
--End of Yesterday
SELECT DATEADD(ms,-3,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)) 'End of Yesterday' 

----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) '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))) 'Last Day of Current Month'
----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'
----Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'
----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) '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))) 'Last Day of Current Year'
----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'
----Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year' 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

db_developer
Database Developer Freelancer
Ukraine Ukraine
MS SQL Server Database Developer with 7+ years experience
 
Technologies/languages: Business Intelligence, SQL, MDX, VBA, SQL Server, Analysis Services (SSAS), Reporting services (SSRS), Integration Services (SSIS), DataWarehouse.
Also: economic background.
 
Feel free to contact me for rates and details.

Comments and Discussions

 
QuestionYou look backwards Pinmemberdigimanus3-Jan-14 4:45 
GeneralMy vote of 5 Pinmemberchetan patidar25-Jul-12 0:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 3 Jan 2014
Article Copyright 2010 by db_developer
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid