SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Format Date Time Function

By Kraai,

The Script is used to get  datetime in comminly used formats, it also contains some basic date time features.

If anyone wants to add more options , they are more than likely to do so, please just mention my name in the script.

use :  select dbo.[KDT_FN_FORMATDATETIME]('YYYY-MM-DD',getdate())
       select dbo.[KDT_FN_FORMATDATETIME]('LASTDAY DDDD',getdate())
SELECT dbo.KDT_FN_FORMATDATETIME('Dayofyear', GETDATE()+200)
SELECT dbo.KDT_FN_FORMATDATETIME('LASTDAY DDDD', GETDATE()+245)
SELECT dbo.KDT_FN_FORMATDATETIME('YYYY-MM-DD', GETDATE()+245)
Purpose- Converting  To different datetime formats and relavant date parts 
Parameters | Result
________________________________________________________________________
dd-mmm-yyyy |   22-OCT-2008          
MMM DD YYYY HH-mm Am/pm | Oct 22 2008  6-02AM
MM/DD/YYYY | 10/22/2008
YYYY.MM.DD | 2008.10.22
DD/MM/YYYY | 22/10/2008
DD.MM.YYYY | 22.10.2008
DD/MM/YYYY | 22/10.2008
DD-MM-YYYY | 22-10-2008
DD MMM YYYY | 22 Oct 2008
MMM DD,YYYY |   Oct 22, 2008
HH-mm-SS | 06-33-22
MMM DD YYYY H-mm-SS-MSAM/PM | Oct 22 2008  6-12-30-540AM
DD-MM-YYYY |   22-10-2008
YYYY/MM/DD | 2008/10/22
YYYYMMDD | 20081022
DD MMM YYYY HH-mm-SS-MS | Oct 22 2008  6-12-30-540AM (will actually by colon, but we can use here since install cd breaks)
HH-mm-SS-MS | 06-18-30-740
Julion | 39741
Day |   22
Month |   10
Year |   2008
YYYY-MM |   2008-10
MM-YYYY |   10-2008
YYYY MM | 2008 10
MM YYYY |   10 2008
Week |   43 week in year 
Dayofyear |   293 day in year 
quarter |   4 Year Quarter
Weekday |   4 day number of week
MMMM | October 
DDDD |   Wednesday
DDDD DD MMMM YYYY |   Wednesday 22 October 2008
DDDD MMMM YYYY | Wednesday  October 2008
LASTDAY |   2008/10/31 (last day of month)
LASTDAY DDDD |   Friday (last day of month)
YYYY-MM-DD |   2008-11-11 

Total article views: 919 | Views in the last 30 days: 6
 
Related Articles
FORUM

select from GETDATE()?

select from GETDATE()?

FORUM

select 21/(datediff(dd,getdate(),getdate())

select 21/(datediff(dd,getdate(),getdate())

FORUM

GetDate Function

Getdate() as default

FORUM

Issue with trying to incorporate the following SELECT DATEPART(WEEKDAY, GETDATE())

Issue with trying to incorporate the following SELECT DATEPART(WEEKDAY, GETDATE())

FORUM

help using getdate function

help using getdate function

Tags
datetime    
function    
 
Contribute