Solved

Valid Date Time format

Posted on 2014-11-05
3
179 Views
Last Modified: 2014-11-06
I have a string which looks like this "110414 100354" the first part is the Date, the second part is the time. How can I quicly convert this into a valid DateTime format for a SQL server

Thanks
0
Comment
Question by:AlexPonnath
[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
  • 2
3 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40425618
The date portion (assuming YYMMDD) can be handled with this

convert( datetime*,  your_field_here ,12)

* date or datetime or datetime2 or smalldatetime

The time however needs colons between the units
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40425629
|        FIELDX |             DT120 |                    DATETIME_120 |
|---------------|-------------------|---------------------------------|
| 110414 100354 | 11-04-14 10:03:54 | November, 04 2014 10:03:54+0000 |

Open in new window

4
This result was produced by the following. basically it forms a date style equal to YY-MM-DD hh:mm:ss which equates to style number 120

The sql:
select
      *
      , convert(datetime,ca.dt120) as datetime_120
from table1
cross apply (
  select
         substring(fieldx,1,2) + '-'
       + substring(fieldx,3,2) + '-'
       + substring(fieldx,5,2) + ' '
       + substring(fieldx,8,2) + ':'
       + substring(fieldx,10,2) + ':'
       + substring(fieldx,12,2)
  ) ca (dt120)

Open in new window

I used a cross apply but you don't have to do that way.

For date style references see:
http://www.experts-exchange.com/Database/MS-SQL-Server/A_12315-SQL-Server-Date-Styles-formats-using-CONVERT.html
0
 

Author Comment

by:AlexPonnath
ID: 40426485
Thanks, I was hoping there was a nicer way but couldn't find anything, that's why I build a function like this
<cffunction name="basicdatetime" > 
    <cfargument name="datestr" required="true" type="string"> 
    	<cfif len(datestr) is 13>
			<cfset tmpdate = left(datestr,2) & "/" & mid(datestr, 3,2) & "/" & mid(datestr, 5,2) & " " & mid(datestr, 8,2) & ":" & mid(datestr, 10,2) &  ":" & mid(datestr, 12,2) >
				<cfif IsDate(tmpdate)>
                	<cfset tmpdate = DateTimeFormat(tmpdate, "mm/dd/yyyy HH:nn:ss")>
                <cfelse>
					<cfset tmpdate = "01/01/1900">
				</cfif>
			<cfset tmpdate = DateTimeFormat(tmpdate, "mm/dd/yyyy HH:nn:ss")>
		<cfelse>
			<cfset tmpdate = "01/01/1900 00:00:00">
		</cfif>
    <cfreturn tmpdate> 
</cffunction>

Open in new window


at least I can reuse it and it checks if its a valid date before I build a sql date string, I could even go further and validate that the time segments in field 1 is <= 24 and segment 2 and 3 is <=60
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 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