datetime problem

Question

Post reply

datetime problem

Chinna

SSCertifiable

Points: 5408
More actions
February 26, 2008 at 7:49 am

#183367

i have a column of date with other format i need to change it update it ..... i got the date in Mar 13 2000 12:00AM format but i need to update it to 2000-03-13 00:00:00 format how can i do that plz let me know ....thkz in adavnce
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the data type of the column?

If it's datetime, it doesn't have a format. The format of a datetime is solely due to the way it's queried.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chinna SSCertifiable Points: 5408 More actions · Answer 2

the datatype in the table for my column is nvarchar(50)...so how would i convert it to datatimestamp or that format of output

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Look up convert. There should be an appropriate style that you can use to convert the date into a datetime. Once it's in a datetime, you can convert it back to a varchar if you wish or (what would probably be better) store it as a datetime .

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 4

Try:

select cast(yourcolumn as datetime)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Chinna SSCertifiable Points: 5408 More actions · Answer 5

when i jus click modify on my column and change the datatype to datetime it says

- Warning: Data might be lost converting column 'Invoice Date' from 'nvarchar(50)

i tried the query u gave me but it wrks out ...but i jus want to update it in my table ...i tried using that this way but it did not change

update table

set [invoice date]= cast([invoice date] as datetime]

it gets executed but i dnt find any change in the date format ....

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

If all the rows cast to datetime without an error, then you can ignore that warning and change the data type.

Take a backup of the data first, of course, just in case. (or export the table to a file if you prefer)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass