Post reply

Another Date question

  • 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

  • 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

  • June 15, 2005 at 9:55 am

    #565758

    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

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 15, 2005 at 9:56 am

    #565759

    well the day i need is January 1 2007 forward it is in the future becasue it is the day the information is to be deleted from our system.

    also im not using a stored procedure i am just trying to query

    so would it look like

    select city_code, outlet_code,SET DATEFORMAT MDY (delete_date)

    from ......

    where delete_date > 'January 1 2007'

    thanks

  • June 15, 2005 at 10:23 pm

    #565964

    SET DATEFORMAT MDY

    SELECT city_code, outlet_code, delete_date

    from table1

    WHERE CAST(delete_date AS datetime) > '20070101'

    Andy

  • June 16, 2005 at 6:42 am

    #566048

     

    select city_code, outlet_codefrom ......

    where convert(datetime,delete_date,1) > '20070101'

  • June 16, 2005 at 7:43 am

    #566078

    Thank you all so much for the help

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

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