silchester
asked on
Manipulating DateTime Strings
Hi
How do I efficiently convert a string being supplied to my SQL of the form:
"2009-07-29:13:25"
into the SQL datetime format:
"07/29/2009 1:25:00 PM" ?
My second issue:
I have been reading about UTC in the forum, and can see it is complex if dealing with different systems, ie MAC, Linux etc. But, at the end of the day, if I am just using Windows XP with SQL 2005 Server Express, is it true to say that the UTC datetime in SQL will be the same as the UTC datetime in another application on the same computer?
In other words: for the above datetime will the UTC value be the same regardless of the software application using it - ie. so long as I convert the datetime to a UTC value this will then be 'portable', at least on the same PC?
How do I efficiently convert a string being supplied to my SQL of the form:
"2009-07-29:13:25"
into the SQL datetime format:
"07/29/2009 1:25:00 PM" ?
My second issue:
I have been reading about UTC in the forum, and can see it is complex if dealing with different systems, ie MAC, Linux etc. But, at the end of the day, if I am just using Windows XP with SQL 2005 Server Express, is it true to say that the UTC datetime in SQL will be the same as the UTC datetime in another application on the same computer?
In other words: for the above datetime will the UTC value be the same regardless of the software application using it - ie. so long as I convert the datetime to a UTC value this will then be 'portable', at least on the same PC?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>I have been reading about UTC in the forum, and can see it is complex if dealing with different systems, ie MAC, Linux etc. But, at the end of the day, if I am just using Windows XP with SQL 2005 Server Express, is it true to say that the UTC datetime in SQL will be the same as the UTC datetime in another application on the same computer?<<
I guess in same computer, it will remain same.
I guess in same computer, it will remain same.
ASKER
Incredible! Fantastic, after thinking I was going to have to get another app to manipulate it all etc etc. Good one.
Am just having trouble using it in a computed column for a table:
convert(varchar(50),CONVER T(datetime ,left([DAT ETIME TO CONVERT],10)+' '+right([DATETIME TO CONVERT,5),120),22)
Or am I going to have to put this into an after insert trigger?
Cheers
Am just having trouble using it in a computed column for a table:
convert(varchar(50),CONVER
Or am I going to have to put this into an after insert trigger?
Cheers
ASKER
PS. Can I be cheeky and ask if there is a function that wil then take this converted datetime and give me the UTC value ?
Thanks
Thanks
well you can put it in trigger for sure but don't understand what you want to say by "Computed Column for a table" can you please elaborate more?
ASKER
Well, in the SQL 2005 table designer a column can be made a 'computer column' by putting a formula into its column properties. So, I tried inserting the convert code into it, but it does not like it - so i guess it does not see this code line as a 'formula'.
I have just tired it in a trigger, and it works. I just thought that the computed column would have been a neater way of automatically filling in a colum with the converted value (another column is holding the unconverted value).
I have just tired it in a trigger, and it works. I just thought that the computed column would have been a neater way of automatically filling in a colum with the converted value (another column is holding the unconverted value).
first computed column is not a physical column and it used to compute the value as and when demanded so directly insert in computed column will not be possible.
ASKER
Thanks Ritesh - really helped me there.
Open in new window