convert DateTime to mm/dd/yyyy format

[royyan]

I need suggestions to convert DateTime to mm/dd/yyyy format in a SQL statement. Can anybody help me? Thanks!

 

[durug]

The SQL Statement is in ASP/VB or in a stored procedure?
If is the first case then use the function

FormatDateTime(Date(),2)

 

[SarkMan]

if it is in SQL then use the following in the SQL string
"convert(datetime," & the variable for date time & " )"

 

[royyan]

After using "convert" in my SQL atatement, the ASP page shows following errors:

*****************************************
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'convert' in expression.
*****************************************
Do you have any idea what happened here?

For your reference, here is my code:

"select count(*) as TotalCount, convert(datetime, Date) as Date1 from MyTable where Date between #" & fromDate & "# and #" & toDate & "# group by convert(datetime, Date)"


Thanks a lot!

 

[SarkMan]

I work more with SQL server and that function is supported. It may not be supported in Access.

 

[royyan]

I think that is the problem. I checked Jet-SQL, there is no "convert" function. I will try other ways to get this work done. If you guys come up new solution, please let me know also.
Thanks for all the information!