SQL Query date format conversion.

[kjacarver]

I'm having a problem converting the British date format to the American date format within an SQL query. I use the Month and Year methods to attain the month and year values from the two variables - strFromDate and strToDate. However, I'm not sure of the syntax to use in the query. Here is the code, but if anyone has any ideas they would be appreciated greatly.

strQuery = strQuery & "WHERE Data = 'ORDER' " strQuery = strQuery & "AND Input_Date BETWEEN month('" & strFromDate & "') /01/ year('" & strFromDate & "') AND month('" & strToDate & "') /01/ year('" & strToDate & "') "

 

[Mighty]

Two questions:

Are your dates stored in date format or as a string?
Are your dates stored in American or British format? Mise Le Meas,

Mighty

 

[kjacarver]

The dates are stored as a String, and in the British format.

 

[Mighty]

As far as I can tell, it's not going to be easy to order your records by the date as they are in string format. For example: 07/02/01 would not be between 10/01/01 and 10/02/01 (dd/mm/yy).

Have you got control of the database. If so, can you change the format of the Input_Date field to a date format.

I will have a think about how you could do it with your current setup. Mise Le Meas,

Mighty

 

[kjacarver]

Dear Mighty
Thank-you for your time on this matter, I have by trial and error now figured it out. The date will always start with 01 in the database, so it is just a case of swoping the month and day around.

strQuery = strQuery & "AND (Input_Date BETWEEN '" & month(strFromDate) & " /01/ " & year(strFromDate) & "' AND '" & month(strToDate) & " /01/ " & year(strToDate) & "') "

Thanks again.

 

[Qualis]

Actually, depending on the data, it could have worked they way you origionally had it, except for one small issue. I have found this to be true myself when using date fields in SQL. When you do a compare with a string date, you have to surround it with single quotes (') or you do not get the desired results. Your last post has the single quotes and thus wil work...