Converting one format of Datetime to format of date

Hi

In the database Date field is defined as Datetime For example it is stored as 2003-04-09 00:00:00 .If i want to convert that Datetime field into Date of format DD/MM/YYYY .what I should i write for this in the Select Query .

Note:
After Converting from Datetime to Date I want that Field type tobe Date Not Varchar.

Pls Help me out

From Anitha

I think it should be somseting like this

SELECT convert(datetime, '2003-09-04', 103) FROM dummy;

103 is the format for dd/mm/yyyy i think.

(am not a t work so can not test it, will check for it tomorrow if you stil need it)

Hi

Still Not working.

Convert(datetime,'2003-03-09',103) .It is giving the result with Time for example

09/03/2003 00:00:00 .000

but i want only
09/03/2003 in date datetype not varchar type

select col1, col2
From thisTable
WHERE convert (datetime, convert (varchar, datecol, 101), 101) = @yourdate

This also returns the date with Time.
I am also facing the same problem and looking for the solution...please if someone can help?

thanks in advance,

When you convert to a datetime, it is always going to show the time when you display the variable. This is by the definition of the datetime data-type. If you want it to only show DD/MM/YYYY, then you should convert it to varchar type.