Create a date from day month and year with T-SQL

Question

I am trying to convert a date with individual parts such as 12  1  2007 into a datetime in SQL Server 2005  I have tried the following   CAST DATEPART year  DATE   -   DATEPART month  DATE    -   DATEPART day  DATE  AS DATETIME    but this results in the wrong date  What is the correct way to turn the three date values into a proper datetime format

User · Answer

Try this query       SELECT SUBSTRING CONVERT VARCHAR JOINGDATE 103  7 4 AS     YEAR SUBSTRING CONVERT VARCHAR JOINGDATE 100  1 2 AS MONTH SUBSTRING CONVERT VARCHAR JOINGDATE 100  4 3 AS DATE FROM EMPLOYEE1   Result   2014    Ja    1 2015    Ja    1 2014    Ja    1 2015    Ja    1 2012    Ja    1 2010    Ja    1 2015    Ja    1

User · Answer

Or using just a single dateadd function   DECLARE  day int   month int   year int SELECT  day   4   month   3   year   2011  SELECT dateadd mm    year - 1900    12    month - 1    day - 1

User · Answer

You can also use   select DATEFROMPARTS year  month  day  as ColDate  Col2  Col3  From MyTable Where DATEFROMPARTS year  month  day  Between  DateIni and  DateEnd   Works in SQL since ver 2012 and AzureSQL

User · Answer

I know the OP is asking for SQL 2005 answer but the question is pretty old so if you re running SQL 2012 or above you can use the following   SELECT DATEADD DAY  1  EOMONTH  somedate  -1     Reference  https   docs microsoft com en-us sql t-sql functions eomonth-transact-sql view sql-server-2017 amp viewFallbackFrom sql-server-previousversions

User · Answer

I add a one-line solution if you need a datetime from both date and time parts   select dateadd month    Year -1900  12    Month -1   DayOfMonth -1    dateadd ss   Hour 3600    Minute 60    Second  0    dateadd ms   Millisecond  0

User · Answer

Assuming y  m  d are all int  how about   CAST CAST y AS varchar     -    CAST m AS varchar     -    CAST d AS varchar  AS DATETIME    Please see my other answer for SQL Server 2012 and above

User · Answer

For SQL Server versions below 12 i can recommend use of CAST in combination with SET DATEFORMAT  -- 26 February 2015 SET DATEFORMAT dmy SELECT CAST  26-2-2015  AS DATE   SET DATEFORMAT ymd SELECT CAST  2015-2-26  AS DATE    how you create those strings is up to you

User · Answer

If you don t want to keep strings out of it  this works as well  Put it into a function    DECLARE  Day int   Month int   Year int SELECT  Day   1   Month   2   Year   2008  SELECT DateAdd dd   Day-1  DateAdd mm   Month -1  DateAdd yy   Year - 2000   20000101

User · Answer

You can also use   select DATEFROMPARTS year  month  day  as ColDate  Col2  Col3  From MyTable Where DATEFROMPARTS year  month  day  Between  DateIni and  DateEnd   Works in SQL since ver 2012 and AzureSQL

User · Answer

Assuming y  m  d are all int  how about   CAST CAST y AS varchar     -    CAST m AS varchar     -    CAST d AS varchar  AS DATETIME    Please see my other answer for SQL Server 2012 and above

User · Answer

