Date Query in SQL statement.

[NinjaTurtle]

Dear,

i have 2 questions about SQL Statement in Date:

1)
i have 3 fields in a table called "onLeave": Name, DateFrom & DateTo. Now i want to select those people who not in the office at the following date of 28/12/2003 OR 8/1/2004 or BOTH.

2)
In SQL Query Analyzer, how can i change the DATE format (eg. from mm/dd/yyyy to dd/mm/yyyy) in the SELECT Statement?

[glenngv]

Are the date fields in datetime datatype? If yes, just change the date format of the server in the Regional Settings.

[NinjaTurtle]

nope. they are in string type.

[glenngv]

I don't think you can do that in Query Analyzer unless you change the datatype to datetime. Regarding question#1, if you accept date input of dd/mm/yyyy and the date string in the database is in mm/dd/yyyy, you can use this function to change the format before putting it in the SQL statement

Function ChangeDateFormat(ByVal d) 'dd/mm/yyyy
  dim arr
  if not isdate(d) then exit function
  arr = split(d,"/")
  ChangeDateFormat = arr(1) & "/" & arr(0) & "/" & arr(2) 'mm/dd/yyyy
End Function

dim strDate
strDate = "28/12/2003"
strDate = ChangeDateFormat(strDate)
Response.write strDate 'displays 12/28/2003
'then use new strDate in SQL statement