Anyone thathas ever tried to treat a string as a date in a SQL command for SQL Server knows it’s a pain in the ass. And if you haven’t tried it, then I’d do something else instead. BUT if you HAVE to do it, then here’s one trick that worked for me.
I had a list of dates in one column of a CSV file I had just finished creating. All was well in the world and I happily gave the client the code to use to make the CSV file. But there was a problem! Oh NO! The client didn’t want to have to bother parsing the CSV file themselves. They wanted to be able to select a date range and only retrieve those records and put them into the CSV file. I thought, “Fine, I’ll do that for you you miserable turd. Why the hell couldn’t you tell me that when I started???”. What I said was, “OK”.
At first I thought it wouldn’t be a problem. I was going to use the SQL BETWEEN command in the WHERE clause of the SELECT statement and that should fix it. But my web server kept balking at the command. So I thought there was a definite problem here. I spent several hours trying various CONVERT and TYPE commands in both SQL and ASP but nothing wanted to work. FINALLY I found the answer. First I have to convert the date being read to a VARCHAR type 111, which formats it like 2006/22/22, then replace “/” with “-”.
The whole code looks like:
SELECT * FROM tblname WHERE id = ‘16′ AND REPLACE(CONVERT(varchar, now_date, 111), ‘/’, ‘-’) BETWEEN REPLACE(CONVERT(varchar, ‘” & dateFrom & “‘, 111), ‘/’, ‘-’) AND REPLACE(CONVERT(varchar, ‘” & dateTo & “‘, 111), ‘/’, ‘-’)
The three entries I had to convert were the date column to look through (now_date), and the dateFrom and dateTo. Wow. I hope this helps some of you out there so you don’t have to do as much digging as I did. If you know a better way let me know in the comments
