Dear All,
I was wondering if some of the readers of this question may help me to solve this :
I would like to make some report on Active Directory extraction from CSVDE query.
Some fields corresponds to date and time and are represented either like 20040325075722.0Z (hereafter field 1) or 131242751793786660 (which reprensents the elpased time since 01/01/1601, hereafter field2 )
I have added a function for field 1
@Right(@Left(WHENCREATED;8);2)+"/"+@Right(@Left(WHENCREATED;6);2)+"/"+@Left(WHENCREATED;4)
to obtain 25/03/2004 and I would like to make additional function to extract all data that are related to date between DD/MM/YYYY and DD/MM/YYYY. Does any of you have an idea how to solve that ?
For field 2, it is easier as I can consider the figures as number by performing the following function (Lastlogon/10000000/3600/24)-109205 and then applying a filter based on numbers.
Many thanks in advance for your appreciated help!
Cheers,
Nikolai
NTR
Offline
Last seen: 4 years 7 months ago
Joined: 11/26/2017 - 14:43
Hi NTR and welcome to the site.
For your first problem I would probably make a date field as it seems that you are making a character field instead. Create a virtual date field and then use the following:
@CtoD(@Left(WHENCREATED;8);"YYYYMMDD")
This will return a data field. You can then use the fuction @BetweenDate(Date; LowerLimit; UpperLimit) to do the extract that you want.
Hopefully I understood what you wanted to do and this helps.
Brian
Hi Brian,
Many thanks for you answer, it is definitely helpful.
May I also ask you if you can help with this :
I do have a numeric field (i.e. 131242751793786660 (which reprensents the elpased time since 01/01/1601) that I have converted into value (date and time) through this formula(Lastlogon/10000000/3600/24)-109205. I was wondering if it is possible to obtain the answer in a format similar to YYYYMMDD HHMMSS to have both date and time. If not I would be happy with only a date format but I have not found a formula similar to the XLS rounddown. If I use the @round() formula it will convert to the right day if lastlogon occured in a.m. but to the next day if occured in p.m.
Many thanks again for your appreciated help!
Nikolai
Hi Brian,
I have finally - partially - solved my issue with the function @DaysToD but I am still looking to have both time and date within the same field. If you do have an answer I would be happy.
Have a nice day,
Nikolai
Hi Nikolai,
Within IDEA if you want to have both the date and time in the same field then it would have to be in a character field. IDEA has date fields and time fields but it does not have a combined date and time field. So are you looking for this to be in a character field?
Thanks
Brian
Hi Brian,
I would like to know how can I get the new character field combining the Date and Time fields.
Thanks,
Goncalo
Hi Goncalo,
To create your field you will need to turn both your date and time fields to character fields then concatenate them together. You have many formatting options available to you for how the date will look and how many spaces or what character separates the date and time. You need to append a virtual character field long enough to fit your formatting option.
Examples using December 01, 2015 and 11:15AM
@dtoc(date,"dd/mm/yyyy") + " " + @ttoc(time)
will return: 01/12/2015 11:15:00
@dtoc(date,"ddmmyyyy") + "-" + @ttoc(time)
will return: 01122015-11:15:00
@dtoc(date,"yyyy-mm-dd") + " ~ " + @ttoc(time)
will return: 2015-12-01 ~ 11:15:00
Regards,
Steve