Converting from varchar to datetime

Question

Post reply

Converting from varchar to datetime

TcW_1978

SSCrazy

Points: 2018
More actions
January 29, 2010 at 10:51 am

#209555

I got this :
DECLARE @OriginalDateDebut datetime
DECLARE @HeureDebut nvarchar(5)
DECLARE @tempDateDebut datetime
DECLARE @tempVarchar nvarchar(30)
SET @OriginalDateDebut = GETDATE()
SET @HeureDebut = '17:30'
set @tempVarchar = CAST(CONVERT(DATE, @OriginalDateDebut,21) AS nvarchar(10))
set @tempVarchar = @tempVarchar + ' ' + @HeureDebut + ':00'
select @tempVarchar
Which gives me : 2010-01-29 17:30:00
But when I try :
set @tempDateDebut = cast(@tempVarchar as DATETIME)
I get : The conversion of a char data type to a datetime data type resulted in an out-of-range...
Is there a way to cast a varchar in a date format (ie:2010-01-29 17:30:00) to a datetime variable?
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Your dateformat is probably dmy. Your code worked for me, to get it to fail I had to set dateformat to dmy.

Try the following, it creates the string in ISO8601 format.

DECLARE @ OriginalDateDebut datetime

DECLARE @ HeureDebut nvarchar(5)

DECLARE @ tempDateDebut datetime

DECLARE @ tempVarchar nvarchar(30) -- added spaces between @ and variable name to allow code to post.

SET @OriginalDateDebut = GETDATE()

SET @HeureDebut = '17:30'

set @tempVarchar = CAST(CONVERT(DATE, @OriginalDateDebut,21) AS nvarchar(10))

set @tempVarchar = @tempVarchar + 'T' + @HeureDebut + ':00'

select @tempVarchar

set @tempDateDebut = cast(@tempVarchar as DATETIME)

select @tempDateDebut

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Question, are you trying to return todays date at 17:30 as part of this query? If so, you could do the following:

select dateadd(mi, 1050, dateadd(dd,datediff(dd,0,getdate()), 0))

TcW_1978 SSCrazy Points: 2018 More actions · Answer 3

You saved my life! lol

The only thing missing was the 'T' between the date and the time, everything works perfectly!

Thanks a lot!

Paul White SSC Guru Points: 150467 More actions · Answer 4

Avoid string manipulation of dates and times if at all possible.

An alternative approach might be:

DECLARE @DateOnly DATE = GETDATE(),

@TimeOnly TIME = '17:30',

@DateTime DATETIME2;

SELECT @DateTime = @DateOnly,

@DateTime = DATEADD(HOUR, DATEPART(HOUR, @TimeOnly), @DateTime),

@DateTime = DATEADD(MINUTE, DATEPART(MINUTE, @TimeOnly), @DateTime);

PRINT @DateTime;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Paul White (1/31/2010)
Avoid string manipulation of dates and times if at all possible.
An alternative approach might be:
DECLARE @DateOnly DATE = GETDATE(),
@TimeOnly TIME = '17:30',
@DateTime DATETIME2;
SELECT @DateTime = @DateOnly,
@DateTime = DATEADD(HOUR, DATEPART(HOUR, @TimeOnly), @DateTime),
@DateTime = DATEADD(MINUTE, DATEPART(MINUTE, @TimeOnly), @DateTime);
PRINT @DateTime;

Or, using your setup:

select cast(@DateOnly as datetime) + cast(@TimeOnly as datetime); -- tried datetime2 and it didn't want to work

Paul White SSC Guru Points: 150467 More actions · Answer 6

Lynn,

Yes. DATETIME2 does not support the add operator:

[font="Courier New"]Msg 8117, Level 16, State 1, Line xx

Operand data type datetime2 is invalid for add operator.[/font]

Adding dates directly has always seemed a bit 'dodgy' so I'm quite pleased about this change.

Though not as compact, I think I prefer the method using DATEADD and DATEPART.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi