Converting a date to number in SQL

[robert99]

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

[#2]

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

[maxgro]

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)

;

[Nicole-Smith]

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;

[robert99]

Thanks

This worked

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

[robert99 · Accepted solution]

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