topic Re: Converting a date to number in SQL in QlikView

Converting a date to number in SQL

robert99 — Mon, 07 Apr 2014 15:32:31 GMT

Is it possible to convert a date in SQL to a number

At present I'm doing this

INITIAL LOAD

ODBC CONNECT TO [SC5 LIVE];
SCCallTess:

SQL SELECT

Call_Num,

Call_InDate etc

FROM SC5LIVE.dbo.SCCall
where Call_Num >= 100000

THEN

load ...

Call_Num,

Call_InDate etc

Resident SCCallTess

where num(Call_InDate) >40179 ; //DATE 1/1/2010

drop table SCCallTess;

This works

But I would like to do the where with the SQL load

;

Is this possible. Num is not recognised in a SQL load

Re: Converting a date to number in SQL

Mon, 07 Apr 2014 15:38:01 GMT

Try this

SQL SELECT

Call_Num,

Call_InDate,

CONVERT(bigint,CONVERT(Call_InDate,'Format')) AS Call_InDateNum,

etc

FROM MERRYCHEFSC5LIVE.dbo.SCCall
where Call_Num >= 100000

Re: Converting a date to number in SQL

maxgro — Mon, 07 Apr 2014 15:53:57 GMT

this works for my sql server database

I think you can adapt to your table and field names

let vStartDate =date(40179, 'DD/MM/YYYY');

trace $(vStartDate);

Table3:

SQL

SELECT

[dat_data]

FROM [d003_date]

where dat_data > convert(datetime,'$(vStartDate)',103)

;

Re: Converting a date to number in SQL

Nicole-Smith — Mon, 07 Apr 2014 16:00:23 GMT

A preload should also work instead of using two tables:

ODBC CONNECT TO [SC5 LIVE];

SCCallTess:

LOAD Call_Num,

    Call_InDate etc

where num(Call_InDate)>40179;

SQL SELECT

    Call_Num,

    Call_InDate etc

FROM SC5LIVE.dbo.SCCall

where Call_Num >= 100000;

Re: Converting a date to number in SQL

robert99 — Mon, 07 Apr 2014 16:53:19 GMT

Thanks

This worked

Nicole and Sri. Thanks for your replies. I will check your solutions tomorrow

Re: Converting a date to number in SQL

robert99 — Tue, 08 Apr 2014 16:02:27 GMT

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work#comment-15729

This is also very helpful. To ensure date is a value not text. Then

Let vDateFrom = Num ('01/01/2010') //or could include the appropriate number

then

where Call_InDate >= $(vDateFrom);

should work without converting the date to a number (I will test at work tomorrow)

EDIT This worked. And was the approach I used. I wanted to convert the Call_InDate to a number but this was not necessary with this approach