I personally Prefer Substring as it provide cleansing options and ability to split the string as needed  The assumption is that the data is of the format  dd  mm  yyyy    --2012 and above SELECT CONCAT           RIGHT REPLACE  date            4            -           RIGHT CONCAT  00  SUBSTRING REPLACE  date            CHARINDEX      REPLACE  date              1  LEN REPLACE  date            - CHARINDEX      REPLACE  date            - 5   2            -           RIGHT CONCAT  00  SUBSTRING REPLACE  date            1  CHARINDEX      REPLACE  date            - 1   2             --2008 and below SELECT   RIGHT REPLACE  date            4            -           RIGHT  00  SUBSTRING REPLACE  date            CHARINDEX      REPLACE  date              1  LEN REPLACE  date            - CHARINDEX      REPLACE  date            - 5  2            -           RIGHT  00  SUBSTRING REPLACE  date            1  CHARINDEX      REPLACE  date            - 1  2    Here is a demonstration of how it can be sued if the data is stored in a column  Needless to say  its ideal to check the result-set before applying to the column  DECLARE  Table TABLE  ID INT IDENTITY 1000 1   DateString VARCHAR 50   DateColumn DATE   INSERT INTO  Table SELECT 12  1  2007  NULL UNION SELECT 15 3  2007  NULL UNION SELECT 18  11   2007  NULL UNION SELECT 22   11  2007  NULL UNION SELECT 30  12  2007    NULL  UPDATE  Table SET DateColumn   CONCAT           RIGHT REPLACE DateString            4            -           RIGHT CONCAT  00  SUBSTRING REPLACE DateString            CHARINDEX      REPLACE DateString              1  LEN REPLACE DateString            - CHARINDEX      REPLACE DateString            - 5   2            -           RIGHT CONCAT  00  SUBSTRING REPLACE DateString            1  CHARINDEX      REPLACE DateString            - 1   2              SELECT ID DateString DateColumn FROM  Table

User · Answer

For SQL Server versions below 12 i can recommend use of CAST in combination with SET DATEFORMAT  -- 26 February 2015 SET DATEFORMAT dmy SELECT CAST  26-2-2015  AS DATE   SET DATEFORMAT ymd SELECT CAST  2015-2-26  AS DATE    how you create those strings is up to you

User · Answer

I personally Prefer Substring as it provide cleansing options and ability to split the string as needed  The assumption is that the data is of the format  dd  mm  yyyy    --2012 and above SELECT CONCAT           RIGHT REPLACE  date            4            -           RIGHT CONCAT  00  SUBSTRING REPLACE  date            CHARINDEX      REPLACE  date              1  LEN REPLACE  date            - CHARINDEX      REPLACE  date            - 5   2            -           RIGHT CONCAT  00  SUBSTRING REPLACE  date            1  CHARINDEX      REPLACE  date            - 1   2             --2008 and below SELECT   RIGHT REPLACE  date            4            -           RIGHT  00  SUBSTRING REPLACE  date            CHARINDEX      REPLACE  date              1  LEN REPLACE  date            - CHARINDEX      REPLACE  date            - 5  2            -           RIGHT  00  SUBSTRING REPLACE  date            1  CHARINDEX      REPLACE  date            - 1  2    Here is a demonstration of how it can be sued if the data is stored in a column  Needless to say  its ideal to check the result-set before applying to the column  DECLARE  Table TABLE  ID INT IDENTITY 1000 1   DateString VARCHAR 50   DateColumn DATE   INSERT INTO  Table SELECT 12  1  2007  NULL UNION SELECT 15 3  2007  NULL UNION SELECT 18  11   2007  NULL UNION SELECT 22   11  2007  NULL UNION SELECT 30  12  2007    NULL  UPDATE  Table SET DateColumn   CONCAT           RIGHT REPLACE DateString            4            -           RIGHT CONCAT  00  SUBSTRING REPLACE DateString            CHARINDEX      REPLACE DateString              1  LEN REPLACE DateString            - CHARINDEX      REPLACE DateString            - 5   2            -           RIGHT CONCAT  00  SUBSTRING REPLACE DateString            1  CHARINDEX      REPLACE DateString            - 1   2              SELECT ID DateString DateColumn FROM  Table

User · Answer

Try CONVERT instead of CAST   CONVERT allows a third parameter indicating the date format   List of formats is here  http   msdn microsoft com en-us library ms187928 aspx  Update after another answer has been selected as the  correct  answer   I don t really understand why an answer is selected that clearly depends on the NLS settings on your server  without indicating this restriction

User · Answer

It is safer and neater to use an explicit starting point  19000101   create function dbo fnDateTime2FromParts  Year int   Month int   Day int   Hour int   Minute int   Second int   Nanosecond int  returns datetime2 as begin     -- Note  SQL Server 2012 includes datetime2fromparts   function     declare  output datetime2    19000101      set  output   dateadd year         Year - 1900     output      set  output   dateadd month        Month - 1       output      set  output   dateadd day          Day - 1         output      set  output   dateadd hour         Hour            output      set  output   dateadd minute       Minute          output      set  output   dateadd second       Second          output      set  output   dateadd ns           Nanosecond      output      return  output end

User · Answer

