×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Convert cyymmdd to mmddyyyy

Convert cyymmdd to mmddyyyy

Convert cyymmdd to mmddyyyy

(OP)
Hello Again,

I've got funky date fields coming from an AS/400 that I need to convert to an accepted date by SQL. I'm looking for a fast/correct way to do this for a SQL View I'm creating.


Today's date on the iSeries (AS/400) looks like this: 01061204 (ccyymmdd)

I need the dates to look like 12/04/2006 (mm/dd/yyyy)

If I add 19000000 to the iSeries date I get the current date but in the wrong format (yyyymmdd).

Does anyone have a 'correct' way to convert the resulting yyyymmdd into mm/dd/yyyy?


RE: Convert cyymmdd to mmddyyyy

(OP)
... or more specifically the 'correct' way to convert the date coming from the iSeries to the mm/dd/yyyy format?

In other words, is there a way to add, convert, and arrange the date info in one step?

RE: Convert cyymmdd to mmddyyyy

Add 19000000, then convert to string, then convert to date.

Select Convert(DateTime, Convert(VarChar(8), TheDateField + 19000000))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

RE: Convert cyymmdd to mmddyyyy

mm/dd/yyyy is just a formatting thing.  The absolute safest way to deal with dates (behind the scenes) is to use the ISO unseperated date format.  
    yyyymmdd
No spaces. No dash, no slash, no nothing!


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

RE: Convert cyymmdd to mmddyyyy

>>yyyymmdd
No spaces. No dash, no slash, no nothing!

not true  winky smile what about quotes  hehehehe

Denis The SQL Menace
SQL blog:http://sqlservercode.blogspot.com/

RE: Convert cyymmdd to mmddyyyy

(OP)
Thanks for the quick reply gmmastros!

Something I forgot to say was that this format varchar(10) MM/DD/YYYY is a required format for a data warehouse dump I have to give corporate.

However, I'm guessing that since the field is a varchar instead of a char, my giving them an 8 character field won't be a problem (and if it is, I'm sure they'll let me know).

Is there a way to do as you suggest (add 19000000, convert to string, convert to date, THEN REARRANGE to MMDDYYY. All in the same step or is that going to significantly slow response time to queries?

RE: Convert cyymmdd to mmddyyyy

Select Convert(VarChar(10), Convert(DateTime, Convert(VarChar(8), TheDateField + 19000000)), 101)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

RE: Convert cyymmdd to mmddyyyy

(OP)
*sigh*

Thanks gmmastros, you make it look so easy.

The funny/sad thing is that I had looked at the CAST and CONVERT BOL entry, but had not yet made heads or tails of it.

Now that I've seen your code I get how it works.

Thanks again!

RE: Convert cyymmdd to mmddyyyy

(OP)
Trouble in paradise!

Using the above code I can select random records fine, however, doing a select top 500 * gets me the following error.

CODE

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.

RE: Convert cyymmdd to mmddyyyy

You must have bad data.  To find the bad data...

CODE

Select *
From   Table
Where  IsDate(Convert(VarChar(8), TheDateField + 19000000)) = 0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

RE: Convert cyymmdd to mmddyyyy

(OP)
You are quite correct, I found 3972 items where the original row value was '0'.

I used the following code to fix the problem, and hopefully this 'help request' is put to bed. ;0)

CODE

CREATE VIEW INVOICE_DATA
AS
SELECT
  'LOCATION' AS BU_KEY
  ,CASE
     WHEN MBF9.FEGHNB = '0' THEN '0'
     WHEN MBF9.FEGHNB <> '0' THEN CONVERT(VarChar(10), MBF9.FEGHNB + 19000000 , 101)
  END AS INVOICE_DATE
...
  ,PRD_KEY AS PRODUCT_KEY

FROM
  MyTable

Again, thanks for all the help!

Steve

RE: Convert cyymmdd to mmddyyyy

(OP)
Oops!

I was playing with your code to see what would happen if I took out one of the convert's.

the correct code should read...

CODE

  ,CASE
     WHEN MBF9.FEGHNB = '0' THEN '0'

     WHEN MBF9.FEGHNB <> '0' THEN CONVERT(VarChar(10), Convert(DateTime, Convert(VarChar(8), MBF9.FEGHNB + 19000000)), 101)

  END AS INVOICE_DATE

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close