• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

How to write a SQL Statement which can convert a 8-Char “TEXT” Field into a ”DATETIME” Field.

Please help me to write a SQL SERVER 2005 statement which can convert a 8-Char “TEXT” Field into”DATETIME” Field.


To simplify the issue; say my SQL DB has a single table called “TestTbl;” and this ‘TestTbl” has only one 8-Char text field, namely “DATE” with the data (3 records) as follows:

DATE        (originally as 8-Char Text)

20111017
20110830
20110725

The requested query statement will give the output after its execution as follows:


DATE         (now is simple "datetime" field)

10/17/2011
0-8/30/2011
07/25/2011

Thanks.


van
0
tranvangiang17
Asked:
tranvangiang17
  • 2
1 Solution
 
tranvangiang17Author Commented:
Typo correction:

Output Data should read as follows instead:


DATE         (now is simple "datetime" field)

10/17/2011
08/30/2011
07/25/2011


Thanks.
0
 
Kevin CrossChief Technology OfficerCommented:
Those values should convert to DATETIME with no problem. Try simply:

CAST([DATE] AS DATETIME)

The date format is ISO (112) which is YYYYMMDD and should convert automatically, but you can specify the format explicitly.
BOL: http://msdn.microsoft.com/en-us/library/ms187928.aspx
CONVERT(DATETIME, [DATE], 112)

Now, if you want to force the display to be US (101) - MM/DD/YYYY - on output, you can then convert this in the same manner:
CONVERT(CHAR(10), CONVERT(DATETIME, [DATE], 112), 101)

Hope that helps!
0
 
tranvangiang17Author Commented:
Dear mwvisa1,

It is working just fine.   You are my hero.

Thanks.

Van
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now