CODEWITHSUNDEEP
sqlt-sqlsql-server-2005varchar
user2059064
user2059064

Reputation: 163

How to Insert varchar data into a datetime field (SQL Server 2005)?

I have this data I want inserted to a table. Sample data MM/DD/YYYY + 1 day and 12:00:00 PM

Basically what I need is to insert the current date + 1 day and specific time of 12:00:00 PM.

My code is this:

DECLARE @MyEstimatedDate as varchar(100)    ---TEMPORARY CONTAINER
DECLARE @MyEstimatedDate1 as varchar(100)   ---TEMPORARY CONTAINER
DECLARE @MyEstimatedDate2 as varchar(100)   ---TEMPORARY CONTAINER
DECLARE @MyEstimatedDate3 as DATETIME       ---FINAL DATA NEEDED. This is the data I want inserted.

SET @MyEstimatedDate = DATEADD(day,1,GETDATE()) 
SET @MyEstimatedDate1 = CONVERT(VARCHAR(100),@MyEstimatedDate,101)
SET @MyEstimatedDate2 = @MyEstimatedDate1 + ' 12:00:00 PM'
SET @MyEstimatedDate3 = cast(@MyEstimatedDate2 as datetime)  ---I believe this is the error

Error message I get:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Upvotes: 1

Views: 2705

Answers (3)

Andriy M
Andriy M

Reputation: 77717

Just don't use varchar when manipulating datetime data. SQL Server 2005 offers enough tools for you to be able to avoid conversion.

The following is a more or less known method of dropping the time part from a datetime value:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @InputDateTime), 0);

In the above example, DATEDIFF calculates the number of days between a date specified as 0 and the given date. The number of days is then added by the DATEADD function to the 0 date. The final result is a datetime value with the time of 00:00:00 and the same date as @InputDateTime. This is because the 0 date is an integer representation of 1900-01-01 00:00:00: its time part is zero and, since we have incremented it by a whole number of days, so is the result's time part.

Now, if instead of the DATEDIFF days you add DATEDIFF+1, you will get the next day. Furthermore, if instead of 0 as the date to be incremented you use 12:00, you will get the next day's noon, which appears to be what you want. So, the final expression will look like this:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @InputDateTime) + 1, '12:00');

Since your input timestamp is supposed to be the current date & time, just replace @InputDateTime with GETDATE():

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, '12:00');

Upvotes: 2

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

datetime type contains date + time. In your case for @MyEstimatedDate1 need just a date

DECLARE @MyEstimatedDate varchar(100)    ---TEMPORARY CONTAINER
DECLARE @MyEstimatedDate1 varchar(100)   ---TEMPORARY CONTAINER
DECLARE @MyEstimatedDate2 varchar(100)   ---TEMPORARY CONTAINER
DECLARE @MyEstimatedDate3 DATETIME       ---FINAL DATA NEEDED. This is the data I want inserted.

SET @MyEstimatedDate = DATEADD(day, 1, GETDATE()) 
SET @MyEstimatedDate1 = CONVERT(VARCHAR(100), CAST(@MyEstimatedDate AS date), 101)
SET @MyEstimatedDate2 = @MyEstimatedDate1 + ' 12:00:00 PM'
SET @MyEstimatedDate3 = cast(@MyEstimatedDate2 as datetime)   ---I believ

OR simple to use it

SELECT DATEADD(hour, 36, GETDATE() - CAST(GETDATE() AS time))

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

Why not simply do

cast(dateadd(day, 1, getdate()) as date)

that was for midnight. For noon, do this

dateadd(hour, 12, cast(cast(dateadd(day, 1, getdate()) as date) as datetime))

forget the above, it's wrong.

Correct answer is

dateadd(hour, 12, cast(cast(dateadd(day, 1, getdate()) as date) as datetime))

This time I even tested it.

Upvotes: 0

Related Questions