Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
abhaytrip
Contributor

Loading Date from Oracle DB to QlikView

Hi ,

I've got an issue where I want to load date from MS Access to QlikView but there seems to be this error coming up.

Error : SQL##f - SqlState: S1000, ErrorCode: 1843, ErrorMsg: [Oracle][ODBC][Ora]ORA-01843: not a valid month


The date that i'm loading from MS-Access is this format .

date_1.PNG

The Query that I've Written on QlikView in order to get the data only for that particular day.

date_Sql.PNG

But the error i've been facing is not understandable to me as I'm new to QlikView.

Any sort of help is appreciated.

Thanks in advance.

Abhay.

1 Solution

Accepted Solutions
MVP
MVP

Re: Loading Date from Oracle DB to QlikView

if you have Oracle,

you can use an Oracle function to convert a string to a date

where oracledatefield >= to_date('20012018 05:00:00', 'DDMMYYYY HH24:MI:SS')


or if you only need a date

where oracledatefield >= to_date('20012018', 'DDMMYYYY')

Oracle to_date tips

View solution in original post

18 Replies
MVP & Luminary
MVP & Luminary

Re: Loading Date from MS Access to QlikView

Try it with: where "Date Field" = '1201/2017';

- Marcus

abhaytrip
Contributor

Re: Loading Date from MS Access to QlikView

There's still an error which says

Literal does not match format string.

MVP & Luminary
MVP & Luminary

Re: Loading Date from MS Access to QlikView

Within "Date Field" = '1201/2017' is a typo and it should be "Date Field" = '12/01/2017'. Further I'm not sure if access accept the single-quotes or if it must be another quoting maybe with double-quotes like: "Date Field" = "12/01/2017".

- Marcus

krishna20
Valued Contributor II

Re: Loading Date from MS Access to QlikView

Can you please share screenshot or code that you have used in edit script with an error message.

abhaytrip
Contributor

Re: Loading Date from MS Access to QlikView

ya , I figured the typo but it doesn't seem to be working out.

abhaytrip
Contributor

Re: Loading Date from MS Access to QlikView

This is what is coming after I tried Marcus's Method.

script Error.PNG

stabben23
Honored Contributor

Re: Loading Date from MS Access to QlikView

use another provider?

OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=Z:\Spec.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False];

stabben23
Honored Contributor

Re: Loading Date from MS Access to QlikView

Is it really MS Access, not ORACLE DB you try to Connect to?

where TO_DATE("Date Field") = '12/01/2017'; maybe help

abhaytrip
Contributor

Re: Loading Date from MS Access to QlikView

I wasn't sure until my Lead confirmed it to me.

Its from ORACLE To QLIKVIEW.

I tried the above but its not working out.

So what I did based on a small condition I pulled out some data from the ORACLE DB to have a look at the format of the "DATE_FIELD".

date Error.PNG

So this is the date field that I have in my ORACLE DB,

But i'm just not sure how do I pull it into QLIK.

The query which I tried but hasn't worked out.

script Error.PNG

Also , I'll correct the Discussion Topic.