Convert cyymmdd to mmddyyyy 1

[Qmoto]

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?

 

[Qmoto]

... 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?

 

[gmmastros]

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

 

[gmmastros]

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

 

[SQLDenis]

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

not true ;-) what about quotes hehehehe

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Qmoto]

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?

 

[gmmastros]

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

-George

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

 

[Qmoto]

*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!

 

[Qmoto]

Trouble in paradise!

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

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.

 

[gmmastros]

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

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

-George

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

 

[Qmoto]

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)

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

 

[Qmoto]

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...

  ,CASE
     WHEN MBF9.FEGHNB = '0' THEN '0'
[COLOR=blue]
     WHEN MBF9.FEGHNB <> '0' THEN CONVERT(VarChar(10), Convert(DateTime, Convert(VarChar(8), MBF9.FEGHNB + 19000000)), 101)
[/color]
  END AS INVOICE_DATE