Post
Ask Question

DateTime Functions in SQL Server 2012

DateTime Functions

  • DATEFROMPARTS (year, month, day)
  • DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
  • DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
  • DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
  • SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
  • TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
  • EOMONTH (start_date)

DateFromParts

This function returns a date for the specified year, month, and day.

DATEFROMPARTS ( YEAR,MONTH,DAY )

  • Year: Year value in integer
  • Month: Month value in integer, between 1 and 12
  • Day: Day value in integer, between 1 and 31

Returns: Date

Let us see an example of how to use this function. Before Microsoft SQL Server 2012, when we want to format a date with year, month and day, we used to do that like this:

  1. declare @year int=2014  
  2. declare @month int=4  
  3. declare @day int=8  
  4. SELECT Date=Convert(datetime,convert(varchar(10),@year)+'-'+convert(varchar(10),@day)+'-'+convert(varchar(10),@month),103)  
Result
 
 
 
But with SQL Server 2012, the datefromparts function will make this simple.
  1. declare @year int=2014  
  2. declare @month int=4  
  3. declare @day int=8  
  4. select date=DATEFROMPARTS(@year,@month,@day)  
Result
 
 
 
Note: Remember it returns date and not datetime.
 
DateTimeFromParts

Remember the last line I said, the DateFromParts function will only return you the date. So what if I need to get a datetime value from year, month, day and time as well? That's where the DateTimeFromParts function is useful.This function returns a datetime for the specified year, month, day, hour, minute, second, and precision.
 
DATETIMEFROMPARTS(year, month, day, hour, minute, seconds,milliseconds )
  • Year: year value in integer
  • Month: month value in integer, between 1 and 12
  • Day: day value in integer, between 1 and 31
  • Hour: hour value in integer
  • Minute: minute value in integer
  • Seconds: seconds value in integer
  • Milliseconds: milliseconds value in integer
Returns: DateTime
 
Consider the following query.
  1. declare @year int=2014  
  2. declare @month int=4  
  3. declare @day int=8  
  4. declare @hour int=5  
  5. declare @minute int=35  
  6. declare @seconds int=34  
  7. declare @milliseconds int=567  
  8. select date=DATETIMEFROMPARTS(@year,@month,@day,@hour,@minute,@seconds,@milliseconds)  
Result
 
 
 
DateTime2FromParts
 
This is similar to the preceding function but the difference is here we can set the precision for the time part and this function returns DateTime2.
 
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
  • year: year value in integer
  • month: month value in integer, between 1 and 12
  • day: day value in integer, between 1 and 31
  • hour: hour value in integer
  • minute: minute value in integer
  • fractions: fractions value in integer
  • precision: precision value in integer
Return: DateTime2
 
Consider this following query:
  1. declare @year int=2014  
  2. declare @month int=4  
  3. declare @day int=8  
  4. declare @hour int=5  
  5. declare @minute int=35  
  6. declare @seconds int=34  
  7. select date=DATETIME2FROMPARTS(@year,@month,@day,@hour,@minute,@seconds,0,0)  
Here I am setting the fraction and precision both to 0. So the output will be:
 
 
 
In the preceding query I am just changing the precision to 2. Let us see what happens:
  1. declare @year int=2014  
  2. declare @month int=4  
  3. declare @day int=8  
  4. declare @hour int=5  
  5. declare @minute int=35  
  6. declare @seconds int=34  
  7. select date=DATETIME2FROMPARTS(@year,@month,@day,@hour,@minute,@seconds,0,2)  
Result
 
 
SmallDateTimeFromParts
 
This function returns a smalldatetime value for the specified year, month, day, hour, and minute.
SMALLDATETIMEFROMPARTS (year, month, day, hour, minute )
  • Year: year value in integer
  • Month: month value in integer, between 1 and 12
  • Day: day value in integer, between 1 and 31
  • Hour: hour value in integer
  • Minute: minute value in integer
Return: SmallDateTime.
 
