Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have a column called "OFF_SWITCH_DAT" with date that looks like this: DD-MM-YYYY HH:MM:SS (like this: 4-1-2016 17:48:50). What I would like to do now is transform this value into a weeknumber.
I have tried this:
WeekName((OFF_SWITCH_DAT),'-') as WeekNum,
But this gives me the following output:
-
Also
WeekName(OFF_SWITCH_DAT) as WeekNum,
Does not work (giving me 2016/04)
Any thougts on how I can get a weeknumber.
Cheers,
Marc
use this Expression and define your datefield instead of hardcoded date
=weekname(timestamp#('4-1-2016 17:48:50','DD-MM-YYYY hh:mm:ss'))
Hi,
try something like this:
=Num(Week(YourDateField))
Or this:
=weekname(YourDateField)
Let me know if this helps.
Kind regards,
First make sure that the OFF_SWITCH_DAT is being correctly loaded as a numeric date value. The easiest way to check is to create a list box for OFF_SWITCH_DAT and seeing whether the dates align to the left (string) or right (numeric date), or a mix.
Then you can get a Week number with Week(OFF_SWITCH_DAT), or a week/year name with WeekName(OFF_SWITCH_DAT).
As suggested above the most likely reason is that the timestamp does not have the expected underlying numerical value.
If the incoming format is the same as your expected format in the app, I would suggest that you setup the app format variables to match the expected output. This should minimize your need for additional formatting.
From your example format string, I noticed that you use capital letters for the time. Try altering the format variables to lower case for the time portion. For consistency, make sure all three variables are aligned format wise.
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD-MM-YYYY ';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss';
Try This WeekName(Floor(NumOFF_SWITCH_DAT))).
Thanks