Split Value DATETIME

Explorer ,
Jan 21, 2021 Jan 21, 2021

Copy link to clipboard

Copied

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

Views

81

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Adobe Community Professional , Jan 21, 2021 Jan 21, 2021
<!--- 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 = m...

Likes

Translate

Translate
Adobe Community Professional ,
Jan 21, 2021 Jan 21, 2021

Copy link to clipboard

Copied

<!--- 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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jan 21, 2021 Jan 21, 2021

Copy link to clipboard

Copied

LATEST

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
 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines