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.
This question is related to
sql
sql-server
sql-server-2005
tsql
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
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.
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
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))
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));
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)
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)
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.
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));
Try
CAST(STR(DATEPART(year, DATE))+'-'+ STR(DATEPART(month, DATE)) +'-'+ STR(DATEPART(day, DATE)) AS DATETIME)
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
Try
CAST(STR(DATEPART(year, DATE))+'-'+ STR(DATEPART(month, DATE)) +'-'+ STR(DATEPART(day, DATE)) AS DATETIME)
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
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
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)
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)
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
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)
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
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
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
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
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)
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')))
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))
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')))
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));
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.
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
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));
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')))
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
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.
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')))
Source: Stackoverflow.com