Split Value DATETIME

[tims4831424]

Hi i have an value:  20191111153402

IT is: YYYYMMDDHHMMSS

How can i convert it in an mysql DATETIME Format?

Anybody an idea?

Kindly Regards

Thorsten

[BKBK · Accepted solution]

<!--- Function to convert time-value of the form YYYYMMDDHHMMSS into a datetime --->
<cffunction name="convertTimeValueToDatetime"  returntype="date"><!--- Alternative: returntype="string" --->
	
	
	<!--- Input format: YYYYMMDDHHMMSS --->
	<cfargument name="timeValue" required="true" type="string">
	
	<!--- Extract the digits for year, month, day, hour, minute and second --->
 	<cfset var yr   = left(arguments.timeValue, 4)>
 	<cfset var mnth = mid(arguments.timeValue,5,2)>
 	<cfset var day  = mid(arguments.timeValue,7,2)>
 	<cfset var hr   = mid(arguments.timeValue,9,2)>
 	<cfset var min  = mid(arguments.timeValue,11,2)>
 	<cfset var sec  = right(arguments.timeValue,2)>
 	
 	<!--- Basic validation. (Roll out your own! 🙂  --->
 	<cfset var isYearValid = yr gte 1000 and yr lte 2200>
 	<cfset var isMonthValid = mnth gte 1 and mnth lte 12>
 	<cfset var isDayValid = day gte 1 and day lte 31>
 	<cfset var isHourValid = hr gte 0 and hr lte 24>
 	<cfset var isMinuteValid = min gte 0 and min lte 59>
 	<cfset var isSecondValid = sec gte 0 and sec lte 59>
 	
 	<cfif isYearValid and 
 	      isMonthValid and 
 	      isDayValid and
 	      isHourValid and
 	      isMinuteValid and
 	      isSecondValid>
 		 <cfreturn createDatetime(yr,mnth,day,hr,min,sec)><!--- Alternative: <cfreturn yr & "-" & mnth & "-" & day & " " & hr & ":" & min & ":" & sec> --->
 	<cfelse>
 		<cfthrow message="Error: the input time-value is not valid.">
 	</cfif>

</cffunction>

<cfset testValue = convertTimeValueToDatetime("20010121183359")>
<cfoutput>#testValue#</cfoutput>

[James Moberg]

I have two UDFs, DateHash and DateUnhash, that converts dates & strings back and forth.  (We use this function alot.)

https://gist.github.com/JamoCA/8961085

It can also handle the following formats:

• yyyymmddHHmmss
• yyyymmddHHmm
• yyyymmddHH
• yyyymmdd
• yyyymm
• yyyy