Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
its_anandrjs

Date conversion

Hi All,

I have a Date Column and i want to convert in weekstart format like 'DD-MMM'  but it start from every sunday week.

Please provide help for that.

Anand

1 Solution

Accepted Solutions
erichshiino
Partner - Master
Partner - Master

HI,

If your column is called ORIGINAL_DATE, the syntax will be

...

date (   weekstart( ORIGINAL_DATE, 0, -1 ), 'DD-MMM') AS WeekStart

..

The date function will format it as DD-MMM. The weekstart function will get the start of the week, but the standard is monday. The third parameter of that functions will offset the date by -1 to send it to sunday.

You can get the full explanation on the function with the definition on the help file (copy below)

Hope this helps,

Erich

WeekStart( date [, shift = 0 [,weekoffset = 0]] )

Returns a value corresponding to a timestamp with the first millisecond of the first date ( Monday ) of the calendar week containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work with weeks not starting midnight between Sunday and Monday, you may indicate an offset in days in weekoffset. This may be given as a real number indicating days and/or fractions of a day.

Example:

weekstart ( '2006-01-12' ) returns '2006-01-09' with an underlying numeric value corresponding to '2006-01-09 00:00:00.000'

weekstart ( '2006-01-12', -1 ) returns '2006-01-02' with an underlying numeric value corresponding to '2006-01-02 00:00:00.000'

weekstart ( '2006-01-12', 0, 1 ) returns '2006-01-10' with an underlying numeric value corresponding to '2006-01-10 00:00:00.000'

View solution in original post

2 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

   At my place it starts with every Monday week, but anyways you can try this

   Weekstart(Date,0,1)

   This will give you one day after the weekstart date.

   Hope this will help you.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
erichshiino
Partner - Master
Partner - Master

HI,

If your column is called ORIGINAL_DATE, the syntax will be

...

date (   weekstart( ORIGINAL_DATE, 0, -1 ), 'DD-MMM') AS WeekStart

..

The date function will format it as DD-MMM. The weekstart function will get the start of the week, but the standard is monday. The third parameter of that functions will offset the date by -1 to send it to sunday.

You can get the full explanation on the function with the definition on the help file (copy below)

Hope this helps,

Erich

WeekStart( date [, shift = 0 [,weekoffset = 0]] )

Returns a value corresponding to a timestamp with the first millisecond of the first date ( Monday ) of the calendar week containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work with weeks not starting midnight between Sunday and Monday, you may indicate an offset in days in weekoffset. This may be given as a real number indicating days and/or fractions of a day.

Example:

weekstart ( '2006-01-12' ) returns '2006-01-09' with an underlying numeric value corresponding to '2006-01-09 00:00:00.000'

weekstart ( '2006-01-12', -1 ) returns '2006-01-02' with an underlying numeric value corresponding to '2006-01-02 00:00:00.000'

weekstart ( '2006-01-12', 0, 1 ) returns '2006-01-10' with an underlying numeric value corresponding to '2006-01-10 00:00:00.000'