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:
