Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Is it possible to create a field based on a date? I wanted to create a field that shows deciphers whether data is pre-2019 or post-2019.
The date field I'm using has the format MMM DD, YYYY hh:mm:ss so would need to be split at Jan 01, 2019, 00:00:00 if possible?
Not 100% clear what you need.
but try something like below in your load script
if(Year(DATEFIELD)=2019
, 2019
, if(Year(DATEFIELD)<2019,'Pre 2019','Post 2019')
) as 2019_Flag
Not 100% clear what you need.
but try something like below in your load script
if(Year(DATEFIELD)=2019
, 2019
, if(Year(DATEFIELD)<2019,'Pre 2019','Post 2019')
) as 2019_Flag
You can use the script function Date# to convert the text in date.
Example
Date(Floor(Num(Date#(Datefield, 'MMM DD, YYYY hh:mm:ss'))) )
The Num and floor function will remove the decimal portion, and the Date function will convert to a date field with the format as specified in the set.
Check the help link.
I had to force the date to a normal format 'DD/MM/YYYY' and then this worked! Thanks
Is there a way of future proofing this bit of scripting, so that anything that is 2019 onwards (including any future years too) rather than just hard coding to 2019 specifically?
No unfortunately they want it for everything from 2019 onwards, so there may still be 2019 data when we are in 2020 therefore I can't do the current year
So I can't force it to look for the year that is 2019 or higher in the standard script?