Problem with converting datetime format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with converting datetime format

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2009-01-28 : 09:01:41

I have a editdate(varchar(15)) column in my SQL table and its values like 'Apr 20 2008'. I want to change all data present in editdate column into 4-20-2008(US format date). I have 3200 rows in the table. I have to change all rows in the table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:06:55

use convert(). but why are you using varchar to store datetime? it really makes date manipulation difficult. ALso for display purpose you can just get date in reqd format at front end using formatting functions available there

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:07:24

also see this

http://msdn.microsoft.com/en-us/library/ms187928.aspx

vidhya
Posting Yak Master

108 Posts

Posted - 2009-01-28 : 09:10:01

hi,

I have query to convert a single date in the table ...but i need to convert all data in the table at same time.

CONVERT(varchar(10), Convert(DATETIME,Convert(VARCHAR,GetDate())), 101)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:11:33

[code]SELECT CONVERT(varchar(10), Convert(DATETIME,Convert(VARCHAR,yourdatecolumn)), 101) FROM YourTable[/code]

vidhya
Posting Yak Master

108 Posts

Posted - 2009-01-28 : 09:29:30

i need to update all the datas in the table at the same time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:32:43

all the datas? what all datas? how do you think we can imagine what all fields you want to update? so please provide some sample data and explain what you want to update

vidhya
Posting Yak Master

108 Posts

Posted - 2009-01-28 : 09:40:14

i have editdate(varchar(15)) column in vendor table. it contains 3200 rows. Each row have different value in that column like 'Apr 20 2008'. i need to update this values as 4-20-2008 in my database.
eg:
editdate-->column editdate-->column
Apr 20 2008 4-20-2008
May 28 2006 5-28-2006

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:44:50

[code]UPDATE YourTable
SET editdate=CONVERT(varchar(10), Convert(DATETIME,Convert(VARCHAR, editdate)), 101)
[/code]

vidhya
Posting Yak Master

108 Posts

Posted - 2009-01-28 : 09:59:29

instead of null value it displays the value 01/01/1900

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 10:11:08

was it NULL or '' before?