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.

[richc.livejournal.com]

Or match up the date formats if you db doesn't have a date conversion function.

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.

Re: Have you fixed it?

[scary-lady.livejournal.com]

Very kind of you, and also correct.

I didn't actually work out exactly why it didn't work in the end. I found an online help page that displayed the query. It looked EXACTLY the same as mine. However, when I copied and pasted the date format from that query to mine and carefully replaced the example dates with my dates it worked.

I still have no idea why.