MSSQL7 and convert()

tha_mink

I have a huge table that stores a date in a varchar column in the following format...

1/18/00 3:58:58 PM

Some dates were entered only in the following format

1/18/00

I didn't design the db and for reasons of compatibility in the extraction layer I cannot convert the column.

I need to query on the the date field for things like finding out what has happened since 1/15/01. I thought I would use something like...

SELECT * from table where convert(datetime, datefield) > 'somedate'

But that fails with the typical "conversion of char data to date would have resulted in an out of range date" blah blah blah....

So what to do? (no stored procs please)

tha_mink

tomhath

I don't have a SQLServer db handy, but try adding a format parameter (don't know if it works when converting from char to datetime).

convert(datetime, substring(datefield,1,6), 1)

Sphynx

You know what? This is just my opinion, but really the solution to this issue is for whoever did create that db to change the damn field to the proper type. There should be absolutely no need for you to follow some convoluted freekin' path through your code just to compare two dates. It's rediculous.

Ok so now that I'm done bitching (yes, yes this has happened to me many times also :p guess i'm just sick of it), here's not-so-convoluted solution...

Don't use CONVERT. Use CAST like so...

select CAST(mydate as datetime) from mytable where CAST(mydate as datetime) > 'WHATEVER DATE'

RedDragon

well you could upgrade the times to a unix timestamp using strtotime function in a while loop...

if you need more information about how to do this contact me!

tha_mink

well you could upgrade the times to a unix timestamp using strtotime function in a while loop...

Um, asp.net extraction layer, so, no I couldn't. (and wouldn't want to with the size of my record set)

tha_mink

Originally posted by Sphynx
You know what? This is just my opinion, but really the solution to this issue is for whoever did create that db to change the damn field to the proper type. There should be absolutely no need for you to follow some convoluted freekin' path through your code just to compare two dates. It's rediculous.

Don't use CONVERT. Use CAST like so...

select CAST(mydate as datetime) from mytable where CAST(mydate as datetime) > 'WHATEVER DATE'

If I was re-writing the whole app I would definitly change the DB but as it stands now, there is a whole lot more code that depends on that field being a varchar than I feel like changing. (Plus most of it is VB and .ASP .... i know i know)

But, thanks for the CAST() clue. I will give 'er a shot.

Sphynx

Hehe... sorry for the mini-rant, eh! I guess I just can't control myself 😉

tha_mink

Result of using CAST()

query-----

SELECT CAST(date_of_payment AS datetime) AS Expr1
FROM payments
WHERE (CAST(date_of_payment AS datetime) > '1/20/03')

Result -----

The conversion of char data to datetime resulted in an out of range date.

Any ideas?

Sphynx

That's odd. I tested on MSSQL 7 using the following data...
table name: mytable
field name: mydate

tested with field type varchar(50) and char(50)

data populated...
1/2/00

5/20/01

6/3/02 3:54:21

query used...
select CAST(mydate as datetime) as expr1 from mytable where (CAST(mydate as datetime) > '1/2/01')

result...
2001-05-20 00:00:00.000
2002-06-03 03:54:21.000

Just to compare with your data. Maybe you have some two-digit years that go way back?

tha_mink

You are the man. (unless you are the woman) Either way, thanks. It works fine now. (don't know why it balked the first time)

To convert the dates to a more consistant value, would this work...

UPDATE payments SET date_of_payment=CAST(date_of_payment as datetime)

Sphynx

SWEET 😃

tha_mink

Hey. Following that logic. What could I use to change each "date_of_payment" be a "CAST(date_of_payment as datetime)" for each record using SQL?

Sphynx

I'd try the query you mentioned first. ermm....yeah I think that should work, but dammit I'm at home now and without a server to test it :p . But anyway that should set those values to their calculated CAST value. Well, unless for some odd reason that field is limited to less than like 25 characters.