topic Re: Function MID doesn't work on a Date field (DD/MM/YYYY) which is read as a serial number in scrip in App Development

Function MID doesn't work on a Date field (DD/MM/YYYY) which is read as a serial number in script

thanhng34 — Sat, 16 Nov 2024 18:39:48 GMT

Hi community,

I need your help to solve a problem on my Date field called “personnePhysiqueNaissance”, which is in the format of DD/MM/YYYY.

I need to extract the month with Mid(4,2) function, in order to have 01, 02, 03, 04….

I’d like to use Mid function because the Month function returns January, February,… and the Num(month) returns 1, 2, 3, 4…

But the mid(4,2) doesn’t work as a flag created in script. For example, it returns 53 for 28/01/2000, 31 for 06/01/2000, … (field N°8 in the table below) . The personnePhysiqueNaissance field is the N°1 column in the table below.

I think it has been read as a serial number date in script (I didn’t understand why and what does these words mean), while it has been read as a normal date in design. Because the Mid and Right function does work well on design (field N°7 in the table below).

Mid Function in script

What can I do to use the mid function on this date field in script? Because I need to use it as a flag in script for one of my KPIs.

Thanks in advance for your help.

Re: Function MID doesn't work on a Date field (DD/MM/YYYY) which is read as a serial number in scrip

tresB — Tue, 12 May 2020 08:43:21 GMT

Instead of using the string function mid(), try using date function month(), like:

Month(personnePhysiqueNaissance) as Month

Re: Function MID doesn't work on a Date field (DD/MM/YYYY) which is read as a serial number in scrip

Vegar — Tue, 12 May 2020 11:20:46 GMT

I with @tresB on this one, try using month(). If the two digits are important just format the numeric as two digits like this:

NUM( Month(personnePhysiqueNaissance) ,'00') AS Month

Re: Function MID doesn't work on a Date field (DD/MM/YYYY) which is read as a serial number in scrip

thanhng34 — Tue, 12 May 2020 19:49:56 GMT

Thank you ! Your function NUM( Month(personnePhysiqueNaissance) ,'00') AS Month resolved my problem, but I still didn't understand why a date field in DD/MM/YYYY format is read as a serial number on script.

Best regards,