Search on Date field? 1

[CraigBest]

I am working with a SQL 7 Database, and one of the fields I need to select on is a Date field. The data stored there is in standard date format "MM-DD-YY HH:MM:SS" (you know what I mean.)

Anyway, I need to select a set of records from this DB for a given day. But when I pass a date value consisting of just a date part (no time) like "MM-DD-YY" I get no results.

How can I approach this cleanly? I'd prefer to avoid a greater than + Less than approach if possible. Do I need ot use something lie a LIKE operator? Of would reformatting the Date value I'm searching on differently help?

Thanks in advance

Craig

 

[grahamm]

Use the datepart date function to isolate day e.g.

select * from foo
where datepart(dd, timestamp) = 31

This will select only the rows where the timestamp is the 31st of the month

 

[tlbroadbent]

Dates aren't stored in a format such as "MM-DD-YY HH:MM:SS" but they are displayed in similar formats. See the following link for more info on date/time types.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_9xut.asp

If you want to match on just the date part of a date column use the convert function.

Select * From TableA
Where convert(char(8), Date_Col, 2)='09/18/01'

In this example the value in Date_Col is converted to a character string of style 2 which is mm/dd/yy.

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CraigBest]

thbraodbent,

Thanks, I tried that but it did not work. I instead used two statements, on greater than, one less than, which worked out fine. I am not sure why you method didn't work but it may have had to do with a data type error I was getting.

 

[mukund]

Hi CraigHartz,
Try using covert function
for e.g.

Select * from TableName Where
Convert(varchar(10), DateField, 101) = '01/01/2001'

pass the date in mm/dd/yyyy format.

See Convert help in BOL.

101 style converts a date into American format

Hope this helps.

Mukund.

 

[CraigBest]

Thanks Mukund, I'll give it a try once the program is completed and I have a chance to try new things.