Another Date question

Question

Post reply

Another Date question

chuck lane

SSC Journeyman

Points: 85
More actions
June 15, 2005 at 8:10 am

#362896

I am trying to query some things in our database off of a certain date column which is in the format mm/dd/yy. the probem is that the date is stored as varchar 255 so i cant seem to get it to pull everything after a certain date? is there a way to get this information without changing the format of the column?

thanks

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

AJ Ahrens

SSC-Insane

Points: 20676

June 15, 2005 at 9:48 am

#565750

Try SET DATEFORMAT MDY at the beginning of your stored-procedure. Also, what day is giving you problems?

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Phil Parkin SSC Guru Points: 245813 More actions · Answer 2

Chuck, this is certainly possible. The outline of what you need to do is

select data1, data2, etc

from tablelist

where (change varchar field to date) > targetdate

This does not require you to change the datatype of the underlying physical field - this conversion is performed in the query and then normal date operations will work within the query. This 'change to date', however, is not straightforward. Here's some sample code that will give you an idea:

declare @strDate varchar(255), @date datetime, @strDate2 varchar(255)

set @strDate = '12/25/04'

set @strdate2 = '20' + right(@strDate,2) + '/' + left(@strdate,2) + '/' + substring(@strdate,4,2)

set @date = cast(@strdate2 as datetime)

select @strdate2, @date

The principle here is to change the varchar field (@strDate) to another varchar (@strdate2), in the format

yyyy/mm/dd (to get to something that is standard regardless of the country in which the query is executed) .

and then to convert that to a date using CAST. It is probably possible to do this all in one line if you have to - I expanded it out for clarity.

Note that I have assumed that all of your dates are post 2000.

Regards

Phil

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.