Hi,

I am trying to convert date to MM/DD/YYYY format.

I am using CONVERT("Date field", SQL_CHAR) but it is returning YYYY-MM-DD format.

How can i specify style in the CONVERT function ?

Short answer, CONVERT doesn't do anything but convert the data type. It does not alter the display in any way.
You should do that in your application. if you can't do it in your app (like when using Crystal Reports), you could format it your self like:
Don't forget to convert it back to YYYY-MM-DD before using it in a restriction or an INSERT/UPDATE statement.

Thanks for the reply. Another related question is that if i am returning the following format for date of birth in my sql query with date and time. How do i just get the date and not the time.

The format i am returning is 4/23/2008 12:00:00 .

Do i use substring, or left function and how would i use it to just get 4/23/2008 ?

I used SUBSTRING(B."SchDate",1,10) and LEFT(B."SchDate",10) but that did not work.

I would actually use the Date functions: