Convert Date in a delete statement

Question

Convert Date in a delete statement

A Little Help Please

SSCarpal Tunnel

Points: 4764
More actions
May 17, 2010 at 7:46 am

#221588

I have a simple SP delete:
DELETE
FROM NETHistory
The table NETHistory contains a Column called "Date", the column is a "Varchar(12)".
The Date is displayed as "May 15 2010".
If I wanted to delete everything from NETHistory "where Date >= "May 16 2010"
How would I do a convert date in my SP?
Thanks

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

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

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

A Little Help Please (5/17/2010)
I have a simple SP delete:
DELETE
FROM NETHistory
The table NETHistory contains a Column called "Date", the column is a "Varchar(12)".
The Date is displayed as "May 15 2010".
If I wanted to delete everything from NETHistory "where Date >= "May 16 2010"
How would I do a convert date in my SP?
Thanks

First, why is a date being stored in a varchar912) column instead of a datetime column? Second, you show how the date is displayed, but is that how it is actually stored?

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 2

First, why is a date being stored in a varchar912) column instead of a datetime column? Second, you show how the date is displayed, but is that how it is actually stored?

Not sure WHY/WHO/Or what they where thinking when they designed this!!

The date is actualled stored in the DB as "May 14 2010"

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

A Little Help Please (5/17/2010)
First, why is a date being stored in a varchar912) column instead of a datetime column? Second, you show how the date is displayed, but is that how it is actually stored?
Not sure WHY/WHO/Or what they where thinking when they designed this!!
The date is actualled stored in the DB as "May 14 2010"

Next question, are you sure it is consistently stored in that format?

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

If the dates are stored consistently, check out the following. Realize that you won't be able to use any indexes on the Date column on the table because of the CONVERT function in the WHEE clause snippet.

declare @MyDate as varchar(12);

set @MyDate = 'May 16 2010';

select convert(datetime, @MyDate, 107);

where convert(datetime, @MyDate, 107) >= '20100516';