Retieving dates in SQL

[Guest_imported]

HI,
I am having a field whose datatype is Varchar(String)
which stores dates. When I retrieve these dates, I want them in the format-"00/00/0000" but instead, for single digit day/month it is giving-"0/0/0000".
For Eg a date like-"4/5/2001" stored in the table, i want it as -"04/05/2001" AND THIS I WANT IN SQL-QUERY ITSELF.
Thanx in advance for those who help
Regards
Mahesh

 

[JNC73]

Hello Mahesh,

I think you can try this.

First convert the VARCHAR to a date and then convert it back to a VARCHAR of a special type.
This means

SELECT CONVERT(VARCHAR(20),CTOD(<fieldname>), 114)

The last number you can lookup in the helpfile of Transact SQL to get the correct format of the datetime field.

Hope this helps,


JNC73

 

[tlbroadbent]

Great tip from JNC73.

If you want, you can do the conversion to date and then back to varchar in one statement. You can even retain the original column name.

convert(varchar, convert(datetime, <fieldname>), 101) As <fieldname> Terry

http://members.home.net/tlbroadbent/index.htm

 

[foxdev]

for single digit day/month it is giving-&quot;0/0/0000&quot;.

The two suggestions given are fool-proof, but probably the root cause of this behaviour is your Windows date format settings (Control Panel, Regional). The advantage of using the CONVERT approach is that they'll work for any workstation regardless of its settings, but if you want to avoid having to put this code in a multitude of places, try adjusting your Short Date format to be MM/dd/yyyy. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com