//
you're reading...
SQL Server

SQL server Date Time Function

Datetime functions allow manipulating columns with DATETIME/SMALLDATETIME data types. SQL server DateTime Function:

GETDATE and GETUTCDATE Functions

GETDATE and GETUTCDATE Functions are Nondeterministic function. Both functions returns the current date and time. GETDATE returns current system date and time of the computer where SQL Server is running.

GETUTCDATE returns current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

DATEADD Functions

DATEADD function is Deterministic function. DATEADD Function adds a certain interval of time to the specified date and time value.

Syntax: DATEADD (datepart , number, date )

DATEADD returns a new date time value based on adding an interval to the specified date and time value.

DATEDIFF Function

DATEDIFF function is Deterministic function. DATEDIFF () gives the difference between the two date values.

Syntax: DATEDIFF ( datepart , startdate , enddate )

DATEDIFF returns number of date and time boundaries crossed between two specified dates. In DATEDIFF function start date is subtracted from end date. If start date is later than end date, a negative value is returned.

DATEPART Function

To retrieve any part of date and time use DATEPART function.

Syntax: DATEPART ( datepart , date )

DATEPART function takes two arguments 1)part of the date that you want to retrieve and 2)date itself. The DATEPART function returns an integer that represents date part of specified date.

DATEPART Output
SELECT DATEPART(year, ‘2009-02-13 18:35:06.523’) 2009
SELECT DATEPART(quarter, ‘2009-02-13 18:35:06.523’) 1
SELECT DATEPART(month, ‘2009-02-13 18:35:06.523’) 2
SELECT DATEPART(dayofyear, ‘2009-02-13 18:35:06.523’) 44
SELECT DATEPART(day, ‘2009-02-13 18:35:06.523’) 13
SELECT DATEPART(week, ‘2009-02-13 18:35:06.523’) 7
SELECT DATEPART(weekday, ‘2009-02-13 18:35:06.523’) 6
SELECT DATEPART(hour, ‘2009-02-13 18:35:06.523’) 18
SELECT DATEPART(minute, ‘2009-02-13 18:35:06.523’) 35
SELECT DATEPART(second, ‘2009-02-1 18:35:06.523’) 6
SELECT DATEPART(millisecond, ‘2009-02-1 18:35:06.523’) 523

DATENAME Function

DATENAME Function returns a character string that represents date part of the specified date.

Syntax: DATENAME ( datepart , date )

DATENAME function takes two arguments same as DATEPART function 1) part of the date that you want to retrieve and 2) date itself.

If you want the name of month
SELECT DATENAME (month, ‘2009-02-13 18:35:06.523’)  => Output : February

If you want the name of week day
SELECT DATENAME (weekday, ‘2009-02-13 18:35:06.523’)  => Output : Friday

DAY, MONTH, and YEAR Functions

All of this DAY, MONTH, and YEAR functions takes a single date value as a argument. Each of this function returns an integer that represents respective portions of the date.

SELECT   DAY(‘2009-02-1 18:35:06.523’) as ‘Day’, MONTH(‘2009-02-1 18:35:06.523’) as ‘Month’, YEAR(‘2009-02-1 18:35:06.523’) as ‘Year’

Each of this functions equivalent to DATEPART function. Like DAY is equivalent to DATEPART (dd, date), MONTH is equivalent to DATEPART (mm, date) and YEAR is equivalent to DATEPART (yy, date).

DATETIME FORMAT

In SQL Server used Cast or Convert function to Format DateTime value or column into a specific date format.Both function are used to convert datetime to varchar or string.

CAST function Syntax: CAST(expression as data_type)

Let’s convert current date time to varchar

select cast(getdate() as varchar)

CONVERT function is used to change or convert the DateTime formats.By using convert function you can get only Date part or only Time part from the datetime.

CONVERT Function Syntax: CONVERT(data_type,expression,date Format style)

Let’s take Sql Server DateTtime styles example:

Format Query
USA mm/dd/yy select convert(varchar, getdate(), 1)
ANSI yy.mm.dd select convert(varchar, getdate(), 2)
British/French dd/mm/yy select convert(varchar, getdate(), 3)
German dd.mm.yy select convert(varchar, getdate(), 4)
Italian dd-mm-yy select convert(varchar, getdate(), 5)
dd mon yy select convert(varchar, getdate(), 6)
Mon dd, yy select convert(varchar, getdate(), 7)
USA mm-dd-yy select convert(varchar, getdate(), 10)
JAPAN yy/mm/dd select convert(varchar, getdate(), 11)
ISO yymmdd select convert(varchar, getdate(), 12)
mon dd yyyy hh:miAM (or PM) select convert(varchar, getdate(), 100)
mm/dd/yyyy select convert(varchar, getdate(), 101)
yyyy.mm.dd select convert(varchar, getdate(), 102)
dd/mm/yyyy select convert(varchar, getdate(), 103)
dd.mm.yyyy select convert(varchar, getdate(), 104)
dd-mm-yyyy select convert(varchar, getdate(), 105)
dd mon yyyy select convert(varchar, getdate(), 106)
Mon dd, yyyy select convert(varchar, getdate(), 107)
hh:mm:ss select convert(varchar, getdate(), 108)
Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) select convert(varchar, getdate(), 109)
mm-dd-yyyy select convert(varchar, getdate(), 110)
yyyy/mm/dd select convert(varchar, getdate(), 111)
yyyymmdd select convert(varchar, getdate(), 112)
Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13)
hh:mi:ss:mmm(24h) select convert(varchar, getdate(), 114)

 

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

December 2011
M T W T F S S
« Nov   Jan »
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: