SIMPLE datetime in where clause urgh...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SIMPLE datetime in where clause urgh...

Author

Topic

michaelcrawford
Starting Member

2 Posts

Posted - 2006-08-28 : 13:51:25

I simply need to select all records based on the date passed in via the query string.

select [logid], [logdate] from tbl_log WHERE [log_date]=cast('" + request("logdate") + "' as datetime)

This does not work.

request("logdate") is a query string parameter that comes in on an asp.net page looking like this: 3/3/2005

I'm not well versed in sql, however I have tried a many different where clause syntax.

Please help.

Thanks,

Mike.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-28 : 13:56:35

shooting from the hip i would say the logdate values stored in the table have hours:minutes:seconds ... on them?

you may want to do a between on the start/end of the day, or a month/day /year compare

________________________________________________
"Wow, you have a master's degree in psychology? I will order a vodka martini, thanks"

michaelcrawford
Starting Member

2 Posts

Posted - 2006-08-28 : 14:13:39

I opened the table in sql server and the logdate data is stored like this: 3/3/2005 2:43:09 PM

I want to ignore time for this query and select all records based on the date.

Thanks,

Mike.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-28 : 14:22:28

Do the query like this:


elect
	[logid],
	[logdate]
from
	tbl_log
WHERE
	-- Get data for 2006-05-15
	[log_date] >= '20060515' and
	[log_date] <  '20060516'

CODO ERGO SUM

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-28 : 14:24:45

figured..

you have some options. do a month/day/year compare, or setup two variables, one for the beginning of the day (the one you are getting passed in) and one for the end of the day.

where logdate >= mypassedindate and logdate < DATEADD(d,1,mypassedindate)

check dateadd for syntax, its a little different if you do it in ASP versus SQL.

quote:
Originally posted by michaelcrawford

I opened the table in sql server and the logdate data is stored like this: 3/3/2005 2:43:09 PM

I want to ignore time for this query and select all records based on the date.

Thanks,

Mike.

________________________________________________
"Wow, you have a master's degree in psychology? I will order a vodka martini, thanks"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-08-28 : 17:26:33

Also Michael, not an answer to your question, but a strong recommendation - don't simply concatenate the request querystring into your query like that! It will make it vulnerable to a severe security hole called a SQL Injection Attack. Use parameters instead.
See http://sqlteam.com/redir.asp?ItemID=8406

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-28 : 17:53:03

quote:
Originally posted by snSQL

Also Michael, not an answer to your question, but a strong recommendation - don't simply concatenate the request querystring into your query like that! It will make it vulnerable to a severe security hole called a SQL Injection Attack. Use parameters instead.
See http://sqlteam.com/redir.asp?ItemID=8406

Absolutely! And it does help him, also, since by using parameters you don't need to worry about date formats and delimiters and all that.

more: http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources