Date Conversion

I have a table that has '09042003143404' which is Date and Time Combined in
a varchar type field. I am trying to Insert it into another table that has
a DateTime type field. I have tried Convert and Cast. I am not sure if I
can even do this! Any ideas?
The code that I have is
Insert Into MasterJobLog
(RSID,
Code,
DateStamp,
DocumentName,
Pages,
Cost,
Client,
PatronID,
Printer,
DocumentType )
Select 'RS101', Code, DateStamp,
DocumentName,Pages,Cost,Client,PatronID,Printer,DocumentType from RS101
Thanks
ArthurNot very pleasant, but it works, perhaps someone else will come up with a
slicker way...
create table test( a varchar(100) not null)
go
insert into test values ('09042003143404')
GO
select convert(datetime,substring(a,5,4) + substring(a,1,2)
+ substring(a,3,2) + ' ' + substring(a,9,2) + ':'
+ substring(a,11,2) + ':' + substring(a,13,2)) from test
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Arthur C" <arthur.christy@.tamut.edu.delete.me> wrote in message
news:#bCqoVzlDHA.2268@.TK2MSFTNGP12.phx.gbl...
> I have a table that has '09042003143404' which is Date and Time Combined
in
> a varchar type field. I am trying to Insert it into another table that
has
> a DateTime type field. I have tried Convert and Cast. I am not sure if I
> can even do this! Any ideas?
>
> The code that I have is
> Insert Into MasterJobLog
> (RSID,
> Code,
> DateStamp,
> DocumentName,
> Pages,
> Cost,
> Client,
> PatronID,
> Printer,
> DocumentType )
> Select 'RS101', Code, DateStamp,
> DocumentName,Pages,Cost,Client,PatronID,Printer,DocumentType from RS101
>
> Thanks
> Arthur
>|||> I have a table that has '09042003143404' which is Date and Time Combined
in
> a varchar type field.
Ugh, why?
> I am trying to Insert it into another table that has
> a DateTime type field.
INSERT otherTable(DateTime_Column_Not_Field)
SELECT CONVERT
(
DATETIME,
SUBSTRING(Silly_Varchar_Column_Not_Field, 5, 4)
+ SUBSTRING(Silly_Varchar_Column_Not_Field, 1, 2)
+ SUBSTRING(Silly_Varchar_Column_Not_Field, 3, 2) + ' ' +
+ STUFF(STUFF(RIGHT(Silly_Varchar_Column_Not_Field, 6), 5, 0, ':'), 3,
0, ':')
)
FROM OriginalTable|||hehehhe...lets stuff the turkey...
select cast(stuff(stuff(stuff(stuff(a,13,0,':'),11,0,':'),9,0,left(a,4)+'
'),1,4,'') as datetime) dt
from test
--
-oj
Rac v2.2 & QALite!
http://www.rac4sql.net
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23A%23r41zlDHA.3256@.tk2msftngp13.phx.gbl...
> Not very pleasant, but it works, perhaps someone else will come up with a
> slicker way...
> create table test( a varchar(100) not null)
> go
> insert into test values ('09042003143404')
> GO
> select convert(datetime,substring(a,5,4) + substring(a,1,2)
> + substring(a,3,2) + ' ' + substring(a,9,2) + ':'
> + substring(a,11,2) + ':' + substring(a,13,2)) from test
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Arthur C" <arthur.christy@.tamut.edu.delete.me> wrote in message
> news:#bCqoVzlDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > I have a table that has '09042003143404' which is Date and Time Combined
> in
> > a varchar type field. I am trying to Insert it into another table that
> has
> > a DateTime type field. I have tried Convert and Cast. I am not sure if
I
> > can even do this! Any ideas?
> >
> >
> >
> > The code that I have is
> > Insert Into MasterJobLog
> > (RSID,
> > Code,
> > DateStamp,
> > DocumentName,
> > Pages,
> > Cost,
> > Client,
> > PatronID,
> > Printer,
> > DocumentType )
> > Select 'RS101', Code, DateStamp,
> > DocumentName,Pages,Cost,Client,PatronID,Printer,DocumentType from RS101
> >
> >
> > Thanks
> > Arthur
> >
> >
>|||That works. Thanks
"Arthur C" <arthur.christy@.tamut.edu.delete.me> wrote in message
news:%23bCqoVzlDHA.2268@.TK2MSFTNGP12.phx.gbl...
> I have a table that has '09042003143404' which is Date and Time Combined
in
> a varchar type field. I am trying to Insert it into another table that
has
> a DateTime type field. I have tried Convert and Cast. I am not sure if I
> can even do this! Any ideas?
>
> The code that I have is
> Insert Into MasterJobLog
> (RSID,
> Code,
> DateStamp,
> DocumentName,
> Pages,
> Cost,
> Client,
> PatronID,
> Printer,
> DocumentType )
> Select 'RS101', Code, DateStamp,
> DocumentName,Pages,Cost,Client,PatronID,Printer,DocumentType from RS101
>
> Thanks
> Arthur
>