There are several date time data types present in SQL server.
- Time
- date
- datetime
- smalldatetime
- datetime2
- datetimeoffset
Time
When we require only the time in the application we can make use of time data type. Time data type consumes 3-5 bytes of memory.
Date
When we want to store only the Date we can make use of Date data type. Date data type uses 3 bytes of memory.
DateTime
When we want both the date and time to be stored in database we can make use of datetime data type. DateTime data type uses 8 bytes of memory,
SmallDateTime
Small datetime stores the datetime. The time is based on 24 hours per day with seconds always: 00
DateTime2
Datetime2 is extension of datetime that can have larger date range
DateTimeOffset
DateTimeOffset is a datetime with UTC offset. YYYY-MM-DD hh:mm:ss[.nnnnnnn][+|-]hh:mm
UTC: Coordinated Universal Time is a standard that is used across the world based on which world regulates the clock and time. UTC is basically world standard time and based on this time other country time is calculated.
Date and Time Function in SQL server
Current TimeStamp:
Returns the db system timestamp without the db time offset. ANSI SQL equivalent to GETDATE
SELECT CURRENT_TIMESTAMP
Output: 2017-05-16 22:41:23.237
Getdate():
returns the current system date and time.
Syntax:
SELECT GETDATE() AS CURRENT_DATE_TIME
Output: 2017-05-16 22:43:38.420
Sysdatetime:
returns a date and time datetime(7) of db. It returns more fractional seconds precisions
select Sysdatetime()
Output: 2017-05-16 22:52:19.7178978
Sysdatetimeoffset()
returns the database system timestamp with database time zone offset
select Sysdatetimeoffset()
Output: 2017-05-16 22:53:12.6469252 +05:30
Sysutcdatetime()
returns system datetime without zone offset as datetime2. It returns more fractional seconds precisions.
select Sysutcdatetime()
Output: 2017-05-16 17:28:13.8181512
Sysdatetimeoffset()
returns system datetime with zone offset UTC and more fractional seconds precisions.
select Sysdatetimeoffset()
Output : 2017-05-16 22:58:48.2061181 +05:30
ISDate(varchar expression)
The isdate function determines whether the passed expression is a valid date or not?
It takes a character expression and returns the 1 if it’s a valid date and 0 if not as shown below:
SELECT ISDATE(GETDATE())
Output: 1
SELECT ISDATE(‘SAILLESH’)
Output: 0
ISDate function returns 0 for datetime2 or datetimeoffset as shown below:
select ISDATE(‘2017-05-16 22:33:33.6570000’)
Output: 0
select ISDATE(‘2017-05-16 22:33:33.6570000 +00:00’)
Output: 0
Day() returns the day number of the date of the specified date.
SELECT DAY(GETDATE())
Output: 17
DECLARE @DATE DATETIME
SET @DATE=CONVERT(DATETIME,’2017-05-16 22:33:33.657′,120)
SELECT DAY(@DATE)
Output: 16
Month() function returns the month of the specified date. It takes a datetime expression as a parameter and returns an integer value corresponding to the month.
SELECT MONTH(GETDATE())
Output: 5
DECLARE @DATE DATETIME
SET @DATE=CONVERT(DATETIME,’2017-05-16 22:33:33.657′,120)
SELECT MONTH(@DATE)
Output: 5
Year() function returns the year from the specified datetime passed in the expression.
SELECT YEAR(GETDATE())
Output: 2017
Datename() returns a string from the datetime expression based on the datepart from the datetime expression. Ex datepart can be a date, year, month.
Returning day from the datetime using DateName
SELECT DATENAME(DAY,GETDATE())
Output: 17
Retrieving weekday from the DateName method:
SELECT DATENAME(WEEKDAY,GETDATE())
Output: Wednesday
Retrieving name of the month from the DateName method:
SELECT DATENAME(MONTH,GETDATE())
Output: May
DatePart table
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 |
microsecond | mcs |
nanosecond | ns |
TZoffset | tz |
ISO_WEEK | isowk, isoww |
So we can use the datepart shortcut as well in place of full datepart as shown below:
Retrieving year from the datetime using DateName:
SELECT DATENAME(yy,GETDATE())
Output: 2017
DatePart() methods returns the integer representing the specified DatePart type of the passed date.
select datepart(month,getdate())
Output: 5
select datepart(year,getdate())
Output: 2017
DateAdd() function increment specified value to the specified DatePart to the dateTime value as shown below:
select Dateadd(MONTH,1,GETDATE())
Output: 2017-06-30 22:02:55.437
select Dateadd(YEAR,1,GETDATE())
Output: 2018-05-31 22:12:07.897
Passing negative value in parameter will result in subtraction or decreasing the DatePart.
select getdate();
select Dateadd(YEAR,-5,GETDATE());
Output:
DateDiff() function returns difference between two datetime.
select Datediff(month,’1-1-2017′,GETDATE())
Output: 4