SQL Data Conversion

Hi All,
please assist on following conversion in SQL Query
20071011 to 11-10-2007 as date (single column data)
and 102311 to 10:23:11 as time (single column data)
LVL 2
batman_kAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
you just need to replace that value with your column name


SELECT CONVERT(VARCHAR(10),CAST(StartDate AS DATETIME), 110)
from YourTable
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT CONVERT(VARCHAR(10),CAST('20071011' AS DATETIME), 110)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
For the second part

SELECT LEFT(urColumn,2) + ':' + SUBSTRING(urColumn, 3,2) +':'+RIGHT(urColumn,2)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
batman_kAuthor Commented:
Hi Aneesh,
Please help with Date part.
Column is called StartDate and data varies (is not always as 20071011)
Please send example
For time, comumn is called StartTime. Please send example

Thanks in advance!
0
 
batman_kAuthor Commented:
Hi Annesh,

Both examples are working...thanks!
One last thing on Time:
Time is 092010 for example. Conversion needs to make it as 09:20:10
example is working for 142320 aka 14:23:20

Thanks!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT LEFT('092010',2) + ':' + SUBSTRING('092010', 3,2) +':'+RIGHT('092010',2)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.