Posts: 1
Threads: 1
Joined: Dec 2022
Reputation:
0
Level: 1 []
Total Points: 0
Rank 0 / 1
99% to upload Level
Activity 0 / 1
99% to upload your Rank
Experience 1
99% to upload Experience
Points: 50
|
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.
|
Posts: 0
Threads: 0
Joined: May 2021
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
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')))
|
Posts: 0
Threads: 0
Joined: Feb 2023
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
Try CONVERT instead of CAST.
CONVERT allows a third parameter indicating the date format.
List of formats is here: [To see links please register here]
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.
|
Posts: 0
Threads: 0
Joined: Apr 2018
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
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):
[To see links please register here]
DATEFROMPARTS(ycolumn, mcolumn, dcolumn)
or
DATEFROMPARTS(@y, @m, @d)
|
Posts: 0
Threads: 0
Joined: May 2021
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
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][1] for SQL Server 2012 and above
[1]: [To see links please register here]
|
Posts: 0
Threads: 0
Joined: Sep 2018
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
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
|
Posts: 0
Threads: 0
Joined: Oct 2020
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
Try
CAST(STR(DATEPART(year, DATE))+'-'+ STR(DATEPART(month, DATE)) +'-'+ STR(DATEPART(day, DATE)) AS DATETIME)
|
Posts: 0
Threads: 0
Joined: Aug 2022
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
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)
|
Posts: 0
Threads: 0
Joined: Feb 2019
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
Sql Server 2012 has a function that will create the date based on the parts ([DATEFROMPARTS][1]). 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
[1]: [To see links please register here]
|
Posts: 0
Threads: 0
Joined: Jun 2021
Reputation:
0
Level: inf []
Total Points: inf
Rank nan / 1
100% to upload Level
Activity inf / 1
99% to upload your Rank
Experience nan
100% to upload Experience
Points: 50
|
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)
|
|