Skip to main content
tims4831424
Inspiring
January 21, 2021
Answered

Split Value DATETIME

  • January 21, 2021
  • 2 replies
  • 355 views

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

    This topic has been closed for replies.
    Correct answer BKBK
    <!--- 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>

    2 replies

    James Moberg
    Inspiring
    January 21, 2021

    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
     
    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    January 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  = 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>