Skip to content
Home » SQL DATEFROMPARTS

SQL DATEFROMPARTS

SQL DATEFROMPARTS() function is a date function that returns a date value that maps to a year, month, and day values.




SYNTAX

DATEFROMPARTS (year, month, day)

The DATEFROMPARTS function accepts three parameters:

Year is an integer

Month is an integer, any month value between 1 to 12.

Day is an integer, any day between  1 to 31

Lets look at an example of using DATEFROMPARTS function in SQL.

Following DATEFROMPARTS function returns a date for given month, year, and day .

declare @yr as int ,@month as int ,@day as int

set @yr =2017
set @month =12
set @day =31
select DATEFROMPARTS(@yr, @month, @day) as Date

 

To get a date from given year, month, and day without using a DATEFROMPARTS function

To get a date from given year, month, day without using a DATEFROMPARTS function, you can use following T-SQL Code.

declare @yr as int ,@month as int ,@day as int

set @yr =2017
set @month =12
set @day =31
select CAST(@yr  as varchar)  + ‘-‘ + cast ( @month as varchar) + ‘-‘ +  cast ( @day as varchar) as date

DATEFROMPARTS function returns null, if any of year, month or day value is null

declare @yr as int, @month as int, @day as int
set @yr = 2017
set @month = null
set @day = 31

select DATEFROMPARTS(@yr, @month, @day) as Date

DATEFROMPARTS returns an error for any invalid day, month and year value.




Lets pass an invalid value for month that is 34 to DATEFORMPARTS function.

declare @yr as int, @month as int, @day as int
set @yr =2017
set @month = 34
set @day = 31

select DATEFROMPARTS(@yr, @month, @day) as Date

As you can see, it gives an error saying cannot construct data type date, some of the arguments have values which are not valid.

Also Read..

SQL DateName()

SQL DateAdd

SQL DateDiff

Loading

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.