asked on

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

PortletPaul

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AlexPonnath

ASKER

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