Can I have some SQL tech support please?

[scarylady]

I'll start with a disclaimer: I am not very good at SQL. I don't use it much.

So, can anyone tell me why the following query isn't working?

USE [DATABASE]
GO

SELECT *
FROM [TABLE]
WHERE [DATETIME FIELD] BETWEEN [FIRST DATE] AND [LAST DATE]
GO

SQL accepts and runs the query but brings back zero results even though I know there are records that should fall into the range. I note that the display of the datetime field in Enterprise Manager is dd/mm/yyyy and that in a Query Result Pane it is yyyy-mm-dd. I have tried both of these, but no success. The exact same query runs fine on a numeric field, so my syntax is OK. What am I doing wrong?

Comments

[richc.livejournal.com]

You might need to use to_date (or your DB's equivalent) to turn the first and last dates from strings to the date type. Assuming you haven't done this already.

Have you fixed it?

[furzepig.livejournal.com]

I don't think we know each other, but your post brought back horrible memories I have of many days of troubleshooting a similar problem.

In my case it was to do with the date format - I had to output the date in ODBC format, then analyze it. My solution wouldn't solve your problem (as it was in Cold Fusion based code calling data from SQL server) but I recommend pursuing the date format angle if you haven't solved it already!

Apologies for inappropriate spamming if this is no use to you.