Date changes to number?

[#1]

Hi All,

Basic query I suppose... I have my event_date_time_dimension_id field which actually has the data like 20101121, however below is the script I am using to change it to a format, but after I reload it changes to 21 11 56934... Any suggestions pls..?

date("event_date_time_dimension_id",'DD MMM YYYY') as event_date_time_dimension_id,

Red..

[marcel_olmo]

how about this :

text( date("event_date_time_dimension_id",'DD MMM YYYY') ) as event_date_time_dimension_id,

[#3]

try this

date(date#(event_date_time_dimension_id),'yyyyMMdd'),'dd-MMM-yyyy')

[#4]

or for the SQL statement

to_char(to_date(event_date_time_dimension_id),'yyyyMMdd'),'dd-MMM-yyyy')

[#5 · Accepted solution]

Hi All,

Thank you for taking the time to reply to my query, however I figured out a solution other than what is mentioned and it could be useful for anyone of you in future..

date(makedate(left("event_date_time_dimension_id",4),mid("event_date_time_dimension_id",5,2), mid("event_date_time_dimension_id",7,2)),'DD MMM YYYY') as "event_datetime_dimension_id"

That fixed my problem... but not sure if its gonna help for you guys.

Thanks Again,

Red

[johnw]

redguy4u wrote:date(makedate(left("event_date_time_dimension_id",4),mid("event_date_time_dimension_id",5,2), mid("event_date_time_dimension_id",7,2)),'DD MMM YYYY') as "event_datetime_dimension_id"

I would strongly suggest Jochem's solution instead - all I see wrong with it is a typo (an extra close parenthesis). Perhaps that threw you. I believe the below corrects the typo and matches the output format you want:

date(date#(event_date_time_dimension_id,'YYYYMMDD'),'DD MMM YYYY') as event_datetime_dimension_id

I'd call date(date#(...)) the standard solution for this class of problems. Doing a bunch of text manipulation to break the input apart, then assemble it back together with makedate() is significantly more trouble to go through. It also makes it harder to see what the input format actually is, where date#() makes this very clear. Here's the input format, here's the output format.

As for what was wrong with your first solution, the date() function takes a number as input and converts it into a date in the specified format. The number is the number of days since December 30, 1899. So you told it you wanted a date that was 20,101,121 days since December 30, 1899, which apparently is... well, it's December 30, 56934. Not sure where you're getting November 21, 56934. Did you write that wrong, or is that really what you're getting for date(20101121)?

The date#() function, though, takes a string as input, interprets it according to the format you give it, and converts it to the number of days since December 30, 1899. So it takes '20101121' and interprets it as 'YYYYMMDD', and returns the number of days since December 30, 1899, which is 40503. OK, it realizes it's a date, but it's still in YYYYMMDD format at that point. But combine it with the date() function, and you can set the output format as desired.

[#7]

Hello John,

Thank you for the explaination, but that got fixed when I tried the way at that time...and as you said this looks more polished and clean compared to what I had... Anyways will try this in future..

Thanks agian for taking time to post this in detail.

Red