Understanding Date and Time Functions and Operations in SQL Server
page 2 of 8
by Abhishek Kumar Singh
Feedback
Average Rating: 
Views (Total / Last 10 Days): 54504/ 146

Parts of DATETIME in Microsoft SQL Server

SQL Server supports mainly the following list of options for DATEPART function:

                          Datepart

                      Abbreviations

Year

yy, yyyy

Quarter

qq, q

Month

mm, m

Dayofyear

dy, y

Day

dd, d

Week

wk, ww

Weekday

dw

Hour

hh

Minute

mi, n

Second

ss, s

Millisecond

ms

In Microsoft SQL Server the default language is "us-english" which has date format as Year-Month-Date (ymd).

Default base date : Jan 1, 1900

To understand this with examples, execute the following queries. I have added the result and description for each query below.

To convert Numeric value to DateTime

<span class=Bold>Query
 </span>DECLARE @dtDate AS DATETIME
 SET @dtDate = 2;
 SELECT @dtDate AS RESULT

Result
1900-01-03 00:00:00.000

Reason
It adds 2 days to the base date.

Query

SET @dtDate = 2.266;
SELECT @dtDate AS RESULT
Or,
SET @dtDate = CONVERT(DATETIME,2.266);
SELECT @dtDate AS RESULT

Result

1900-01-03 06:23:02.400

Reason
SQL Server adds 2 days in the base date and distributes remaining value 0.266 in hours, minutes, seconds and milliseconds. The entire approach of calculation is given below:

1900-01-01    + 2 days       = 1900-01-03         (Day)

24                * .266           = 6.384                  (Hours)

60                * .384           = 23.04                  (Minutes)

60                * .04            = 2.4                     (Seconds)

1000             * .4              = 400                     (Milliseconds)

Taking all the whole numbers (shown in bold format) the result in date time format becomes:

1900-01-03 06:23:02.400.

View Entire Article

User Comments

Title: datetime in sqlserver   
Name: murali
Date: 2010-11-11 12:52:06 AM
Comment:
How to alter the datetime to date conversion
Title: Great!!   
Name: Shyam
Date: 2009-12-30 12:33:12 AM
Comment:
Thank you for your nice article.
it Gives me a better understanding to know time Manipulation Function!
Title: awesome   
Name: mahesh
Date: 2009-05-29 4:15:11 PM
Comment:
great job it helped me a lot thank you very much!!!!!!
Title: Great!!!   
Name: Gourik Kumar Bora
Date: 2009-03-31 3:21:57 AM
Comment:
Hi,
Thanks a lot. quite a helpful article..
Title: set datefirst inside function   
Name: ll
Date: 2008-11-17 7:24:10 AM
Comment:
how to use set datefirst inside function
Title: Thanks.   
Name: Abhishek (Author)
Date: 2008-11-13 8:05:34 AM
Comment:
Thanks to all of you for appreaciating my article.
Title: Thanks a Lot   
Name: Krishna Prabhu
Date: 2008-11-13 4:43:50 AM
Comment:
Thanks a lot for giving this artical.It helped in completing project
Title: Thnx a Ton   
Name: Neha Mishra
Date: 2008-11-12 2:15:52 AM
Comment:
Thnx a ton for such a detailed and informatic article. It helped in clearing almost all my doubts.
Title: Thank you   
Name: Vishal s.surana
Date: 2008-11-11 7:44:52 AM
Comment:
Thank you for your nice article.It is very helpful to understand differnt behaviour of datetime datatype.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-21 5:27:15 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search