Consider this following query.
  1. declare @year int=2014  
  2. declare @month int=4  
  3. declare @day int=8  
  4. declare @hour int=5  
  5. declare @minute int=35  
  6. select date=SmallDatetimeFromparts(@year,@month,@day,@hour,@minute)  
The output will be:
 
 
 
DateTimeOffsetFromParts
 
This function returns a datetimeoffset value for the specified date and time. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.
DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
  • Year: year value in integer
  • Month: month value in integer, between 1 and 12
  • Day: day value in integer, between 1 and 31
  • Hour: hour value in integer
  • Minute: minute value in integer
  • Seconds: seconds value in integer
  • fractions: fractions value in integer
  • Hour_offset: hour portion of the time zone offset in integer
  • Minute_offset: minute portion of the time zone offset in integer
  • Precision: precision value in integer
Return: DateTimeOffset.
 
The offset arguments are used to represent the time zone offset. If the offset arguments are omitted then the time zone offset is assumed to be 00:00, that is, there is no time zone offset. If the offset arguments are specified then both arguments must be present and both must be positive or negative.
 
Consider the following query:
  1. declare @year int=2014  
  2. declare @month int=4  
  3. declare @day int=8  
  4. declare @hour int=5  
  5. declare @minute int=35  
  6. declare @seconds int=45  
  7. select date=DATETIMEOFFSETFROMPARTS(@year,@month,@day,@hour,@minute,@seconds,567,12,40,3)  
The output is:
 
 
 
TimeFromParts
 
This function returns a time value for the specified hour, minute, seconds, fractions, and precision.

TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
  • Hour: hour value in integer
  • Minute: minute value in integer
  • Seconds: seconds value in integer
  • fractions: fractions value in integer
  • precision: precision value in integer
Return: Time.
 
Consider this following query.
  1. declare @hour int=5  
  2. declare @minute int=35  
  3. declare @seconds int=45  
  4. select date=TIMEFROMPARTS(@hour,@minute,@seconds,567,3)  
The output will be:
 
 
 
EOMonth

If you ask me, I will say that this is one of the important functions in the new version of SQL Server for datetime. You will understand why after I explain this. How will you calculate the last date for the current month in SQL Server 2008?
  1. declare @date1 datetime=getdate()  
  2. select dateadd(month,datediff(month,-1, @date1),-1)  
Result
 
 
 
Now in SQL Server 2012, a new method is introduced to make this simple which is EOMONTH.
 
EOMONTH ( start_date [, month_to_add ] )
  • Start_Date: the date for which end date for the month to be calculated
  • Month_to_Add: number of months to add to the start_date; this is an optional parameter.

So EOMONTH will return the date, that is the last date of the month entered. I am writing another query for the preceding queries but using EOMONTH.

  1. Select EOMONTH(getdate()) EnodOfthismonth  
Result
 
 
 
In case you want to know the last date for last month or the month before that or next month, then we need to use the optional parameter Month_to_Add.
  1. Select EOMONTH(getdate(),-1) as lastmonth  
  2. Select EOMONTH(getdate(),-2) as monthbeforethat  
  3. select EOMONTH(getdate(),1) as nextmonth  
Result
 
 
 
Use of the EOMONTH() function to find the last day of the month:
  1. SELECT EOMONTH(GETDATE()) LastDayofMonth  
Result

 

Use of the EOMONTH() function to find the last day of the month during Leap Year:
  1. SELECT EOMONTH('20110201') NonLeapYearFebLastDay;  
  2. SELECT EOMONTH('20120201') LeapYearFebLastDay;  
  3. SELECT EOMONTH('20130201') NonLeapYearFebLastDay;  
Result
 
 
 
Use of the EOMONTH() function to find the last day of the previous and next month:
  1. SELECT DATENAME(dw,EOMONTH(GETDATE())) LastDayofMonthDay;  
Result

 

Summary
 
This article described all the new DateTime functions introduced in SQL Server 2012 with examples.


Similar Articles