I know the OP is asking for SQL 2005 answer but the question is pretty old so if you re running SQL 2012 or above you can use the following   SELECT DATEADD DAY  1  EOMONTH  somedate  -1     Reference  https   docs microsoft com en-us sql t-sql functions eomonth-transact-sql view sql-server-2017 amp viewFallbackFrom sql-server-previousversions

User · Answer

SQL Server 2012 has a wonderful and long-awaited new DATEFROMPARTS function  which will raise an error if the date is invalid - my main objection to a DATEADD-based solution to this problem    http   msdn microsoft com en-us library hh213228 aspx  DATEFROMPARTS ycolumn  mcolumn  dcolumn    or  DATEFROMPARTS  y   m   d

User · Answer

Try this   Declare  DayOfMonth TinyInt Set  DayOfMonth   13 Declare  Month TinyInt Set  Month   6 Declare  Year Integer Set  Year   2006 -- ------------------------------------ Select DateAdd day   DayOfMonth - 1             DateAdd month   Month - 1                 DateAdd Year   Year-1900  0      It works as well  has added benefit of not doing any string conversions  so it s pure arithmetic processing  very fast  and it s not dependent on any date format This capitalizes on the fact that SQL Server s internal representation for datetime and smalldatetime values is a two part value the first part of which is an integer representing the number of days since 1 Jan 1900  and the second part is a decimal fraction representing the fractional portion of one day  for the time  --- So the integer value 0  zero  always translates directly into Midnight morning of 1 Jan 1900      or  thanks to suggestion from  brinary   Select DateAdd yy   Year-1900           DateAdd m    Month - 1   DayOfMonth - 1      Edited October 2014  As Noted by  cade Roux  SQL 2012 now has a built-in function  DATEFROMPARTS year  month  day  that does the same thing   Edited 3 Oct 2016   Thanks to  bambams for noticing this  and  brinary for fixing it   The last solution  proposed by  brinary  does not appear to work for leap years unless years addition is performed first   select dateadd month   Month - 1        dateadd year   Year-1900   DayOfMonth - 1

User · Answer

Or using just a single dateadd function   DECLARE  day int   month int   year int SELECT  day   4   month   3   year   2011  SELECT dateadd mm    year - 1900    12    month - 1    day - 1

User · Answer

Try this   Declare  DayOfMonth TinyInt Set  DayOfMonth   13 Declare  Month TinyInt Set  Month   6 Declare  Year Integer Set  Year   2006 -- ------------------------------------ Select DateAdd day   DayOfMonth - 1             DateAdd month   Month - 1                 DateAdd Year   Year-1900  0      It works as well  has added benefit of not doing any string conversions  so it s pure arithmetic processing  very fast  and it s not dependent on any date format This capitalizes on the fact that SQL Server s internal representation for datetime and smalldatetime values is a two part value the first part of which is an integer representing the number of days since 1 Jan 1900  and the second part is a decimal fraction representing the fractional portion of one day  for the time  --- So the integer value 0  zero  always translates directly into Midnight morning of 1 Jan 1900      or  thanks to suggestion from  brinary   Select DateAdd yy   Year-1900           DateAdd m    Month - 1   DayOfMonth - 1      Edited October 2014  As Noted by  cade Roux  SQL 2012 now has a built-in function  DATEFROMPARTS year  month  day  that does the same thing   Edited 3 Oct 2016   Thanks to  bambams for noticing this  and  brinary for fixing it   The last solution  proposed by  brinary  does not appear to work for leap years unless years addition is performed first   select dateadd month   Month - 1        dateadd year   Year-1900   DayOfMonth - 1

User · Answer

