DateTime Problem

  • When i do the following query

    select fromdate from emp where fromdate>'2/16/2007' (here fromdate is varchar)

    I get the follwing result set

    3/18/2004

    9/28/2004

    2/19/2004

    5/24/2006

    9/29/2004

    5/10/2001

    9/13/2001

    6/30/2006

    8/25/2000

    7/18/2006

    5/01/2007

    8/02/2000

    4/25/2001

    8/17/2006

    9/27/2006

    but i need the dates greater than '2/16/2007' which is not doing

    when I tried for less than and equal to it works just fine but am unable to get greater han

  • Just cast fromdate (in the table) as a datetime like:

    select fromdate from emp where cast(fromdate as datetime)>'2/16/2007'

    as long as you are sure they are all valid datetimes otherwise the conversion will fail.

  • It looks to me like your "fromdate" field might be char/varchar instead of datetime. In that case, you'll get any string that starts with a character > "2" in the example you gave.

    If the column is datetime, try casting the date in the where clause as datetime.

    If the column is char/varchar, you probably need to cast both as datetime, or you need to modify the table so the column is datetime (if that won't break anything else in the database/app/data connection layer). Casting both will result in a slow query (since it'll have functions on both sides of the equation), but will at least work.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • yeah my Fromdate is a varchar as i told you and now i tried doing cast(fromdate as datetime) i get hte result set ok but with an error message

    Msg 241, Level 16, State 1, Line 1

    Syntax error converting datetime from character string.

  • check out convert(datatype, yourcol, format)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I don't know if you want to use a temp table but this should work

    select *

    into #temp

    from emp

    where isDate(fromdate) = 1

    select fromdate

    from #temp

    where cast(fromdate as datetime)>'2/16/2007'

  • Mike Levan (12/17/2007)


    yeah my Fromdate is a varchar as i told you and now i tried doing cast(fromdate as datetime) i get hte result set ok but with an error message

    Msg 241, Level 16, State 1, Line 1

    Syntax error converting datetime from character string.

    Since the field is varchar, you might have some records that are not proper dates. You could try this to make sure they are dates

    SELECT

    fromdate

    FROM emp

    WHERE fromdate IS NOT NULL

    AND ISDATE(CONVERT(CHAR(10),fromdate,101)) = 0

    Greg

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • This topic is named improperly.

    There is no any problem with datetime.

    The only problem is with column not being datetime.

    Make it datetime and the problem will disappear.

    _____________
    Code for TallyGenerator

  • Mike Levan (12/17/2007)


    When i do the following query

    select fromdate from emp where fromdate>'2/16/2007' (here fromdate is varchar)

    I get the follwing result set

    3/18/2004

    9/28/2004

    2/19/2004

    5/24/2006

    9/29/2004

    5/10/2001

    9/13/2001

    6/30/2006

    8/25/2000

    7/18/2006

    5/01/2007

    8/02/2000

    4/25/2001

    8/17/2006

    9/27/2006

    but i need the dates greater than '2/16/2007' which is not doing

    when I tried for less than and equal to it works just fine but am unable to get greater han

    1 Always use proper DATETIME datatype to store dates

    2 Let the front end do the formation

    If you have no option to change the datatype of the column, create a new column with datetime datatype;update it from varchar column and use that datetime column for further manipulations. Otherwise you have to end with with lot of castings or convertions


    Madhivanan

    Failing to plan is Planning to fail

  • follow Madhivanan's advise !

    select fromdate

    , convert(datetime,fromdate,100) as fromdate_datetime

    , convert(char(23), convert(datetime,fromdate,100), 121) as frildate_datetimestring

    from emp

    where convert(datetime,fromdate,100) > convert(datetime,'2/16/2007',100)

    order by convert(datetime,fromdate,100)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I absolutely agree with Sergiy and Madhivinan... the root problem is that the column is the wrong data type. The datatype for the column should be/must be changed to DATETIME to avoid these problems in the future.

    And, no, you can't rely on ISDATE because ISDATE('2007') will return a "1"...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I absolutely agree with Sergiy and Madhivinan

    Well except that you often mispell my name 😉


    Madhivanan

    Failing to plan is Planning to fail

  • Oh bugger... I'm sorry. I'm the same way... I do like to have "Jeff" spelled correctly.

    Heh... lately, I've been using your name (or a mispelled version of it :hehe: ) so much I should try to figure out how to make a hot-key for it 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 12 (of 12 total)

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