datetime query 1

[nelco]

I have to create a query where I have to compare a field name which has datetime with current date in my asp page.

"select user_nme,lot_no,space_no,work_phn_no,rec_prcs_dte FROM [agency].[dbo].[auto_park_unassigned_user] where rec_prcs_dte = '"&dte&"'"

here dte = current date (mm/dd/yyyy) and rec_prcs_dte has date and time stamp.

Even though dates are same, I do not get any results because of time stamp in the field. I will have to convert datetime into mm/yy/dd. How do I do that?
I tried but no success. Any help will be appreciated.

 

[DotNetGnat]

what database are you using...look into cast() and convert() functions

-DNG

 

[guitardave78]

Try

"select user_nme,lot_no,space_no,work_phn_no,rec_prcs_dte FROM [agency].[dbo].[auto_park_unassigned_user] where rec_prcs_dte = #"&Date()&"#"

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[gmmastros]

Since the field in the database could contain time information, you are better off using this query instead.

select user_nme,lot_no,space_no,work_phn_no,rec_prcs_dte 
FROM   [agency].[dbo].[auto_park_unassigned_user] 
where  rec_prcs_dte >= '[!]YourDate[/!]'
       And rec_prcs_dte < '[!]YourDate[/!]' + 1

This will only work if your date does NOT contain time information.

Written this way, if you have an index on the date field, that index will be used the SQL Server when executing the query. There are other ways to accomplish this, but they usually involve methods that will cause a table scan of the data and therefore hinder performance.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nelco]

Thanks gmmastros. That worked. I appreciate your help.

Also thanks to DotNetGnat and guitardave78 for all help.

 

[gmmastros]

You're welcome. Thanks for the star.

Do you understand why it works? If not, I would be glad to explain further.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Chopstik]

Actually, that makes sense. I've done that for a while but never really considered the why of it working that way. Your explanation put it into perspective for me. Thanks!

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill