Get Date From DateTime In SQL

Something I need to do from time to time is get just the date part of a datetime value in SQL.

select convert(varchar,DateColumn,101)

The 101 means “mm/dd/yyyy” format, but there are a bunch of other codes you can use. 108 will return just the time “hh:mm:ss” for instance.

Update: 101 includes 4 digit year ‘yyyy’. A code of 1 would apparently be “mm/dd/yy”, according to user comments. Thanks!

SQL Server DateTime Formats

The SQL Server DateTime and smallDateTime data types are ideal for storing date and time information, but you need to know how to deal with them to effectively use them. This article goes over the fuctions provided by SQL Server to manipulate datetime.

Functions provided

SQL Server 2000 and 2005 has provided some useful functions to deal with datetime. Some of there functions are:

GETDATE Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values. Syntax: GETDATE()
DATEADD Returns a new datetime value based on adding an interval to the specified date. Syntax: DATEADD (datepart, number, date)
DATEDIFF Returns the number of date and time boundaries crossed between two specified dates. Syntax: DATEDIFF(datepart, startdate, enddate)
DATENAME Returns a character string representing the specified datepart of the specified date. Syntax: DATENAME(datepart, date)
DATEPART Returns an integer representing the specified datepart of the specified date.Syntax: DATEPART (datepart, date)
DAY Returns an integer representing the day datepart of the specified date. Syntax: DAY (date)
MONTH Returns an integer that represents the month part of a specified date. Syntax: MONTH (date)
YEAR Returns an integer that represents the year part of a specified date. Syntax: YEAR (date)

SQL Server stores the datetime data type internally as two 4 byte integers and smalldatetime as two 2 byte integers. The first integer in both the cases stores the number of day difference from the base date.

The second integer part stores the number of milliseconds/minutes since midnight.

SQL Server’s GetDate()

GetDate() returns the current system date and time in the Microsoft SQL Server standard internal format for datetime values.

DateTime Formats/Styles

The list of styles that can be applied are:

 

SQL Style ID SQL Style Example
0 or 100 mon dd yyyy hh:miAM (or PM) Jan 15 2008 5:44PM
101 mm/dd/yy 01/15/2008
102 yy.mm.dd 2008.01.15
103 dd/mm/yy 15/01/2008
104 dd.mm.yy 15.01.2008
105 dd-mm-yy 15-01-2008
106 dd mon yy 15 Jan 2008
107 Mon dd, yy Jan 15, 2008
108 hh:mm:ss 17:42:33
9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM) Jan 15 2008 5:42:09:953PM
110 mm-dd-yy 01-15-2008
111 yy/mm/dd 2008/01/15
112 yymmdd (no spaces) 20080115
13 or 113 dd mon yyyy hh:mm:ss:mmm(24h) 15 Jan 2008 17:37:13:163
114 hh:mi:ss:mmm(24h) 17:37:52:407
20 or 120 yyyy-mm-dd hh:mi:ss(24h) 2008-01-15 17:38:20
21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h) 2008-01-15 17:38:44.867
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces) 2008-01-15T17:39:12.193

 

Apply a datetime format using VarChar Conversion

When a DateTime value is converted to a VarChar string value a format may be applied.  Format ‘111’ gives the year, month and date without a time in a format that can easily be converted back to a DateTime.

SELECT Convert (VARCHAR, GetDate (),111) — Should return 2008/01/15

To test the sample code, simply run it within Query Analyser, SQL Server Management Studio or any other tool for executing SQL queries.

 

 

Advertisements

Posted on December 13, 2006, in Web Dev. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: