Convert string to DateTime

Hi.
I get dates from a text file on this format: 200604281050. What would be the
easiest way to convert it to DateTime format, from char(12)? The example
should be as in: 04.28.2006 10:50 AM (or 28.04.2006. 10:50).
Do I have to build the DateTime field manually through PL/SQL ?
Any hints appreciated
Kind regards
Mr. SmithCAST(LEFT(YourCol,8) + ' ' + RIGHT(YourCol,4) AS DATETIME)
Andrew J. Kelly SQL MVP
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:%23a%23OiT5kGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Hi.
> I get dates from a text file on this format: 200604281050. What would be
> the easiest way to convert it to DateTime format, from char(12)? The
> example should be as in: 04.28.2006 10:50 AM (or 28.04.2006. 10:50).
> Do I have to build the DateTime field manually through PL/SQL ?
> Any hints appreciated
> Kind regards
> Mr. Smith
>|||Andrew, it failed
DECLARE @.st VARCHAR (20)
SET @.st='200604281050'
--select CAST(LEFT(@.st,8) + ' ' + RIGHT(@.st,4) AS DATETIME)
--Server: Msg 242, Level 16, State 3, Line 4
--The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
SELECT CAST(LEFT(@.st,8)+' ' +SUBSTRING(@.st,9,2)+':'+RIGHT(@.st,2) AS
DATETIME)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ecRpjY5kGHA.1264@.TK2MSFTNGP05.phx.gbl...
> CAST(LEFT(YourCol,8) + ' ' + RIGHT(YourCol,4) AS DATETIME)
> --
> Andrew J. Kelly SQL MVP
> "Mr. Smith" <nospam@.blindfolded.gone> wrote in message
> news:%23a%23OiT5kGHA.1640@.TK2MSFTNGP02.phx.gbl...
>|||Try this:
DECLARE @.st VARCHAR (20)
SET @.st='200604281050'
SELECT CONVERT(DATETIME, LEFT(@.st, 8), 112)+CONVERT(DATETIME, SUBSTRING(@.st,
9, 2) + ':' + SUBSTRING(@.st, 11, 2), 114)|||Sorry you need to add the :
Andrew J. Kelly SQL MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eaEycg5kGHA.3512@.TK2MSFTNGP03.phx.gbl...
> Andrew, it failed
> DECLARE @.st VARCHAR (20)
> SET @.st='200604281050'
> --select CAST(LEFT(@.st,8) + ' ' + RIGHT(@.st,4) AS DATETIME)
> --Server: Msg 242, Level 16, State 3, Line 4
> --The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> SELECT CAST(LEFT(@.st,8)+' ' +SUBSTRING(@.st,9,2)+':'+RIGHT(@.st,2) AS
> DATETIME)
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ecRpjY5kGHA.1264@.TK2MSFTNGP05.phx.gbl...
>|||Mr Smith
Why are you using PL/SQL? Isn't that Oracle's SQL dialect?
HTH
Kalen Delaney, SQL Server MVP
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:%23a%23OiT5kGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Hi.
> I get dates from a text file on this format: 200604281050. What would be
> the easiest way to convert it to DateTime format, from char(12)? The
> example should be as in: 04.28.2006 10:50 AM (or 28.04.2006. 10:50).
> Do I have to build the DateTime field manually through PL/SQL ?
> Any hints appreciated
> Kind regards
> Mr. Smith
>|||hehe - I was waiting for someone to ask that question. ;)
*mike hodgson*
http://sqlnerd.blogspot.com
Kalen Delaney wrote:

>Mr Smith
>Why are you using PL/SQL? Isn't that Oracle's SQL dialect?
>
>|||So why didn't you?
--
HTH
Kalen Delaney, SQL Server MVP
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:%23C92PRClGHA.145
6@.TK2MSFTNGP04.phx.gbl...
hehe - I was waiting for someone to ask that question. ;)
mike hodgson
http://sqlnerd.blogspot.com
Kalen Delaney wrote:
Mr Smith
Why are you using PL/SQL? Isn't that Oracle's SQL dialect?|||What I meant was when I was reading through the thread I was thinking
"did anyone notice that the original poster mentioned PL/SQL" and then I
got to your post (Mozilla Thunderbird displays yours last in the thread
when the thread is collapsed). As such I didn't need to ask because you
already had.
*mike hodgson*
http://sqlnerd.blogspot.com
Kalen Delaney wrote:
> So why didn't you?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:%23C92PRClGHA.1456@.TK2MSFTNGP04.phx.gbl...
> hehe - I was waiting for someone to ask that question. ;)
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Kalen Delaney wrote:
>|||:-)
HTH
Kalen Delaney, SQL Server MVP
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:uUF31dClGHA.4776@.TK2MSFTNGP05.phx.gbl...
What I meant was when I was reading through the thread I was thinking "did
anyone notice that the original poster mentioned PL/SQL" and then I got to
your post (Mozilla Thunderbird displays yours last in the thread when the
thread is collapsed). As such I didn't need to ask because you already had.
mike hodgson
http://sqlnerd.blogspot.com
Kalen Delaney wrote:
So why didn't you?
HTH
Kalen Delaney, SQL Server MVP
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:%23C92PRClGHA.1456@.TK2MSFTNGP04.phx.gbl...
hehe - I was waiting for someone to ask that question. ;)
mike hodgson
http://sqlnerd.blogspot.com
Kalen Delaney wrote:
Mr Smith
Why are you using PL/SQL? Isn't that Oracle's SQL dialect?