Convert varchar to smalldatetime...

[song2siren]

Hello

I'm trying to convert a column (JudgementDate) in a SQL database (JPL_FLO_Index_copy) from varchar to smalldatetime. The dates, for example, need to be converted from 1 July 2003 to 01/07/2003. Some of the rows however, have a <NULL> value.

I've tried things along the following lines without success:

alter table JPL_FLO_Index_copy
add tempDate smalldatetime

update JPL_FLO_Index_copy
set tempDate = cast(JudgementDate as smalldatetime), JudgementDate = isnull
(judgementDate, '1900-01-01')

alter table JPL_FLO_Index_copy
alter column JudgementDate smalldatetime

update JPL_FLO_Index_copy
set JudgementDate = tempDate

alter table JPL_FLO_Index_copy
drop column tempDate

I'm sure this is fairly simple, but any help/suggestions would be very much appreciated.

 

[johnherman]

I would drop the old varchar column and then add it back with the new datatype smalldatetime. Seems safer to me. I realize that this "moves" the column to the "bottom" of the table and you may not want to deal with that.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[song2siren]

Hi johnherman

Thanks for the suggestion, but I really need to be able to convert my existing data somehow, since the table already contains over a thousand records.

 

[SQLBill]

Check out ALTER TABLE in the BOL, scroll down to Arguments and look at the section on ALTER COLUMN. That lists cases where altering the column won't work. Do any apply to you?

Also, be aware that if you change the column to a DATETIME datatype the date WON'T be stored in the format 01/07/2003.

DATETIME is eight-bytes, with the first four bytes being the number of days before or after the default date. The second four bytes are the number of 'ticks' (3.33 milliseconds) after midnight.

That's how DATETIME is STORED. How you display it is determined by your SQL Server collation or commands in the application.

So, with what you have tried, what errors or unexpected results did you get?

-SQLBill

 

[song2siren]

Hi SQLBill

I don't think any of these cases apply. Regardless of how I approach this I always get a syntax error saying the character string cannot be converted to smalldatetime datatype. I would've thought this would be simple, since it's easy enough to do this the other way round and convert smalldatetime fields into longer text dates for display on ASP pages.

Thanks

 

[SQLBill]

Okay, try adding the following to your script. It will let SQL Server know what format it is to read the date information as.

SET DATEFORMAT dmy

Also, run ISDATE to make sure the date information is valid. Refer to the BOL for more information.

SELECT ISDATE(JudgementDate)
FROM JPL_FLO_Index_copy

If it returns a 1, the value is a DATE. If it returns a 0, the value isn't a date. NULL will return 0, so you could run it with ISNULL.

SELECT ISDATE(ISNULL(JudgementDate,'1/1/1900'))
FROM JPL_FLO_Index_copy

That will replace NULL with 1/1/1900 which is the default date.

-SQLBill

 

[SQLSister]

Additionally, you may have some incorrect date values in the table such as 2/30/2004, or 4jan 2004, or 3Mat05 or ASAP, etc. OFten a field which has been incorrectly defined as a varchar also has ho controls as to what data to enter. Anything that is not a valid date needs to be fixed before converting.