Solved

Conversion of (yyyyddmmhhmmss) to datetime

Posted on 2004-08-19
9
422 Views
Last Modified: 2010-05-19
Hi,

we are storing datetime(yyyymmddhhmmss) in the varchar datatype as string. I want to manupulate the time with that datestring.  I have used CONVERT(datetime,'20040816000000'), but failed. Please help me

Thank you
0
Comment
Question by:chemanth
9 Comments
 
LVL 11

Accepted Solution

by:
ram2098 earned 168 total points
ID: 11839248
You need to split and do it

select convert(datetime,convert(varchar(10),CONVERT(datetime,left('20040816000000',8),101),120) + ' ' + substring('20040816000000',9,2) + ':' + substring('20040816000000',11,2)+ ':' + substring('20040816000000',13,2))
0
 
LVL 11

Expert Comment

by:ram2098
ID: 11839257
The above displays you it in the valid datetime format. Is this want? Let me know if I am wrong.
0
 
LVL 3

Expert Comment

by:alexpreston
ID: 11839264
I think that's right. I was trying to think of a neater way, but I think this is the best way. It seems a lot of effort but since the date string is not in a recognised format, the fiddling around is necessary.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 166 total points
ID: 11839330
CONVERT arguments:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

As you can see, only ISO has a non-separator format, but without time (s%$@  M$ :-)

Thus, indeed you need to fiddle around with substrings etc (as shown above)

CHeers

0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 11839481
I saw ram2098's post after I had already done something similar.   Here it is anyway.   I don't know any other 'neater' way that this general method.  

ram2098's example gives this output: 2004-08-16 00:00:00.000

My example below gives this output:  2004-08-16 01:08:12.000 -- because I entered a time value in the string.  However, ram2098's solution produces the correct result on the time side too.  

select convert(datetime,left('20040816000000',8) +' '+ substring('20040816010812',9,2) + ':' + substring( '20040816010812',11,2) + ':' + right('20040816010812',2),108)

As for doing time calculations, here's a bit of an example:-

declare @time1 datetime
declare @time2 datetime

select @time1 = convert(datetime,left('20040816000000',8) +' '+ substring('20040816000000',9,2) + ':' + substring( '20040816000000',11,2) + ':' + right('20040816000000',2),108)

select @time2 = convert(datetime,left('20040816010812',8) +' '+ substring('20040816010812',9,2) + ':' + substring( '20040816010812',11,2) + ':' + right('20040816010812',2),108)

select datediff(minute,@time1,@time2)


AustinSeven
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 166 total points
ID: 11841940
I agree, you have to do some manipulation.  The shortest method I can figure out is:

DECLARE @v1 VARCHAR(20)
SET @v1 = '20040816000000'

SELECT CAST(LEFT(@v1, 8) + ' ' + STUFF(STUFF(SUBSTRING(@v1, 9, 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
0
 
LVL 3

Expert Comment

by:Ayesha_K
ID: 11842759
you can also select minutes and seconds from the database like

select minute(Mydate),second(MyDate) from ...

Regards
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now