How to change date format

[ancheswaroop]

How to change date format from mm dd(02 01) to yyyymmm (2006032)

 

[blom0344]

These look like string representations.
I suppose you could work with substr(), || and '2006' to convert the formats

What is yyyymmm?

Ties Blom

 

[ddiamond]

My guess is that mmm is the julian date.

 

[ddiamond]

julian_day('2006-' || substr(dt_mmdd,1,2) || '-' || substr(dt_mmdd,4,2))

 

[ddiamond]

Actually, I think I confused day_of_year with Julian_day.
This is what you actually want:
'2006' || day_of_year('2006-' || substr(dt_mmdd,1,2) || '-' || substr(dt_mmdd,4,2))

 

[ancheswaroop]

Here i have to change the format
mm dd to yyyymmm

if date is Feb 3 2006
mm dd show 02 03
yyyymmm show as 2006034

(yyyymmm)2006034 is 2006Jan 31 days + Feb 3 days(31+3=34)

mm dd(02 01) to yyyymmm (2006032)

 

[MarcLodge]

Ancheswarooop,
Try:

'2006'||SUBSTR(DIGITS(DAYOFYEAR('2006-'||COL_MM||'-'||COL_DD)),8)

This will ensure that the number of days is always 3 characters eg 1st of Jan gets displayed as 2006001 rather than 20061

Marc

 

[ddiamond]

Marc,

I've always had a bit of trouble with the digits function. What determines how many zeros it will pad? How did you choose 8 for the starting position of the substr function?

- Dan

 

[ddiamond]

'2006' || substr(char(dayofyear('2006-' || substr(dt_mmdd,1,2) || '-' || substr(dt_mmdd,4,2))+1000),2)

 

[ddiamond]

I just tested Marc's solution, and it appears to work. My solution should also work. So you have 2 options. I don't know which method is faster, or if it makes any difference.

 

[MarcLodge]

Dan,

I've also struggled with the digits function but know that when using functions such like dayofyear, the output is more predictable. The digitising of this function will always put the first character, be it 0 or otherwise, in position 8.

Marc

 

[ancheswaroop]

'2006'||SUBSTR(DIGITS(DAYOFYEAR('2006-'||TRAN_DATE_TRANSACTION,1,2||'-'||TRAN_DATE_TRANSACTION,1,2)),8)

this one working fine but i have some days records with year 2005

 

[ddiamond]

How can you determine the year from our data? Is there another field that contains it?

 

[ancheswaroop]

TABLE 1

TRANSACTION_DATE
-------------------
11052005
12112005
01222006
02192006
02125006 (MMDDYYYY)

TABLE 2

AT31_TRANSACTION_DATE
------------------------
2005270
2005340
2006022
2006050
2006043 (YYYYDDD)

I HAVE TO MATCH THIS TO TABLES WITH THIS DATES

BUT FORMAT WAS DIFFERENT
AND SOME DATES ARE 2005 AND 2006

 

[MarcLodge]

Ancheswaroop,
I'm going to be a little harsh here. The information you require is in this thread, with great examples from Dan and an idea from myself. Please read back through the replies and look at what has been given to you by the way of examples, and use them.

If you have a table that holds a date that is not in a date format, then substr is the answer. The answer that you require is here, with examples. Yes, Dan or I or many others could give you the answer, but the whole idea behind tek tips is that you ask questions and learn from the replies.

Please have a go with some SQL, and if you are still struggling, get back to us with the SQL you have written, and we will attempt to lead you to an answer.

Sorry to be so negative, but I really do think you have enough information in this thread to give it a go.

Marc

 

[ddiamond]

Just to summarize, anytime you need to use a date function with a text field, you must format the text field as yyyy-mm-dd. In most cases, this reformating can be done with substr and concat operator. The functions you've needed thus far are dayofyear and digits. If your requirements have changed, we may be able to suggest additional functions.

I am not completely sure what your new requirements are. Your original problem was to reformat 'mm dd'. Now you are saying you need the year. Clearly you cannot derive the year from 'mm dd'. Now the final 2 tables you mentioned do contain the year, so you should be able to reformat us substr and digits. My recomendation is to put both dates in the format yyyyddd before matching.