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

[thanhng34]

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.

[tresesco]

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

Month(personnePhysiqueNaissance) as Month

[Vegar · Accepted solution]

I with @tresesco  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

[thanhng34]

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,