Select today prob

Can't get just a certain days records to show

[bateman_ap]

Hi, I am building a query where I want to see all records that fall on a passed day. It is driving me mad though cos it just refuses to work!

I am using "WHERE fld_date_time = '20040901'" which displays no records although I have a database entry with 01/09/2004 13:30:00

If I put "WHERE fld_date_time < '20040901'" I see records so it is obviously querying OK.

Any ideas?

[Mr_Brutal]

Hi,

Isn't this a TIME problem, its not matching the = SQL because the time is wrong - doesn't it use midnight on that day or seomthing so using < will work OK.

Thats my first thought anyway!

[bateman_ap]

Do you have to then work out the next day and do a BETWEEN query? Seems strange you can't get the query to ignore the time bit of the record.

[Mr_Brutal]

Your right it does, maybe if you specify the date part from the field and whatever your comparing to it will match ok.

[mattglet]

I had a simliar issue a while ago. Try something like this:

SELECT something FROM yourtable WHERE (CONVERT(smalldatetime, CONVERT(varchar, fld_date_time, 101), 101) = CONVERT(smalldatetime, CONVERT(varchar, GETDATE(), 101), 101))

It's converting dates to type varchar, then converting both to smalldatetime to easily compare. There may be a better (read: more efficient) way to do this, but it worked for me.

[john_k]

I use one that uses a combination of CAST and CONVERT, so it is a little shorter. But I don't think it is any more efficient in execution.

Example:
CAST(CONVERT(char, GETDATE(), 101) AS datetime)

I haven't done many user-defined functions on SQL Server, but this seems like a good candidate for one!