How to create a Date in SQL Server given the Day Month and Year as Integers

Question

FOR Example if I have    DECLARE  Day int   25 DECLARE  Month int    10  DECLARE  Year int   2016   I want to return  2016-10-25   As Date or datetime

User · Answer

CREATE DATE USING MONTH YEAR IN SQL    DECLARE  FromMonth int NULL   ToMonth int NULL   FromYear int NULL   ToYear int NULL     Region For Create Date            DECLARE  FromDate DATE NULL         DECLARE  ToDate DATE NULL      SET  FromDate DateAdd day 0  DateAdd month   FromMonth - 1 DateAdd Year   FromYear-1900  0        SET  ToDate DateAdd day -1  DateAdd month   ToMonth - 0 DateAdd Year   ToYear-1900  0       Region For Create Date

User · Answer

So  you can try this solution   DECLARE  DAY INT   25 DECLARE  MONTH INT    10  DECLARE  YEAR INT   2016 DECLARE  DATE AS DATETIME  SET  DATE   CAST RTRIM  YEAR   10000    MONTH   100    DAY  AS DATETIME    SELECT REPLACE CONVERT VARCHAR 10    DATE  102         -   AS EXPECTDATE   Or you can try this a few lines of code   DECLARE  DAY INT   25 DECLARE  MONTH INT    10  DECLARE  YEAR INT   2016  SELECT CAST RTRIM  YEAR   10000   -      MONTH   100   -     DAY  AS DATE  AS EXPECTDATE

User · Answer

Old Microsoft Sql Sever   lt  2012   RETURN dateadd month  12    year    month - 22801   day - 1

User · Answer

The following code should work on all versions of sql server I believe     SELECT CAST CONCAT CAST  Year AS VARCHAR 4     -  CAST  Month AS VARCHAR 2     -  CAST  Day AS VARCHAR 2    AS DATE

User · Answer

In SQL Server 2012   you can use datefromparts     select datefromparts  year   month   day    In earlier versions  you can cast a string   Here is one method   select cast cast  year 10000    month 100    day as varchar 255   as date

User · Answer

For SQL Server 2008 users  I made a custom function  CREATE FUNCTION sql2012 datefromparts           Year int   Month int   Day int   RETURNS DATETIME AS BEGIN     RETURN convert datetime convert varchar  year  right  0  convert varchar  month  2  right  0  convert varchar  day  2    END GO  To use it  DECLARE  day int 29   month int 10   year int 1971 SELECT dbo sql2012 datefromparts  year  month  day

User · Answer

In SQL Server 2012   you can use DATEFROMPARTS     DECLARE  Year int   2016   Month int   10   Day int   25  SELECT DATEFROMPARTS   Year   Month   Day     In earlier versions  one method is to create and convert a string   There are a few string date formats which SQL Server reliably interprets regardless of the date  language  or internationalization settings      A six- or eight-digit string is always interpreted as ymd  The month   and day must always be two digits    https   docs microsoft com en-us sql t-sql data-types datetime-transact-sql  So a string in the format  yyyymmdd  will always be properly interpreted    ISO 8601-- YYYY-MM-DDThh mm ss-- also works  but you have to specify time and therefore it s more complicated than you need    While you can simply CAST this string as a date  you must use CONVERT in order to specify a style  and you must specify a style in order to be deterministic  if that matters to you    The  yyyymmdd  format is style 112  so your conversion looks like this   DECLARE  Year int   2016   Month int   10   Day int   25  SELECT CONVERT date CONVERT varchar 50    Year 10000    Month 100    Day   112     And it results in      2016-10-25   Technically  the ISO 112 yyyymmdd format works even with other styles specified  For example  using that text format with style 104  German  dd mm yyyy    DECLARE  Year int   2016   Month int   10   Day int   25  SELECT CONVERT date CONVERT varchar 50    Year 10000    Month 100    Day   104     Also still results in      2016-10-25   Other formats are not as robust  For example this   SELECT CASE WHEN CONVERT date  01-02-1900  110    CONVERT date  01-02-1900  105  THEN 1 ELSE 0 END    Results in      0   As a side note  with this method  beware that nonsense inputs can yield valid but incorrect dates   DECLARE  Year int   2016   Month int   0   Day int   1025  SELECT CONVERT date CONVERT varchar 50    Year 10000    Month 100    Day   112     Also yields      2016-10-25   DATEFROMPARTS protects you from invalid inputs  This   DECLARE  Year int   2016   Month int   10   Day int   32  SELECT DATEFROMPARTS   Year   Month   Day     Yields      Msg 289  Level 16  State 1  Line 2 Cannot construct data type date    some of the arguments have values which are not valid    Also beware that this method does not work for dates prior to 1000-01-01  For example   DECLARE  Year int   900   Month int   1   Day int   1  SELECT CONVERT date CONVERT varchar 50    Year 10000    Month 100    Day   112     Yields      Msg 241  Level 16  State 1  Line 2 Conversion failed when converting   date and or time from character string    That s because the resulting string   9000101   is not in the  yyyymmdd  format  To ensure proper formatting  you d have to pad it with leading zeroes  at the sacrifice of some small amount of performance  For example   DECLARE  Year int   900   Month int   1   Day int   1  SELECT CONVERT date RIGHT  000    CONVERT varchar 50    Year 10000    Month 100    Day   8  112     Results in      0900-01-01   There are other methods aside from string conversion  Several are provided in answers to  Create a date with T-SQL   A notable example involves creating the date by adding years  months  and days to the  zero date     This answer was inspired by Gordon Linoff s answer  which I expanded on and provided additional documentation and notes

User · Answer

select convert varchar 11   transfer date  106    got me my desired result of date formatted as 07 Mar 2018  My column  transfer date  is a datetime type column and I am using SQL Server 2017 on azure

[sql] How to create a Date in SQL Server given the Day, Month and Year as Integers

Examples related to sql

Examples related to sql-server