Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

MS Access load datetime field as YYYYMM

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!

1 Solution

Accepted Solutions
jpapador
Valued Contributor

Re: MS Access load datetime field as YYYYMM

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],

5 Replies

Re: MS Access load datetime field as YYYYMM

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.

Not applicable

Re: MS Access load datetime field as YYYYMM

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

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],


jpapador
Valued Contributor

Re: MS Access load datetime field as YYYYMM

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],

Not applicable

Re: MS Access load datetime field as YYYYMM

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!

Community Browser