Change text to datetime

[scc]

I have a field that is char(14) and I would like to write a query (for a view) that will return a datetime field that will be recognized by MS Access as a datetime field.

Here's a sample of the data in stored in SQL Server:

20041215163407

the field is named DeliveryTime

Can someone help with this?

TIA

 

[mwolf00]

SELECT
convert(smallDateTime,''''
+ substring(txt,5,2) + '/'
+ substring(txt,7,2) + '/'
+ substring(txt,1,4) + ' '
+ substring(txt,9,2) + ':'
+ substring(txt,11,2) + ':'
+ substring(txt,13,2)
)
FROM myTable

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mwolf00]

Darn - forgot the end

SELECT
convert(smallDateTime,''''
+ substring(txt,5,2) + '/'
+ substring(txt,7,2) + '/'
+ substring(txt,1,4) + ' '
+ substring(txt,9,2) + ':'
+ substring(txt,11,2) + ':'
+ substring(txt,13,2)
+ '''')
)
FROM myTable

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[dbomrrsm]

above gives error

Syntax error converting datetime from character string.

this does it:

declare @string char (14)
set @string = '20041215163407'
set dateformat MDY
SELECT CONVERT(datetime,
''+ substring(@string,5,2) + '/'
   + substring(@string,7,2) + '/'
   + substring(@string,1,4) + ' '
   + substring(@string,9,2) + ':'
   + substring(@string,11,2) + ':'
   + substring(@string,13,2))

[blue]DBomrrsm[/blue]

 

[scc]

I'm having problems getting either of these to work.

dbomrrsm: I don't know how I can use a field to bring back the data instead of hardcoding it...

mwolf00: this brings back an error that says--

The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

 

[mwolf00]

SELECT
  convert(smallDateTime,'' 
   + cast(substring(txt,5,2) as char(2)) + '/'
   + cast(substring(txt,7,2) as char(2)) + '/'
   + cast(substring(txt,1,4) as char(4)) + ' '
   + cast(substring(txt,9,2) as char(2)) + ':'
   + cast(substring(txt,11,2) as char(2)) + ':'
   + cast(substring(txt,13,2) as char(2))
   + '')
 )
FROM myTable

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[scc]

Exactly what I needed!

Thanks!!