topic Re: Converting DateTime to Date in QlikView

Converting DateTime to Date

oseimuduame — Wed, 25 Nov 2020 16:16:04 GMT

Good Morning all,

i have a Bottle-Neck that i am struggling with, i have a CreatedDateTime which i want to convert to just Date.

i used TO_CHAR(o.CreatedDateTime, DD-MM-YYYY) which is not working.

please i need help on this.

Thank You.

Re: Converting DateTime to Date

kaushiknsolanki — Tue, 30 Aug 2016 08:42:53 GMT

Hi,

Try this.

Load *,Date(Floor(o.CreatedDateTime)) as NewDate;

Sql Select * from xyz;

Regards,

Kaushik Solanki

Re: Converting DateTime to Date

oseimuduame — Tue, 30 Aug 2016 09:20:38 GMT

Tried it, still no luck

Telling me

Field not found - <o.CreatedDateTime>

here is what i want to INSERT it;

//Item Report

SELECT

i.GroupCode as "ITEM_GROUP",

o.MenuItemName as "MENU_ITEM_NAME",

// To_CHAR(o.CreatedDateTime, 'DD-MM-YYYY') as "ORDER_DATE",

//CONVERT(date[20], o.CreatedDateTime, dd/mm/yyyy) as "ORDER_DATE",

o.Price as "MENU_ITEM_PRICE",

o.Quantity as "MENU_ITEM_QTY"

FROM DashAlata.dbo.MenuItems i, DashAlata.dbo.Orders o

WHERE i.Id = o.MenuItemId;

Re: Converting DateTime to Date

jpenuliar — Tue, 30 Aug 2016 09:39:47 GMT

Field Not Found:

Can you confirm that the Field [CreatedDateTime] exists in table Orders?

Re: Converting DateTime to Date

oseimuduame — Tue, 30 Aug 2016 09:56:29 GMT

CreatedDateTime is in One Table[Order Table] in my Database

Re: Converting DateTime to Date

jpenuliar — Tue, 30 Aug 2016 10:12:29 GMT

is this the same DashAlata.dbo.Orders ?

Re: Converting DateTime to Date

oseimuduame — Tue, 30 Aug 2016 10:21:01 GMT

Yes. The same Table [My Order Table]

Re: Converting DateTime to Date

jpenuliar — Tue, 30 Aug 2016 10:42:55 GMT

Why not perform the Date Conversion at the Preceeding Load (after your SQL script).

going back to your script issue, just to finally confirm field does exists, try querying just the fileds in Orders table:

SQL

SELECT

o.MenuItemName as "MENU_ITEM_NAME",

o.CreatedDateTime as "ORDER_DATE",

o.Price as "MENU_ITEM_PRICE",

o.Quantity as "MENU_ITEM_QTY"

FROM DashAlata.dbo.Orders o

;

after confirming the query runs ok, add your TO_CHAR() or maybe some other functions applicable, TO_DATE() , etc..

Re: Converting DateTime to Date

Anil_Babu_Samineni — Tue, 30 Aug 2016 10:44:15 GMT

Please share the Data like two values. If you share the App that is very use ful

o.CreatedDateTime

Re: Converting DateTime to Date

ToniKautto — Tue, 30 Aug 2016 11:19:38 GMT

It sounds like you are asking for advice on how to compose the SQL query. Perhaps the database vendor has online help to guide you on the suitable function? Or are you having an issue with the data in QlikView?

Re: Converting DateTime to Date

oseimuduame — Tue, 30 Aug 2016 12:28:52 GMT

@Toni Kautto its is not advice on how to compose my SQL Query. i have a functioning Date that i Format from Oracle Database Using TO_CHAR. But i dont know why all what i have tried is not working for this one.

i have tried CAST, I have also Tried CONVERT

Re: Converting DateTime to Date

ToniKautto — Tue, 30 Aug 2016 12:56:31 GMT

You are still referring to SQL functions. There is no CAST or CONVERT in Qlik engine. For this reason you are asking for help to compose a SQL query, and are not asking about help with Qlik functionality.

Anything that you define after the SQL keyword will be sent and processed by the connector or data source. The Qlik engine will not process this string.

If you filed is properly formatted in the database, why do you not just load it as it is and format it in QlikView? If you load the data without converting it in SQL statement, what value do you get?

Re: Converting DateTime to Date

oseimuduame — Tue, 30 Aug 2016 13:01:27 GMT

Ohh i get your point now @Toni The Value i am getting is 2016-08-22 10:54:23 365 which is not what i want.

i want to Format it in Qlikview to be like 2016-08-22

So that i can work with it in QLIKVIEW

Re: Converting DateTime to Date

rupamjyotidas — Tue, 30 Aug 2016 13:08:33 GMT

What the above suggestions are saying is that you run the script like this

Load

Date(Floor(ORDER_DATE),'YYYY-MM-DD') as OrderDate;

SELECT

i.GroupCode as "ITEM_GROUP",

o.MenuItemName as "MENU_ITEM_NAME",

o.CreatedDateTime as "ORDER_DATE",

o.Price as "MENU_ITEM_PRICE",

o.Quantity as "MENU_ITEM_QTY"

FROM DashAlata.dbo.MenuItems i, DashAlata.dbo.Orders o

WHERE i.Id = o.MenuItemId;

For more info please ready about preceding load.

Re: Converting DateTime to Date

ToniKautto — Tue, 30 Aug 2016 13:09:20 GMT

Try loading it as the unwanted text, and format the value in the LOAD statement.Perhaps try something in line with...

LOAD

Date(Date#(ORDER_DATE, 'YYYY-MM-DD hh:mm:ss fff')) AS ORDER_DATE

;

SQL SELECT

o.CreatedDateTime as "ORDER_DATE"

FROM DashAlata.dbo.Orders o;

Re: Converting DateTime to Date

oseimuduame — Tue, 30 Aug 2016 13:54:49 GMT

Hello Guys, thank you for your Support. My dashboard working perfectly i just need to Join my Table Correctly

i always say this, You guys are the Best and this Community is the Best i have ever joined. Keep it up guys

Working Perfectly