topic Re: Only load date and not time from date/time field in QlikView

Only load date and not time from date/time field

pauldamen — Fri, 28 Dec 2012 11:08:55 GMT

Hi,

I am trying to load a date from a database into Qlikview. The field I am taking the data from is a date/time field so the data looks like: 28-12-2012 12:07:31

When I am trying to make a master calender it keeps runing because of the time field and therefor making a endless amount of fields.

I have tried to load the data with a left function, date function, in a variable but none seems to be working.

Hope someone can help me with only loading the date and forgetting the whole time field.

Regards,

Paul

Re: Only load date and not time from date/time field

Fri, 28 Dec 2012 11:32:49 GMT

date(date#(date1-timestamp#('hh:mm:ss'),'dd/mm/yyyy))

hope it helps you

Re: Only load date and not time from date/time field

jagan — Fri, 28 Dec 2012 11:33:00 GMT

HI,

Try like this

=Date(Date#('28-12-2012 12:07:31', 'DD-MM-YYYY hh:mm:ss'), 'DD-MM-YYYY')

Hope this helps you.

Regards,

Jagan.

Re: Only load date and not time from date/time field

pauldamen — Fri, 28 Dec 2012 11:35:50 GMT

I tried the date function as well, the script part looks like this:

SQL

SELECT "cm_parent_webpageid", "cm_parent_webpageidname",

"cm_referringdomain", "cm_trafficsourcename", "cm_viewdate"

FROM "Finext_CRM".dbo."Filteredcm_webpageview";

When I put the cm_viedate (this is the date part) in this formula and reload I get a error saying: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'Date' is not a recognized built-in function name.

Why do I get this message?

Regards,

Paul

Re: Only load date and not time from date/time field

Gysbert_Wassenaar — Fri, 28 Dec 2012 12:18:24 GMT

If you must do it in the sql statement then try: DATEADD(dd, DATEDIFF(dd, 0, cm_viewdate), 0)

Otherwise I'd try to use floor(cm_viewdate) in the load statement.