topic Separate Date from Time in QlikView

Separate Date from Time

Mon, 25 Jul 2011 08:44:41 GMT

Dear All

I am a new user and I need your help for a rather easy ( for you ) problem.

I want to separate date from time.

In the past I was doing something like this :

Date (DateTime,'dd/mm/yyyy') As ShortDate

But now I can't find the date function. I am using the Version 9.00.7469.8 SR4.

Thank you very much

Kostas Stathis ( k.stathis@live.com )

Separate Date from Time

kaushiknsolanki — Mon, 25 Jul 2011 08:52:49 GMT

Hi,

Date function is there in version 9.

Have a look at the help file.

And its the only function to get the date out of a time stamp.

Regards,

Kaushik Solanki

Separate Date from Time

Mon, 25 Jul 2011 08:54:25 GMT

Hi Kostas,

Please try this....

Date#(Date (DateTime,'dd/mm/yyyy') )As ShortDate

Regards,

Antony.

Separate Date from Time

Mon, 25 Jul 2011 09:22:27 GMT

Dear Kaushik

Below you can see the error message. The date function exists in the help file

date( expression [ , format-code ] ).

I am recieving data from an access 2007 database. I will try to use the same from SQL 2008 Sample DB.

Thank you for your instant response.

SQL Error:[Microsoft][ODBC Microsoft Access Driver] Wrong number of arguments used with function in query expression 'Date(App_Date,'dd/mm/yyyy''.

SQL Scriptline:

SQL State:37000

SQL SELECT

App_Date,

Date(App_Date,'dd/mm/yyyy') As ShortDate,

App_ID,

App_Notes,

App_Time,

ContactName,

EstCost,

FinCost,

Job,

Status

FROM Appointment

Separate Date from Time

kaushiknsolanki — Mon, 25 Jul 2011 09:27:43 GMT

Hi,

Yes, the reason is sql date function takes only 1 parameter, where as qlikview date takes 2 parameter.

Try below code.

load *, Date(App_Date,'dd/mm/yyyy') As ShortDate;

SQL SELECT

App_Date,

App_ID,

App_Notes,

App_Time,

ContactName,

EstCost,

FinCost,

Job,

Status

FROM Appointment

Regards,

Kaushik Solanki

Separate Date from Time

Mon, 25 Jul 2011 09:28:20 GMT

Dear Antony

I have done this yesterday. The result is very strange. I am loosing the month.

I am expecting 11/07/2011 and the result is 11/00/2011 !!!

The Day and the Year are always correct but the month is always 00.

Regards Kostas

Separate Date from Time

Mon, 25 Jul 2011 09:37:28 GMT

Hi,

Please send your application......

Separate Date from Time

Mon, 25 Jul 2011 09:50:24 GMT

I have just tried it. There is no error message BUT the result was 11/00/2011 the month is 00.

This the new script. I will spend some more time trying to find out what goes wrong. Unfortunately I have all the time in the world.

LOAD

App_Date,

Date(App_Date,'dd/mm/yyyy') As ShortDate,

App_ID,

App_Notes,

App_Time,

ContactName,

EstCost,

FinCost,

Job,

Status;

SQL SELECT App_Date,App_ID,App_Notes,App_Time,ContactName,EstCost,FinCost,Job,Status

From Appointment;

Thank you very much

Kostas

Separate Date from Time

Mon, 25 Jul 2011 09:55:15 GMT

Antony you can find posted ( as an answer to Kaushik ) the script.

Thanks

Kostas

Separate Date from Time

kaushiknsolanki — Mon, 25 Jul 2011 09:56:55 GMT

HI,

Make sure that the data is in proper way in your database.

Regards,

Kaushik Solanki

Separate Date from Time

Mon, 25 Jul 2011 10:36:25 GMT

Hi Kostas,

You can try the below. If you can send some examples of App_Date field from your SQL database, we can guide you better.

makedate(year(App_Date), month(App_Date), day(App_Date)) as ShortDate

Regards,

Sajeevan

Separate Date from Time

Wed, 27 Jul 2011 13:15:42 GMT

Dear Sajeevan

Your suggestion works perfectly. The DB is an Access it is not SQL. With SQL I have no problem and I use the Date function. I am not sure yet what is going wrong. I am looking the regional settings of the PC ,there was a difference with the date format in QV ( but the problem is not solved using the date function ). I didn't send you a sample because it is in Greek.

Thank you very much

Kostas

Separate Date from Time

Wed, 27 Jul 2011 13:37:02 GMT

Hi Kostas,

Thanks. I have couple of Greek colleagues and one of the colleague's name is also Kostas hence it is not a problem even if the data is in Greek.

Best Regards,

Sajeevan