?
Solved

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

Posted on 2011-10-17
3
Medium Priority
?
347 Views
Last Modified: 2012-05-12
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
Comment
Question by:tranvangiang17
  • 2
3 Comments
 

Author Comment

by:tranvangiang17
ID: 36983083
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36983384
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
 

Author Closing Comment

by:tranvangiang17
ID: 36989715
Dear mwvisa1,

It is working just fine.   You are my hero.

Thanks.

Van
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
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…

755 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