Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL SERVER | How to Create a Date using Day, Month and Year | Multiple Methods

Here we are showing what are the multiple ways to create a Date using Day, Month and Year. For more details please see the sample input and expected output.

Sample Input

DECLARE @Year INT = 2018, @Month INT = 05, @Day INT = 31

Expected Output

TheDate
2018-05-31 00:00:00.000

Script – DDL and INSERT Sample Data

--

DECLARE @Year INT = 2018, @Month INT = 05, @Day INT = 31

--

SOLUTION – 1

--

--2012+
DECLARE @Year INT = 2018, @Month INT = 05, @Day INT = 31
SELECT CAST(DATEFROMPARTS(@Year,@Month,@Day) AS DATETIME) TheDate


--

Output – 1

--

TheDate
-----------------------
2018-05-31 00:00:00.000

(1 row affected)

--

SOLUTION – 2

--

--Before 2012
DECLARE @Year INT = 2018, @Month INT = 05, @Day INT = 31
SELECT DATEADD(mm, (@Year - 1900) * 12 + @Month- 1 , @Day - 1) TheDate

--

Output – 2

--

TheDate
-----------------------
2018-05-31 00:00:00.000

(1 row affected)

--

SOLUTION – 3

--

--Before 2012
DECLARE @Year INT = 2018, @Month INT = 05, @Day INT = 31
SELECT CAST(CAST(@Year*10000 + @Month*100 + @Day AS VARCHAR(MAX)) AS DATETIME) TheDate

--

Output – 3

--

TheDate
-----------------------
2018-05-31 00:00:00.000

(1 row affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements