Problem with Dates

Question

Problem with Dates

Jon-211045

Old Hand

Points: 371
More actions
February 22, 2005 at 1:44 pm

#163701

I have always had a problem getting dates.
I mean, I can't get the syntax for dates right.
I need to run a query that can count the number of records based on date criteria.
Here is the one I made that works fine in access, but when I use it in asp code to call to SQL Server, I get "the requested properties cannot be supported"
What is the SQL Server friendly version of this statement?
sql = "SELECT Count(tblLinkRequest.LinkRequestID) AS CountOfLinkRequestID, tblLinkRequest.DateSent FROM tblLinkRequest GROUP BY tblLinkRequest.DateSent HAVING (((tblLinkRequest.DateSent)=#" & Date() & "#));"
Kindest Regards,
Jon
Free Cell Phone Games

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

sql = "SELECT Count(tblLinkRequest.LinkRequestID) AS CountOfLinkRequestID, tblLinkRequest.DateSent FROM tblLinkRequest GROUP BY tblLinkRequest.DateSent HAVING (((tblLinkRequest.DateSent)='" & Date() & "'));"

Jon-211045 Old Hand Points: 371 More actions · Answer 2

It's really that simple? Just take out the #'s?

Kindest Regards,

Jon
Free Cell Phone Games

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Unless Date() is some kind of variable you are passing in, you may want to try the following

@sql = 'SELECT Count(tblLinkRequest.LinkRequestID) AS CountOfLinkRequestID, tblLinkRequest.DateSent FROM tblLinkRequest GROUP BY tblLinkRequest.DateSent HAVING (((CONVERT( varchar, tblLinkRequest.DateSent, 101))=' + CHAR(39) + CONVERT( varchar, GETDATE(), 101) + CHAR(39) + '))'

Please note, @sql is a declared variable. The reason I used the CONVERT( varchar, DateField, 101) is so the timestamp is not part of the equality operation.

Try the following and see the difference: SELECT GETDATE(), CONVERT( varchar, GETDATE(), 101) AS 'Other GETDATE()'

I wasn't born stupid - I had to study.

Habib Zmerli Mr or Mrs. 500 Points: 559 More actions · Answer 4

I found that date formatting is probelmatic with international systems (i.e; French = dd/mm/yyyy).

The best way to pass dates to sql server in any locale is to use a quoted string with the format : 'yyyymmdd'

If you need time information use : 'yyyymmdd hh:nn:ss'

HABIB.

Kindest Regards,

Habib Zmerli (MVP)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Why are you preparing the SQL string to be executed in your client code anyway? Why don't you use a stored procedure here?

Piggy-backing on Habib, this should be interesting:

http://www.karaszi.com/SQLServer/info_datetime.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

amackay_1 Ten Centuries Points: 1240 More actions · Answer 6

The other main difference between dates in Access and SQL Server is that you have to specifically consider the time part in SQL Server. For example checking where Datefield='1/1/05' it won't match to a date stored as '1/1/05 22:05:03'. There are a number of ways to get around this.. you could use a range for example: datefield >= '1/1/05 00:00:00 AND datefield<='1/1/05 23:59:59'

Jon-211045 Old Hand Points: 371 More actions · Answer 7

I don't know how to use stored procedures yet.

I am more than willing to try learn though.

Kindest Regards,

Jon
Free Cell Phone Games

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

SP's are nothing mysterious. First place to look for stored procedures is BOL (aka the online help). Ken Henderson has a book "The Guru's Guide to Stored procedures...." I haven't read it myself, but if it is only half as good as his other two books which I own, it is worth the money.

And as always, when you got stuck with something, search here, or start a thread and someone will help.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]