January 18, 2005 at 8:46 am
Friends,
I have a table with datetime column, when i alter the column to varchar I'm losing seconds and milliseconds. Could any one suggest if there is a way to alter the column to varchar without losing seconds and milliseconds part.
I don't prefer to add column and do updates etc etc......
Thanks in advance
January 18, 2005 at 9:06 am
Try this way
select convert(varchar(25),getdate(),21)
Thanks,
Ganesh
January 18, 2005 at 9:45 am
Here is my situation,
tOrders(dtmOrdered datetime)
Alter table tOrders alter column dtmOrdered varchar(26)
when I do this:
2005-01-18 10:34:50.677 is changed to Jan 18 2005 10:34AM, it's not preserving seconds and milliseconds part, I guess it's default behaviour.
Is there a way to preserve existing data without losing seconds and milliseconds
January 18, 2005 at 10:25 am
Add a column, insert the data and then remove the original column. Question is though WHAT importance are seconds/milliseconds AND WHY do you need to convert this to VARCHAR instead of performing a FORMAT during output?
Good Hunting!
AJ Ahrens
webmaster@kritter.net
January 19, 2005 at 1:46 am
check out the ISO8601 style of the convert function. This will give you everything in an internationally recognised textual format.
select convert(varchar,getdate(),126)
= 2005-01-19T08:35:05.507
That's the format I prefer.
regards,
Mark Baekdal
+44 (0)208 241 1762
Database change management for SQL Server
January 19, 2005 at 2:03 am
or you could use this:
select convert(varchar(12),getdate()) + right(convert(varchar,getdate(),126),len(convert(varchar,getdate(),126))-charindex('T',convert(varchar,getdate(),126)))
result = Jan 19 2005 08:48:19.397
depending on your preferencing for displaying the date to your users.
regards,
Mark Baekdal
+44 (0)208 241 1762
Database change management for SQL Server
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply