Solved

Valid Date Time format

Posted on 2014-11-05
3
192 Views
Last Modified: 2014-11-06
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
0
Comment
Question by:AlexPonnath
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40425618
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40425629
|        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
 

Author Comment

by:AlexPonnath
ID: 40426485
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

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question