Try this   Declare  DayOfMonth TinyInt Set  DayOfMonth   13 Declare  Month TinyInt Set  Month   6 Declare  Year Integer Set  Year   2006 -- ------------------------------------ Select DateAdd day   DayOfMonth - 1             DateAdd month   Month - 1                 DateAdd Year   Year-1900  0      It works as well  has added benefit of not doing any string conversions  so it s pure arithmetic processing  very fast  and it s not dependent on any date format This capitalizes on the fact that SQL Server s internal representation for datetime and smalldatetime values is a two part value the first part of which is an integer representing the number of days since 1 Jan 1900  and the second part is a decimal fraction representing the fractional portion of one day  for the time  --- So the integer value 0  zero  always translates directly into Midnight morning of 1 Jan 1900      or  thanks to suggestion from  brinary   Select DateAdd yy   Year-1900           DateAdd m    Month - 1   DayOfMonth - 1      Edited October 2014  As Noted by  cade Roux  SQL 2012 now has a built-in function  DATEFROMPARTS year  month  day  that does the same thing   Edited 3 Oct 2016   Thanks to  bambams for noticing this  and  brinary for fixing it   The last solution  proposed by  brinary  does not appear to work for leap years unless years addition is performed first   select dateadd month   Month - 1        dateadd year   Year-1900   DayOfMonth - 1

User · Answer

If you don t want to keep strings out of it  this works as well  Put it into a function    DECLARE  Day int   Month int   Year int SELECT  Day   1   Month   2   Year   2008  SELECT DateAdd dd   Day-1  DateAdd mm   Month -1  DateAdd yy   Year - 2000   20000101

User · Answer

Try   CAST STR DATEPART year  DATE    -   STR DATEPART month  DATE     -   STR DATEPART day  DATE   AS DATETIME

User · Answer

Try this   Declare  DayOfMonth TinyInt Set  DayOfMonth   13 Declare  Month TinyInt Set  Month   6 Declare  Year Integer Set  Year   2006 -- ------------------------------------ Select DateAdd day   DayOfMonth - 1             DateAdd month   Month - 1                 DateAdd Year   Year-1900  0      It works as well  has added benefit of not doing any string conversions  so it s pure arithmetic processing  very fast  and it s not dependent on any date format This capitalizes on the fact that SQL Server s internal representation for datetime and smalldatetime values is a two part value the first part of which is an integer representing the number of days since 1 Jan 1900  and the second part is a decimal fraction representing the fractional portion of one day  for the time  --- So the integer value 0  zero  always translates directly into Midnight morning of 1 Jan 1900      or  thanks to suggestion from  brinary   Select DateAdd yy   Year-1900           DateAdd m    Month - 1   DayOfMonth - 1      Edited October 2014  As Noted by  cade Roux  SQL 2012 now has a built-in function  DATEFROMPARTS year  month  day  that does the same thing   Edited 3 Oct 2016   Thanks to  bambams for noticing this  and  brinary for fixing it   The last solution  proposed by  brinary  does not appear to work for leap years unless years addition is performed first   select dateadd month   Month - 1        dateadd year   Year-1900   DayOfMonth - 1

User · Answer

Sql Server 2012 has a function that will create the date based on the parts  DATEFROMPARTS   For the rest of us  here is a db function I created that will determine the date from the parts  thanks  Charles      IF EXISTS  SELECT   FROM dbo sysobjects WHERE id   object id N  dbo   func DateFromParts         DROP FUNCTION  dbo   func DateFromParts  GO  CREATE FUNCTION  dbo   func DateFromParts         Year INT       Month INT       DayOfMonth INT       Hour INT   0   -- based on 24 hour clock  add 12 for PM         Min INT   0       Sec INT   0   RETURNS DATETIME AS BEGIN      RETURN DATEADD second   Sec               DATEADD minute   Min               DATEADD hour   Hour              DATEADD day   DayOfMonth - 1               DATEADD month   Month - 1               DATEADD Year   Year-1900  0        END  GO   You can call it like this     SELECT dbo func DateFromParts 2013  10  4  15  50  DEFAULT    Returns     2013-10-04 15 50 00 000

User · Answer

Sql Server 2012 has a function that will create the date based on the parts  DATEFROMPARTS   For the rest of us  here is a db function I created that will determine the date from the parts  thanks  Charles      IF EXISTS  SELECT   FROM dbo sysobjects WHERE id   object id N  dbo   func DateFromParts         DROP FUNCTION  dbo   func DateFromParts  GO  CREATE FUNCTION  dbo   func DateFromParts         Year INT       Month INT       DayOfMonth INT       Hour INT   0   -- based on 24 hour clock  add 12 for PM         Min INT   0       Sec INT   0   RETURNS DATETIME AS BEGIN      RETURN DATEADD second   Sec               DATEADD minute   Min               DATEADD hour   Hour              DATEADD day   DayOfMonth - 1               DATEADD month   Month - 1               DATEADD Year   Year-1900  0        END  GO   You can call it like this     SELECT dbo func DateFromParts 2013  10  4  15  50  DEFAULT    Returns     2013-10-04 15 50 00 000

