Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

DateTime not recognized as Date & Time from SQL Server - Qlik Sense 2.0

I'm pulling data from SQL Server (2008 R2) via OLE DB into Qlik Sense 2.0 (Demo).

The DateTime fields in SQL are not showing under Date & Time in Qlik Sense Desktop.

I've tried converting them to dates as well with no success.

I don't see any way to right-click on Date & Time to add fields either. Is this a limitation of the Free Download Demo?

TIA,

Dave

Tags (2)
5 Replies
pamaxeed
Contributor III

Re: DateTime not recognized as Date & Time from SQL Server - Qlik Sense 2.0

Can you show me how you tried to convert and format the date time in Qliksense?

Cheers,

Patric

Not applicable

Re: DateTime not recognized as Date & Time from SQL Server - Qlik Sense 2.0

I converted it to a Date in SQL Server:

In my SQL:

CONVERT(Date, DateFieldNameHere) as ThisDate

In the LOAD statement, am referencing ThisDate

It pulls the data into Qlik Sense correctly, just doesn't recognize it in the Date & Time bucket.

I am also able to correctly run Qlik functions like Year(ThisDate) against the data.

Thanks,
Dave

Employee
Employee

Re: DateTime not recognized as Date & Time from SQL Server - Qlik Sense 2.0

they won't show up by default under Date & Time fields. You would need to add some script in the data load editor to ensure that happens. The subject area is called 'derived fields'

http://help.qlik.com/sense/2.1/en-US/online/#../Subsystems/Hub/Content/Scripting/derived-fields.htm

There are no limitations to NOT using derived fields apart from the nice organization on the left side. There are a few more limitations to date & time fields (derived fields) that can make it work just keeping the field as is .  For example, derived fields have not been yet supported in set analysis .

does this help ?

Not applicable

Re: DateTime not recognized as Date & Time from SQL Server - Qlik Sense 2.0

Jonathan,

That's what I was looking for.

I'm surprised Qlik hasn't made Date/Time derived fields a standard functionality.

cristian_ionesc
New Contributor

Re: DateTime not recognized as Date & Time from SQL Server - Qlik Sense 2.0

I have the exact same issues. I did not work with the Data Load Editor as I am a beginner but I am using the Data Manager.

I have 3 tables residing in the same sql db. All 3 have a column called [Week] which is under the SQL datetime format.

When I'm loading the tables, I see very chaotic and random behavior: in 2 of my 3 tables the date shows up in the correct date format, but in the 3rd one (the largest one) I get either serial date (5 digits excel format) or YYYY-MM-DD HH:MMSmiley FrustratedS but not recognized as qliksense date format, but as general.

To make myself more clear - If I upload the table one time the date shows up as serial date. After I delete the table and reload it, it shows up as General and so on.

I know you are going to tell me I need to control this in the Load Script Editor. I don't want to since I don't understand why identical columns in SQL are uploaded differently...

Community Browser