Strange month behavior when formatting "mm"

[Scaniafan]

Good morning all,

I'm experiencing strange behavior of my month part of a date field. When I format it either

format(month(date()),"mm")

or

format(datepart("m",date()),"mm")

it both returns a month one before the actual month.

January > 12
February > 01

If I don't format, it returns the correct month. However, I need the two digit month

What can be causing this behavior? :banghead:

 

[stopher]

Try:

Format(Date(), "mm")

 

[Scaniafan]

That did the trick. Perfect, thank you. Didn't know I didn't need to specify that I wanted to see the month.

 

[Galaxiom]

Good morning all,

I'm experiencing strange behavior of my month part of a date field. When I format it either

format(month(date()),"mm")

or

format(datepart("m",date()),"mm")

it both returns a month one before the actual month.

January > 12
February > 01

If I don't format, it returns the correct month. However, I need the two digit month

What can be causing this behavior? :banghead:

Not strange at all. DatePart and Month are both returning the month number for the given date.

Then you are applying Format to that result. Lets say your date was in January. A date with the value of 1 is 31/12/1899. Hence the result of the Format is 12.

 

[Scaniafan]

Ok, clear explanation of what is going "wrong"

 

[CJ_London]

to follow on from stopher's comments

Format(Date(), "mm") returns a string - "12" for December, "01" for January

Month(Date()) returns a number - 12 for December, 1 for January

Format(Month(Date()),"00") returns a number as a string - "01" for January