Valid Date Time format

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
AlexPonnathAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
|        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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlexPonnathAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

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.