Date Conversion error: out of range

Question

Post reply

Date Conversion error: out of range

hoseam

SSCertifiable

Points: 5533
More actions
December 9, 2013 at 1:39 am

#302123

I have this query
DECLARE @dt VARCHAR(20)
SET @dt = cast(MONTH(getdate()) as varchar(20)) + cast(YEAR(getdate()) as varchar(20))
SELECT CONVERT(DATETIME, '01' + @dt, 112) as [date]
I get this error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. please help

Viewing 5 posts - 1 through 4 (of 4 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If you add [font="Courier New"]SELECT @dt[/font] to your code, you'll see why it's failing.

John

hoseam SSCertifiable Points: 5533 More actions · Answer 2

Actually it was supposed to be SELECT CONVERT(DATETIME,'01' + @dt, 112) as [date], I still get the same error

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

OK, fine, but what I said still stands. Did you try what I suggested?

By the way, there are much better ways of getting the first day of the month than string manipulations. You can do it with some fairly simple date arithmetic. If you search for something like "t-sql get first day of month" then you should learn how to do it quite quickly.

John

Edit - corrected typo

masoudk1990 SSCommitted Points: 1651 More actions · Answer 4

This query works fine:

DECLARE @dt CHAR(8)

SET @dt = cast(MONTH(getdate()) as char(2)) + '01' + cast(year(getdate()) as char(4))

SELECT CONVERT(datetime,RIGHT(@dt,4)+LEFT(@dt,2)+SUBSTRING(@dt,3,2),112)

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.