SQL Date Parameters

**Gyto** · 10-19-2010, 10:30 AM

Hi there,

I am trying to create a simple query to extract data from an SQL 2008 R2 database. The only problem I am having is with the syntax for inserting dates into the SQL string. When I run the code I do not get any errors but it also returns no results when it should. It's probably worth mentioning it's not a problem with UK/US date formats too because I've tried it using dates that work both ways. Here's my code:

Please Login or Register  to view this content.

Any ideas why that SQL statement isn't working? If I take out the WHERE clause it's fine.

Thanks,

Matt

**Gyto** · 10-20-2010, 04:26 AM

Still can't find a solution to this :o( Any ideas? Is there a certain syntax I should be using? I've tried inserting #'s around the date and allsorts...!

Thanks!

**DonkeyOte** · 10-20-2010, 04:53 AM

Have you tried formatting DateFrom and DateTo to Strings appropriate to SQLServer date format ?

Please Login or Register  to view this content.

**Gyto** · 10-20-2010, 05:38 AM

You may be on to something there....I just checked the SQL View that was created for me and the date fields are varchar for some reason. I tried editing the SQL statement as shown below to try and correct this but still no luck so far...not sure if I can do it this way?!

Please Login or Register  to view this content.

**TheCyrusVirus** · 10-20-2010, 05:50 AM

I'm not an expert but dabble in vba. Try the below.

Please Login or Register  to view this content.

**DonkeyOte** · 10-20-2010, 05:50 AM

I don't have a SQL Server install to test against but my experience with MySQL would tell me you need to parse the dates as I outlined (ie as strings in appropriate format)

edit: I am not saying the above with any authority I should add....

**TheCyrusVirus** · 10-20-2010, 06:00 AM

Sorry didnt see your post DonkeyOte, looks like I just duplicated your solution but in a different way.

**Gyto** · 10-20-2010, 06:35 AM

All sorted guys - I tried the suggestions with the date formats but with no luck, so I got the developer at their end to alter the SQL View so the dates were actually date fields instead of varchar fields and it started working immediately. I guess I'll never know if it was possible the other way...! I can't see why it wouldn't work converting them at query runtime but oh well!

Thanks you your help, much appreciated!