Solved

Valid Date Time format

Posted on 2014-11-05
3
164 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
  • 2
3 Comments
 
LVL 48

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 48

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now