User · Answer

Assuming y  m  d are all int  how about   CAST CAST y AS varchar     -    CAST m AS varchar     -    CAST d AS varchar  AS DATETIME    Please see my other answer for SQL Server 2012 and above

User · Answer

It is safer and neater to use an explicit starting point  19000101   create function dbo fnDateTime2FromParts  Year int   Month int   Day int   Hour int   Minute int   Second int   Nanosecond int  returns datetime2 as begin     -- Note  SQL Server 2012 includes datetime2fromparts   function     declare  output datetime2    19000101      set  output   dateadd year         Year - 1900     output      set  output   dateadd month        Month - 1       output      set  output   dateadd day          Day - 1         output      set  output   dateadd hour         Hour            output      set  output   dateadd minute       Minute          output      set  output   dateadd second       Second          output      set  output   dateadd ns           Nanosecond      output      return  output end

User · Answer

If you don t want to keep strings out of it  this works as well  Put it into a function    DECLARE  Day int   Month int   Year int SELECT  Day   1   Month   2   Year   2008  SELECT DateAdd dd   Day-1  DateAdd mm   Month -1  DateAdd yy   Year - 2000   20000101

User · Answer

Try   CAST STR DATEPART year  DATE    -   STR DATEPART month  DATE     -   STR DATEPART day  DATE   AS DATETIME

User · Answer

I add a one-line solution if you need a datetime from both date and time parts   select dateadd month    Year -1900  12    Month -1   DayOfMonth -1    dateadd ss   Hour 3600    Minute 60    Second  0    dateadd ms   Millisecond  0

User · Answer

SQL Server 2012 has a wonderful and long-awaited new DATEFROMPARTS function  which will raise an error if the date is invalid - my main objection to a DATEADD-based solution to this problem    http   msdn microsoft com en-us library hh213228 aspx  DATEFROMPARTS ycolumn  mcolumn  dcolumn    or  DATEFROMPARTS  y   m   d

User · Answer

Try CONVERT instead of CAST   CONVERT allows a third parameter indicating the date format   List of formats is here  http   msdn microsoft com en-us library ms187928 aspx  Update after another answer has been selected as the  correct  answer   I don t really understand why an answer is selected that clearly depends on the NLS settings on your server  without indicating this restriction

User · Answer

If you don t want to keep strings out of it  this works as well  Put it into a function    DECLARE  Day int   Month int   Year int SELECT  Day   1   Month   2   Year   2008  SELECT DateAdd dd   Day-1  DateAdd mm   Month -1  DateAdd yy   Year - 2000   20000101

User · Answer

Try this query       SELECT SUBSTRING CONVERT VARCHAR JOINGDATE 103  7 4 AS     YEAR SUBSTRING CONVERT VARCHAR JOINGDATE 100  1 2 AS MONTH SUBSTRING CONVERT VARCHAR JOINGDATE 100  4 3 AS DATE FROM EMPLOYEE1   Result   2014    Ja    1 2015    Ja    1 2014    Ja    1 2015    Ja    1 2012    Ja    1 2010    Ja    1 2015    Ja    1

User · Answer

Try CONVERT instead of CAST   CONVERT allows a third parameter indicating the date format   List of formats is here  http   msdn microsoft com en-us library ms187928 aspx  Update after another answer has been selected as the  correct  answer   I don t really understand why an answer is selected that clearly depends on the NLS settings on your server  without indicating this restriction

User · Answer

Assuming y  m  d are all int  how about   CAST CAST y AS varchar     -    CAST m AS varchar     -    CAST d AS varchar  AS DATETIME    Please see my other answer for SQL Server 2012 and above

User · Answer

Try CONVERT instead of CAST   CONVERT allows a third parameter indicating the date format   List of formats is here  http   msdn microsoft com en-us library ms187928 aspx  Update after another answer has been selected as the  correct  answer   I don t really understand why an answer is selected that clearly depends on the NLS settings on your server  without indicating this restriction

[sql] Create a date from day month and year with T-SQL

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql