Sql Server 2000 and Dates - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Sql Server 2000 and Dates

kelpiekelpie Posts: 10Member
Hello,

I just recently noticed that when you do a select statement on a table like SELECT studentid, modifieddate from tblStudents where modifieddate like 'Nov%', you get all the records for November even though they are in the databases 11-20-2004 for example. ModifiedDate is a datetime type field. However, if I do "SELECT studentid, modifieddate from tblStudents where modifieddate like '11%'", I do not get any results.

What is going on here and how can I allow users to search by 11 and not Nov.?

Thanks so much for your help.

Laura

Comments

  • iwilld0itiwilld0it Posts: 1,134Member
    [b][red]This message was edited by iwilld0it at 2004-12-10 7:11:35[/red][/b][hr]
    : Hello,
    :
    : I just recently noticed that when you do a select statement on a table like SELECT studentid, modifieddate from tblStudents where modifieddate like 'Nov%', you get all the records for November even though they are in the databases 11-20-2004 for example. ModifiedDate is a datetime type field. However, if I do "SELECT studentid, modifieddate from tblStudents where modifieddate like '11%'", I do not get any results.
    :
    : What is going on here and how can I allow users to search by 11 and not Nov.?
    :
    : Thanks so much for your help.
    :
    : Laura
    :
    :

    Try something like this ...

    [code]
    SELECT * FROM table WHERE MONTH(ModifiedDate) = 11
    [/code]

    - or -

    [code]
    SELECT * FROM table WHERE DATEPART(mm, ModifiedDate) = 11
    [/code]






  • kelpiekelpie Posts: 10Member
    [b][red]This message was edited by kelpie at 2004-12-10 7:58:12[/red][/b][hr]
    : [b][red]This message was edited by iwilld0it at 2004-12-10 7:11:35[/red][/b][hr]
    : : Hello,
    : :
    : : I just recently noticed that when you do a select statement on a table like SELECT studentid, modifieddate from tblStudents where modifieddate like 'Nov%', you get all the records for November even though they are in the databases 11-20-2004 for example. ModifiedDate is a datetime type field. However, if I do "SELECT studentid, modifieddate from tblStudents where modifieddate like '11%'", I do not get any results.
    : :
    : : What is going on here and how can I allow users to search by 11 and not Nov.?
    : :
    : : Thanks so much for your help.
    : :
    : : Laura
    : :
    : :
    :
    : Try something like this ...
    :
    : [code]
    : SELECT * FROM table WHERE MONTH(ModifiedDate) = 11
    : [/code]
    :
    : - or -
    :
    : [code]
    : SELECT * FROM table WHERE DATEPART(mm, ModifiedDate) = 11
    : [/code]
    :
    : Thanks very much. Both queries worked beautifully. Thanks for the lesson.

    Sincerely,

    Laura
    :
    :
    :
    :
    :



Sign In or Register to comment.