8 Replies Latest reply on Apr 26, 2018 10:33 AM by Shinichiro Murakami

# date conversion

Hey Everyone,

I'm trying to figure out how to convert 3 fields in my data set to a date (week begin date)

all my weeks end on Saturday

for example week 1 for Jan 2018 would be 12/13/17 - 1/6/18. All I want to see is "1/6/18"

my data looks like this (also attached)

 F Month F Week F Year Jan 1 2018 Jan 2 2018 Jan 3 2018 Jan 4 2018 Jan 1 2017 Jan 2 2017 Jan 3 2017 Jan 4 2017
• ###### 1. Re: date conversion

Hi Robin

This is kind of tricky, because Month and Week concept conflict each other and

- need to ignore month data

- then need cumulative calc to count number of weeks from start of the year.

First Saturday of year.

Count number of weeks from start of year.

End of week

Thanks,

Shin

• ###### 2. Re: date conversion

Actually, you don't need a table calc for this. The month and week numbers are sufficient, as long as you compute the date carefully:

To explain in a bit more detail:

DATEPARSE converts the month abbreviation and year to a date.

DATETRUNC finds the Saturday at the start of the week

DATEADD shifts the date by the right number of weeks and also adds 6 to get to the last day of the week as requested.

Dan

• ###### 3. Re: date conversion

Thank you, Dan

That's is correct.

For me, this helps me to understand the logic easier.

date(datetrunc('week',(dateparse("yyyyMMMdd",[F Year]+[F Month]+"01")),'saturday') +(int([F Week]))*7)

Thanks,

Shin

• ###### 4. Re: date conversion

I'm currently on 10.5 and looks like DATEPARSE doesn't work

if I drop Date in it's place it looks like the following works

date(datetrunc('week',(date([F Year]+[F Month]+"01")),'saturday') +(int([F Week]))*7)

any problems with using this?

• ###### 5. Re: date conversion

DATEPARSE is only available on some databases. It works for Excel as in your example, but not on whatever others you may use.

DATE should be fine, though.

Dan

• ###### 6. Re: date conversion

It does not work cause MMM is not int value.

Which connection are you using?

Shin

• ###### 7. Re: date conversion

I would think most databases can handle the MMM format but that doesn't mean all will. This is why we have DATEPARSE, but still not every database supports it.

Dan

• ###### 8. Re: date conversion

If connection does not support date conversion, only the way I can think of is

Thanks,

Shin