Reputation: 6872

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

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.

Upvotes: 282

Answers (15)

Peter Bojanczyk

Reputation: 21

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://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-2017&viewFallbackFrom=sql-server-previousversions

Upvotes: 2

Charles Bretana

Reputation: 146603

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));

Upvotes: 351

Marcelo Lujan

Reputation: 2232

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

Upvotes: 11

Gouri Shankar Aechoor

Reputation: 1581

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

Upvotes: 0

user3141962

Reputation: 7

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

Upvotes: 1

Konstantin

Reputation: 3460

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

Upvotes: 2

bluish

Reputation: 27410

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)

Upvotes: 4

Brian

Reputation: 38035

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

Upvotes: 17

Shrike

Reputation: 1201

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)

Upvotes: 120

Saul Guerra

Reputation: 31

Try

CAST(STR(DATEPART(year, DATE))+'-'+ STR(DATEPART(month, DATE)) +'-'+ STR(DATEPART(day, DATE)) AS DATETIME)

Upvotes: 3

Jack

Reputation: 4914

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

Upvotes: 6

Cade Roux

Reputation: 89741

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

Upvotes: 181

Cade Roux

Reputation: 89741

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)

DATEFROMPARTS(@y, @m, @d)

Upvotes: 270

devio

Reputation: 37225

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.

Upvotes: 13

Robert Wagner

Reputation: 17793

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')))

Upvotes: 5

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

Answers (15)

Related Questions