Click here to Skip to main content
12,076,206 members (50,084 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

35.8K views
34 bookmarked
Posted

Useful DateTime Functions

, 5 Mar 2015 CPOL
Rate this:
Please Sign up or sign in to vote.
Reminder.Sou...

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 week by ISO
SELECT DATEADD(dd,7*(DATEDIFF(dd,'1973-01-01','2015-01-04')/7),'1973-01-01') 'First day of ISO Week'

----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 Quarter
SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0) 'First Day of Current Quarter'
----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' <font color="#111111" face="Segoe UI, Arial, sans-serif"><span style="font-size: 14px; white-space: normal;">
</span></font>

License

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

Share

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.

You may also be interested in...

Comments and Discussions

 
GeneralMy Vote of 5 Pin
aarif moh shaikh6-Mar-15 21:36
professionalaarif moh shaikh6-Mar-15 21:36 
QuestionPossible Bug Pin
BrianCharles6-Mar-15 8:55
memberBrianCharles6-Mar-15 8:55 
GeneralMy vote of 1 Pin
RedDk5-Mar-15 8:14
memberRedDk5-Mar-15 8:14 
GeneralVote of 5 Pin
Clark Kent1235-Mar-15 2:35
professionalClark Kent1235-Mar-15 2:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.160208.1 | Last Updated 5 Mar 2015
Article Copyright 2015 by db_developer
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid