date format

[dtfhome]

I am having a problem returning records from a table called employees. In the where clause, I am comparing a datetime field with a form variable in 'mm/dd/yy format'.

Where modification_date = '#form.date#'.

In SQL server, the data is being stored in the datetime format. What function do I use with 'Modification_Date' to convert it to the 'mm/dd/yy' format.

Maybe I need to use convert, or CAST along with a style. I don't know -- still learing sql server. Any help and explanation as to what is behind the function would be helpful.

Thanks in advance.

 

[skicamel]

Usually when I run into last modified fields, they're populated with getdate(), which at the moment would set the date to 10/02/02 19:02:00. 10/02/02 actually equals 10/02/02 00:00:00 or midnight this morning/last night. If this is the case in your pull, you can change this by first converting to a varchar then back to a datetime.

Try this...

Where convert(datetime,convert(varchar,modification_date,112)) = '#form.date#'

 

[r937]

yes, CAST and CONVERT, i have that bookmarked --

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

where convert(varchar(8)
,modification_date,1) = '#form.date#'

style 1 is mm/dd/yy, style 101 is mm/dd/yyyy

rudy

 

[entaroadun]

Instead of converting the SQL field to fit your form format, convert your form field to fit the SQL international format. Just call CDate (if you're using VB) to turn your form field value into the system appropriate date format, then concatenate that onto the SQL string. Remember SQL Server doesn't need #s around its dates, only 's. Hope this helps.

 

[r937]

entaroadun, your suggestion to convert the form field value to a different date format doesn't get around the problem skicamel mentioned, namely that in sql/server, datetime fields have a time component, so you have to do something to disregard the time part, such as truncating it off both sides of the comparison, or somehow comparing midnight to midnight

since the pound signs are located inside single quotes, this indicates to me that dtfhome is using coldfusion

rudy