Wanky SQL Date Issue

[jfrost10]

Hey guys,

Ok, I'm stumped on this. I'm passing a sql string as my command, and in it it has a where clause to compare a date field to a date being passed in (I only want records that occur after the passed in date, so
WHERE TimeIn >= 'datetime'
Here's my problem: If I hard code a date, it works. But if I pass in a variable it doesn't take! Am I missing something here?! I've tried passing in a date var, a string (which I"m using now), but it just doesn't want to work!

Any thoughts?

Jack

 

[fluteplr]

What date format are you using in the string?

I would suggest

YYYY-MM-DD HH:MM:SS

 

[jfrost10]

I'm using

mm/dd/yyyy hh:mm:ss

I'm not sure if the date mask would make a difference though, would it? I mean, if I hard code a date in that format it works fine, just not if its passed in.

jack

 

[mgid]

Maybe you've done this, but the date needs quotes around it, something like:

WHERE TimeIn >= "'" + datetime + "'"

where datetime is a string that sql server can convert to a datetime. Your format might work, but the one fluteplr listed will definitely work.

 

[jfrost10]

yeah, i do have quotes around htem (single quotes work I'm guessing, sine the hard coded datetime I put in just had single's and had no problems working).

How do i cast the datetime variable into the format you guys are suggesting? I tried doing format(variable, "mask&quot, but I got an error saying that sql server didn't recognize how I was trying to format it!


this was so much easier in access.....


Jack

 

[fluteplr]

Could you please post what ever code you have in whatever language you are using so we can see what it is you are actually doing.

If it is in t-sql the please post the t-sql code.

 

[jfrost10]

I'll post it tommorrow, but there's really not much more that I can tell you guys. I'm connecting to the sql server from an ASP.NET web form. I'm passing in a parameter of type Date (which defaults to the mm/dd/yy hh:mm:ss format). If I put a hard coded date in, it works. If I pass a variable, it doesn't.

I'll post the code up tommorrow morning (friday).

Thanks guys,

jack

 

[jfrost10]

Hey guys,

Here's the query re-done in T-SQL. Still doesn't work either way, but gives you an idea of whats happening. The format of the datetime in the db is mm/dd/yyyy hh:mm:ss, and thats the format of the datetime variable coming in as well (with the AM/PM too of course)

Create Procedure GetChatMessages
@RoomNum integer,
@TimeIn datetime as

Select Top 15
ChatID, Username, TimeIn, Message
From ut_chat
Where ChatID = @RoomNum
And TimeIn >= @TimeIn
Order By TimeIn DESC

Any thoughts?

Jack

 

[mgid]

Change the TimeIn part to:

TimeIn >= CAST(@TimeIn AS datetime)

If this fails then the string is bad, either in your application or in the stored procedure reformat the string to the default, which is YYYY-MM-DD HH:MM:SS.

 

[jfrost10]

k, here's what confuses me though: if its standard to do year, then month, then day, why is it that my table shows datetime fields as mm/dd/yyyy?

Is there a settin in sql server 2000 that I have to switch so it does the default like you're suggesting?

Thanks for the response, I really appreciate it


Jack

 

[mgid]

I think it's an ODBC standard and I'm pretty sure that sql server can convert it to a datetime from that format regardless of how it appears in the table. Check if the TimeIn column has datetime datatype.

 

[jfrost10]

This is so dumb. If i hard code a datetime into my sql string (if I do it without a stored proc) it works fine.If I pass it a string variable, even if the variable mirrors the datetime format the hardcoded value did, it doesn't work
>:
It doesn't seem to matter if I pass it a date or a string either...grrrr....

 

[mgid]

I should have seen this ealier, in your procedure change the @TimeIn variable to a varchar.

 

[jfrost10]

Sigh...
/me wipes the egg off of my face

Guys, thanks so much for all your help and advice on this. It turned out that my sql wasn't at fault, it was something else in my code that was updating the time too frequently, which caused the dataset to always come up empty.

sheesh..


Thanks guys!

Jack