[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Manipulating DateTime Strings

Posted on 2009-07-03
9
Medium Priority
?
285 Views
Last Modified: 2012-05-07
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?
0
Comment
Question by:silchester
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24770860
if you have not mistaken in giving date than look at this one





declare @str varchar(50)
set @str='2009-07-29 13:25'
 
select convert(varchar(50),CONVERT(datetime,@str,120),22)

Open in new window

0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 2000 total points
ID: 24770868
if you really have ":" between date and time than consider this one



declare @str varchar(50)
set @str='2009-07-29:13:25'
 
select convert(varchar(50),CONVERT(datetime,left(@str,10)+' '+right(@str,5),120),22)

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24770883
>>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.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:silchester
ID: 24770949
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),CONVERT(datetime,left([DATETIME 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
0
 

Author Comment

by:silchester
ID: 24770959
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
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24770964
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?
0
 

Author Comment

by:silchester
ID: 24771057
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).
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24771078
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.
0
 

Author Closing Comment

by:silchester
ID: 31599491
Thanks Ritesh - really helped me there.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question