Solved

Conversion of (yyyyddmmhhmmss) to datetime

Posted on 2004-08-19
9
432 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 143

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:Scott Pletcher
Scott Pletcher 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

713 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