Script to change dates

aveenm293

Forums

Hi Brian
I have another request. I usually work with data that contains numerous dates which are all in character format. I generally use the @cTod and this always works. Recently I have received with data with the date formats as follows:
1 February 2013
2003 February 1
1 Mar 2013
Mar 1, 2013 
Is is possible to write a script that will change any data like above to the format YYYYMMDD.
Thank you
Regards
Aveen
 
 

avikerem Sun, 05/17/2015 - 13:20

Thanks so much Brian. As usual your support is extremely helpful. I was trying to use DateSerial before writing to you but got wrong results, I will try your script and analyze what went wrong with my implementation. 
I will let you know the results of my tests.
Cheers, Avi

Brian Element Sun, 05/17/2015 - 15:15

Well I used the DateSerial here so hopefully it will work out for you.  Let me know how it goes. 

avikerem Mon, 05/18/2015 - 21:59

Hi Brian
I was called to help in a priority issue. It will take me few days to return to this script. I will let you know once I am back.
Thanks again for your help.

avikerem Thu, 06/18/2015 - 19:59

Hi Brian
I tried your code today and it worked fine. I used it to select data across (before-after) balance sheet dates and it was very useful.
Thanks a lot and have a great weekend
Avi

mkamagate Wed, 04/10/2019 - 15:26

Greetings,
I am currently conducting a test to identify weekend transactions. My statement goes from 12/22/2018 to 01/23/2019. Is there a way to assign th right year in my equation? .My transaction date format is 12/22 ( MM/DD) on the statement. I added the year with the below equation but unfortunately it added 2019 to the decenber transactions.
field.Equation = "@Ctod(@Left( TRANS_DATE ,2) + @Right( TRANS_DATE ,2) + ""2019"", ""MMDDYYYY"")"
Regards,
Mohamed

ravisdxb Thu, 04/11/2019 - 01:45

For the data spanning just 2 months as mentioned by you, use @If to add "2018" in case left most 2 characters equal "12", else, add "2019". 
 
field.Equation = "@Ctod(@Left(TRANS_DATE,2 )+@Right(TRANS_DATE,2)+@if(@Left(TRANS_DATE,2)==""12"",""2018"",""2019""),""MMDDYYYY"")"