Syntax for getting datetime value in select statement

[sfriedman451]

What is the best way to get values in a where clause from a datatime stamp data type without having to enter the full 2002-05-10 10:00:00. Is there a syntax that just allows me to enter a "where = '5/10/2002' or something like that?

 

[MapMan]

Hi There,

Are you asking about a timestamp data type, or a datetime data type? They're not treated the same in SQL.

A timestamp is essentially a binary datatype that is not recognizable or equivalent to date/time.

MapMan

Assume nothing, question everything, be explicit not implicit, and you'll always be covered.

 

[sfriedman451]

a datetimedata type.

 

[MapMan]

The syntax you used is acceptable. As long as you're not comparing to date fields. ie.

[tt]Select * from mytable where EntryDate = '05/02/2002'[/tt]

Would return records entered on May 2, 2002.

But,

[tt]Select * from mytable where EntryDate = GetDate()[/tt]

Would only return records that matched the date and time the query was run. In most cases, that means nothing would be returned.

If you wanted to return all the records for a specific date, try this...

[tt]Select * from mytable where DATEDIFF(Day, EntryDate, GetDate()) = 0[/tt]

HTH,

MapMan

Assume nothing, question everything, be explicit not implicit, and you'll always be covered.