Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Valid Date Time format

Posted on 2014-11-05
3
Medium Priority
?
205 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 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 2000 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
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…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 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