Create Date Field Using Numeric Year and Alpha Month Name

[Anonymous]

Inputs

Month: Apr

Year: 2015

Desired output: 201504

Current syntax:

Num(Year & num(month(date#(Month,'MMM'))), '000000') as [Fiscal Period]

Current output: 020154

I'm very close but I cant seem to figure out where I am going wrong.

Any help is appreciated.

- dave

[sunny_talwar · Accepted solution]

Try this:

Date(MakeDate(Year, Month(Date#(Month, 'MMM')), 1), 'YYYYMM') as [Fiscal Period]

or

Date(Date#(Year & '-' & Month, 'YYYY-MMM'), 'YYYYMM') as [Fiscal Period]

[swuehl]

Try

Year & num(month(date#(Month,'MMM')), '00') as [Fiscal Period]

or

Date( Date#( Year & Month,'YYYYMMM'), 'YYYYMM') as [Fiscal Period]

note that the underlying numeric value is differenct in the second approach (it's the monthstart date value)

[Anonymous]

Amazing. Thank you per usual.

[sunny_talwar]

Not a problem

[maxgro]

try

Date(Date#(Year & Month, 'YYYYMMM'), 'YYYYMM')      as .......