Converting a char to a smalldatetime

Question

Post reply

Converting a char to a smalldatetime

sqlmonkey22

SSC-Addicted

Points: 492
More actions
May 21, 2003 at 5:22 am

#159216

OK guys where am i gong wrong here I have the following problem, when i try to convert a char to a datetime I get the error. The reason i am doing a convert on it is that it wont implicitly convert it for me in a separate update statement i am working on.
Server: Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The SQL is printed out below. Any suggestions?
declare @date1 varchar(8)
declare @date2 datetime
select @date1 = '01012003'
set @date2 = convert(datetime,@date1,103)

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi,

this will work

declare @date1 varchar(10)

declare @date2 datetime

select @date1 = '01.01.2003'

set @date2 = convert(datetime,@date1,103)

print @date2

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jpipes SSChampion Points: 12230 More actions · Answer 2

I think if '01012003' is the format of the string coming in to the proc, you will have to add some sort of delimiter to the string in order for SQL to parse the char into a datetime properly. You could try:

declare @input varchar(8)

declare @date1 varchar(12)

declare @date2 datetime

select @input = '01012003'

select @date1 = substring(@input, 1, 2) + '/' + substring(@input, 3, 2) + '/' + substring(@input, 5, 4)

set @date2 = convert(datetime,@date1,103)

Of course, I am assuming the first 2 characters of @input represent the month portion of the date (tough to tell with 01/01 🙂 )

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

Unfortunately, in some locales Franks solution will give an error when you reach the 13th of the month, as SQL Server may interpret the date as dd.mm.yyyy or mm.dd.yyyy, depending on the locale.

If you submit the date to SQL Server in the format YYYYMMDD (with no separators), then SQL Server will correctly interpret the date regardless of locale.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

quote:
If you submit the date to SQL Server in the format YYYYMMDD (with no separators), then SQL Server will correctly interpret the date regardless of locale.

Cool, each day I learn something new...I remember 10 years ago, when there were no datatime fields, I used to store dates in this format in clipper/dbase programs. That was fast, reliable and easy to handle. What a great step we've made in this 10 years.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jpipes SSChampion Points: 12230 More actions · Answer 5

Good point, Ian. A more locale-independent version of my previous post would be:

declare @input varchar(8)

declare @date1 varchar(12)

declare @date2 datetime

select @input = '01012003'

select @date1 = substring(@input, 5, 4) + substring(@input, 1, 2) + substring(@input, 3, 2)

set @date2 = convert(datetime,@date1,103)