DATETIME functions in sql server:-
- Datetime data types
- Datetime functions
- Understand UTC time and time zone offset.
Datetime :-
UTC Stands for coordinated
universal time, based on which , the world regulates clock and time. There are
slight differences between GMT and UTC, But for most comman purposes, UTC is
synonymous with GMT
DATETIME:-
Data type
|
Format
|
Range
|
Accuracy
|
Storage size(Bytes)
|
Time
|
hh:mm:ss
[.nnnnnnn]
|
00:00:00:0000000 through 23:59:59.9999999
|
100 nanoseconds
|
3 to 5
|
Date
|
YYYY-MM-DD
|
0001-01-01 through 9999-12-31
|
1 day
|
3
|
SmallDateTime
|
YYYY-MM-DD
|
1900-01-01 through 2079-06-06
|
1 minute
|
4
|
Datetime
|
YYYY-MM-DD hh:mm:ss
[.nnn]
|
1753-01-01 through 9999-12-31
|
0.00333 second
|
8
|
DateTime2
|
YYYY-MM-DD
hh:mm:ss
[.nnnnnnn]
|
0001-01-01 00:00:00.0000000
Through 9999-12-31
23:59:59.9999999
|
100
Nan0seconds
|
6 to 8
|
DateTimeOffset
|
YYYY-MM-DD
hh:mm:ss
[.nnnnnnn]
[+|-]hh:mm
|
01-01-01 00:00:00
0000000 through 9999-12-31
23:59:59.9999999(in
UTC)
|
100 nanoseconds
|
8 to 10
|
Function
|
Date Time Format
|
Description
|
GETTIME()
|
2012-08-31
20:15:04.543
|
Commanly used
|
CURRENT_TIMESTAMP
|
2012-08-31
20:15:04.543
|
ANSI
SQL equilvalent to GETDATE
|
SYSDATETIME()
|
2012-08-31
20:15:04.5380028
|
More
fractional seconds precision
|
SYSDATETIMEOFFSET()
|
2012-08-31
20:15:04.5380028+01:00
|
More
fractional seconds precision+Time zone offset
|
GETUTCDATE()
|
2012-08-31
20:15:04.543
|
UTC Date and Time
|
SYSUTCDATETIME()
|
2012-08-31
20:15:04.5380028
|
UTC Date and Time , with more fractional
seconds precision
|
Datetime use in some sql
functions:
Datepart
|
abbreviation
|
|
Year
|
yy, yyyy
|
It returns the total days in year form the
current date
|
quarter
|
qq,q
|
it returns the what time in your system(quarter time)
|
month
|
mm, m
|
it returns the current month in
system machine
|
Dayofyear
|
dy,y
|
it returns the total day from the current year
|
Day
|
dd,d
|
it returns the current date from the system
|
week
|
wk, ww
|
it returns the current week from the system
|
weekday
|
dw
|
it returns the current day in week from the system(like
sun=1,mon=2,tue=3)
|
Hour
|
hh
|
it returns the current hour from the system
|
minute
|
mi, n
|
It returns the current minute from the system
|
millisecond
|
Ms
|
it returns the current
millisecond from the system
|
microsecond
|
mcs
|
it returns the current microsecond from the system
|
nanosecond
|
Ns
|
it returns the current nenosecond
from the system
|
TZoffset
|
Tz
|
Using query:-
Examples:-
select DATEPART(Y,GETDATE())
---its returns the total count days form the
current date
--ouput 33 days in year now
select DATEPART(YY,GETDATE())
---it return the current year from your system
--output 2016 current year
select DATEPART(YYYY,GETDATE())
---it return the current year from your system
same as
-- output 2016 current year
select DATEPART(Q,GETDATE())
select DATEPART(QQ,GETDATE())
select DATEPART(QUARTER,GETDATE())
---it returns the what time in your system(quarter
time)
-- output 1 (count every 6 hours)
select DATEPART(MONTH,GETDATE())
select DATEPART(M,GETDATE())
select DATEPART(MM,GETDATE())
---it returns the current month in system machine
--output 2 current date
select DATEPART(D,GETDATE())
select DATEPART(DD,GETDATE())
---it returns the current date from the system
---output 2 current date
select DATEPART(DY,GETDATE())
select DATEPART(DY,GETDATE())
---it returns the total day from the current year
---output 139 days in year form current date
select DATEPART(DW,GETDATE())
select DATEPART(W,GETDATE())
---it returns the current date from the system
--output 3 week day count(1-7)
select DATEPART(WEEK,GETDATE())
select DATEPART(WK,GETDATE())
select DATEPART(WW,GETDATE())
---it returns the current date from the system
---output 6 it return week number from year like
(1-52)
select DATEPART(HH,GETDATE())
---it returns the current hour from the system
--output 9 clock(1-24)in current system time
select DATEPART(MI,GETDATE())
---it returns the current minute from the system
---output 3 minute(0-59)in current system time
select DATEPART(ss,GETDATE())---
select DATEPART(S,GETDATE())
---it returns the current second from the system
---output 3 second(0-59)in current system time
select DATEPART(MS,GETDATE())
---it returns the current millisecond from the
system
---output 670 millisecond in current system time
select DATEPART(MCS,GETDATE())
---it returns the current microsecond from the
system
---output 647000 in microsecond in your current
system time
select DATEPART(NS,GETDATE())
---it returns the current nenosecond from the
system
---output 347000000 nanosecond in your current
system time
ISDATE () FUNCTION- it Checks if the
given value is a valid date, time, or date time. Returns 1 for success, 0 for
failure.
For Example -:
select ISDATE('kush')
-- output
-> 0
select ISDATE(GETDATE())
-- output
-> 1
select ISDATE ('2016-01-30 21:24:04.163')
-- output
-> 1
select ISDATE ('2016-01-30
09:27:04.1631238')
--Output Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '2016-01-30
09:27:04.1631238'.
DAY, MONTH, AND YEAR:-
DAY ():- Returns the ‘Day
number of the month’ of the given date.
Ex-
select day (getdate())---R
select DAY('01/30/2016')---Returns day date
MONTH ():- Returns the
‘month number of the year’ of the given date.
Ex- select MONTH(getdate())---Returns the current
month number of month
select MONTH('01/30/2016')---Returns months
YEAR ():- Returns the ‘year number of the given date.
Ex - select YEAR(getdate())---Returns the current
number of YEAR
select YEAR('01/30/2016')---Returns YAER
DATENAME:-Returns a string, that represents a part of
given date. This function take 2 parameters. The first parameter specify datepart
specifies, the part of the date, we want. The second parameter, is the actual
date, from which we want the part of the date.
Ex- select DATENAME(DAY,'2016-01-31
11:54:23.3232323')
---returns integer
select DATENAME(WEEKDAY,'2016-01-31 11:54:23.3232323')
---returns day in string
select DATENAME(MONTH,'2016-01-31
11:54:23.3232323')
---returns month in string
if we want to proper way use in all datetime functions:-
Ex- select name,DOB,DATENAME(weekday,DOB) as [day],
MONTH(DOB) as monthnumber,
DATENAME (month,(DOB)) as [monthname],
year(DOB)as [year]
from datetimetable
DatePart, DateAdd and
DateDiff:-
DatePart
(datepart, date):- Returns an integer representing the
specified DatePart. This function is similar to DateName (). DateName returns
nvarchar, where as DatePart () returns an integer.
Ex- select DATEPART(WEEKDAY,'2016-01-31
02:25:11.1223452')
select DATEPART(MONTH,'2016-01-31
02:25:11.1223452')
Ex-
select DATENAME(WEEKDAY,'2016-01-31
02:25:11.1223452')
select DATENAME(MONTH,'2016-01-31
02:25:11.1223452')
DATEADD(DatePart,
numberToAdd, date ):- Returns the datetime, after adding
specified numberToAdd, to the DatePart specified of the given date.
Ex-
select DATEADD(DAY,31,'2016-01-31 02:25:11.122')
select DATEADD(DAY,-20,'2016-01-31 02:25:11.122')
DATEDIFF
(Datepart, startdate, enddate):- returns the count of the
specified DatePart boundaries crossed between the specified startdate and
enddate.
Syntax:-
Ex-
select DATEDIFF(YEAR,'05/06/1992','01/02/2016')
--it returns year
output:-
select DATEDIFF(MONTH,'05/06/1992','01/02/2016')
select DATEDIFF(DAY,'05/06/1992','01/02/2016')
select DATEDIFF(HOUR,'05/06/1992','01/02/2016')
0 comments:
Post a Comment