Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 5 different tables that have a timestamp field call SysDate. What i want to do is combined them together but have run into an issue:
1. Four of the tables the timestamp field looks like 04/18/2018 10:30:32 AM
2. I have one the timestamp field looks like 04/18/2018 10:32
So it is missing the seconds and either the AM or PM. My question is can I use the makedate function to solve my issue. I would need to add either 00 to the seconds and then AM or PM. The other option I was looking at is trimming off the seconds and AM/PM from the four table that include it. I dont really want to do that. Any suggestions would be great.
Thanks,
David
Well, its not an elegant solution , but please see below.
Timestamp(num(Date(MakeDate(Mid([YourDateTimeField],7,4),Left([YourDateTimeField],2),Mid([YourDateTimeField],4,2))))+Time(SubField([YourDateTimeField],' ',2),'hh:mm:ss TT'),'MM/DD/YYYY hh:mm:ss TT')
Change formats where needed.
Hope this helps.
you can use the alt function (*)
Timestamp(Alt(
Timestamp#(YourDateTimeField, 'MM/DD/YYYY hh:mm:ss tt'),
Timestamp#(YourDateTimeField, 'MM/DD/YYYY hh:mm')
), 'MM/DD/YYYY hh:mm:ss')
(*)
page 8
The above will also work, just a small change is needed in terms of the formatting to include the AM and PM.
Timestamp(Alt(
Timestamp#(DateTime, 'MM/DD/YYYY hh:mm:ss tt'),
Timestamp#(DateTime, 'MM/DD/YYYY hh:mm')
), 'MM/DD/YYYY hh:mm:ss TT')