topic Re: MS Access load datetime field as YYYYMM in QlikView

MS Access load datetime field as YYYYMM

Tue, 22 Dec 2015 21:15:55 GMT

Hi Experts,

This is probably a silly question, but what is the syntax to load an MS Access DateTime as YYYYMM? I tried this, but it's not working

Date(Date#(VisitDate, 'YYYYMMDD'), 'YYYYMM') as 'VisitYear Month',

Thanks in advance!

Re: MS Access load datetime field as YYYYMM

sunny_talwar — Tue, 22 Dec 2015 21:24:10 GMT

What format does VisitDate come in? If it does come in as YYYYMMDD, then what you are using looks right except for the single quote around the field name. I would either use double quotes or square brackets

Date(Date#(VisitDate, 'YYYYMMDD'), 'YYYYMM') as [VisitYear Month],

Update: I guess it doesn't matter, single quotes seems to work as well.

Re: MS Access load datetime field as YYYYMM

Tue, 22 Dec 2015 21:29:12 GMT

Thanks for the reply! OK, I changed the line to :

Date(Date#(VisitDate, 'MM/DD/YYYY'), 'YYYYMM') as ['Visit Year Month]',

The format in Access is MM/DD/YYYY. I'm still not getting any results. Is there something else that could be the problem?

Re: MS Access load datetime field as YYYYMM

sunny_talwar — Tue, 22 Dec 2015 21:33:56 GMT

I think single quotes seems to work, but have square bracket or single quotes (not both). Try this:

Date(Date#(VisitDate, 'MM/DD/YYYY'), 'YYYYMM') as [Visit Year Month],

Re: MS Access load datetime field as YYYYMM

jpapador — Tue, 22 Dec 2015 21:39:57 GMT

If it is already coming in as a data type "Date" you won't need the Date#.

Try:

Date(MonthStart(VisitDate), 'YYYYMM') as [Visit Year Month],

Re: MS Access load datetime field as YYYYMM

Tue, 22 Dec 2015 21:46:10 GMT

This worked! I think the fact that the field is already DateTime must somehow play into the syntax choice. (I'm a total newbie, so take that for what it's worth!)

Thank you both very very much!