DateTime GetDate Datepart DateDiff DateTime2 DATEPART DATEADD Function in SqlServer

DATETIME  functions in sql server:-

1. Datetime data types
2. Datetime functions
3. 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')