Convert Date (dd-MM-YYYY) to (dd/MM/YYYY)

[Gunapriyan]

Dear Sir,

In my DB, there is 5000 date values are stored like "DD-MM-YYYY" Format in nvarchar datatype. For Example 18-10-2010

And when i convert into date format for the following purpose.

"Select * from tbl_master order by Convert(datetime,date_of_Appln,103)"

Its not working.

Can you help me to convert? or Can you help me to replace the "-" symbol to "/"?

Thanks in advance.

Regards,

Guna

 

[bborissov]

Guna,
How date and/or datetime fields are stored have nothing to do with the way they are presented.

Select * 
       from tbl_master
order by date_of_Appln

is enough to get ordered recordset.
How this date/datetime will be presented to your user is your frontend job.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Gunapriyan]

Dear Friend,

Thanks for your nice reply.

It returns like,

02/07/2009
02/07/2010
02/08/2007
02/09/2008
02/09/2008


----01,02,03 (Only Date)

Whole thing must orderna?

I expect to return like,

02/08/2007
02/09/2008
02/09/2008
02/07/2009
02/07/2010

----This is correct.

Can you help me?

- GUNA

 

[bborissov]

What is the real type of the field date_of_Appln?
If the field is with datetime type you will get the right order, if you use some char type then you should get this order.
That is one of the reasons why you always should use the right type for thew value.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[drlex]

Assuming all your dates are in DD-MM-YYYY format (i.e. no D-M-YY or similar), you could just use some string functions to re-order.

Select * from tbl_master order by [b]right(date_of_Appln,4) + substring(date_of_Appln,3,2) + left(date_of_Appln,2)[/b]

I'll echo bborissov - best to store data as the appropriate type to avoid all this malarky.


soi là, soi carré