we will discuss DATEFROMPARTS function in SQL Server
DATEFROMPARTS function
- Introduced in SQL Server 2012
- Returns a date value for the specified year, month, and day
- The data type of all the 3 parameters (year, month, and day) is integer
- If invalid argument values are specified, the function returns an error
- If any of the arguments are NULL, the function returns null
Example : All the function arguments have valid values, so DATEFROMPARTS returns the expected date
SELECT DATEFROMPARTS ( 2015, 10, 25) AS [Date]
Output :
![datefromparts function in sql server](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyCkBXMRBCnEB88Vsuw-7aw-43dOXUGkRia5ccZ8E39TH8w1boNXcg9qyPMEh0BkpoL1kAVtV2de5iZ3jOwSUDsGXWFfG4wUWnnQnDCyGkYzteWIaygQC7mI4SLXUGCK9I_jHLjHMOnxrZ/s1600/datefromparts+function+in+sql+server.png)
Example : Invalid value specified for month parameter, so the function returns an error
SELECT DATEFROMPARTS ( 2015, 15, 25) AS [Date]
Output : Cannot construct data type date, some of the arguments have values which are not valid.
Example : NULL specified for month parameter, so the function returns NULL.
SELECT DATEFROMPARTS ( 2015, NULL, 25) AS [Date]
Output :
![datefromparts in sql server](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6paqzZbAvaRn9OHADL-7mpIvOfcJITj-F32etTM41vCUbe_yREWIic5Pi7LXQIBqgJ7_6EBxB6s6m_m-AylbmxQAg0Gabff_MVvFZAfa6OoDw4m-9n4GYaVKNPaj8W8PqFSe8x6EVoop5/s1600/datefromparts+in+sql+server.png)
Other new date and time functions introduced in SQL Server 2012
- EOMONTH (Discussed in Part 125 of SQL Server tutorial)
- DATETIMEFROMPARTS : Returns DateTime
- Syntax : DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
- SMALLDATETIMEFROMPARTS : Returns SmallDateTime
- Syntax : SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
- We will discuss the following functions in a later video
- TIMEFROMPARTS
- DATETIME2FROMPARTS
- DATETIMEOFFSETFROMPARTS
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.