topic Re: Data Format SQL SERVER 2012 in QlikView

Data Format SQL SERVER 2012

Anonymous — Wed, 09 Jul 2014 16:45:25 GMT

Hi,

I have the following date format in my SQL SERVER = DD/MM/YYYY => Brazil Date

When I import this into Qlikview it comes across as a string like this = 20020627

When i change the format in "properties list" switch to "Data" DD/MM/YYYY The qlik view Show = 12/08/56714

Any ideas on how to change this a date in Qlikview?

Thanks

Marcos

Re: Data Format SQL SERVER 2012

ashfaq_haseeb — Wed, 09 Jul 2014 16:58:05 GMT

Load

F1,

F2,

Date(Date#(Your_Date,'YYYYMMDD','DD/MM/YYYY'),

F4;

sql select * from table;

Regards

ASHFAQ

Re: Data Format SQL SERVER 2012

Anonymous — Wed, 09 Jul 2014 17:14:21 GMT

Ashfaq,

I'm new in qlick view, could you show me how to make, below is my script.

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=EBRASIL_PROD;Data Source=192.168.0.4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ULTRA-MARCOS;Use Encryption for Data=False;Tag with column collation when possible=False];

SQL SELECT *

FROM "EBRASIL_PROD".dbo.STJ010;

The field i want to convert is "TJ_DTMPINI"

Thanks

Re: Data Format SQL SERVER 2012

hic — Wed, 09 Jul 2014 17:30:16 GMT

Just use

Load *,

Date(Date#(TJ_DTMPINI,'YYYYMMDD'),'DD/MM/YYYY') as NewDate;

SQL SELECT * FROM "EBRASIL_PROD".dbo.STJ010;

instead of your current SELECT statement.

The reason why you have this problem is that your date date isn't stored as a date type in the SQL SERVER. It is probably a VARCHAR or an INTEGER. Had it been a date type, it would have worked automatically.

HIC

Re: Data Format SQL SERVER 2012

Anonymous — Wed, 09 Jul 2014 17:35:40 GMT

Error in expression:

Date# takes 1-2 parameters

Load *, Date(Date#(TJ_DTMPINI,'YYYYMMDD','DD/MM/YYYY') as NewDate

Re: Data Format SQL SERVER 2012

hic — Wed, 09 Jul 2014 17:38:42 GMT

You're right. It is corrected now. I just added the missing bracket.

HIC

Re: Data Format SQL SERVER 2012

Anonymous — Wed, 09 Jul 2014 17:44:36 GMT

Sorry, you do not understand, this was the error message that QLIK returned, not what I wrote. How is the correct expression?

Thanks

Re: Data Format SQL SERVER 2012

hic — Wed, 09 Jul 2014 17:59:49 GMT

Load *,

Date(Date#(TJ_DTMPINI,'YYYYMMDD'),'DD/MM/YYYY') as NewDate;

SQL SELECT * FROM "EBRASIL_PROD".dbo.STJ010;

HIC

Re: Data Format SQL SERVER 2012

Anonymous — Wed, 09 Jul 2014 18:15:17 GMT

Allright, almost there heheh. I have more two 'date' fields, can't repeat the command. How i proceed.

Thanks.