Why aren't my date fields working (blog advice followed)

[#1]

Sorry to post on such a common topic, but I'm at my wits end! In my defence I have gone through Henric's post on the blog here:

Why don’t my dates work?

I've connected to a SQL server and pulled though some data using this query:

SELECT
HN AS PAS_ID,
Timestamp,
date(Timestamp) as Date,
time(Timestamp) as Time,
Due,
Overdue
FROM DB

The Timestamp field in the SQL database is a Date/Time field.

Date formats are set like this:

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

But I can't get a 'WHERE' statement to work- everything I try, the whole dataset is retrieved. I've tried:

WHERE date(Timestamp) >= '01/01/2015'

WHERE Timestamp >= '01/01/2015 00:00:00'

and all the combinations of these. Have also tried adding a variable:

SET vToday = Today()

Then added

WHERE date(Timestamp)= $vToday

also

WHERE date(Timestamp)= '$vToday'

None of these affect the number of records the query is pulling through.

When I put the Date field (i.e. date(Timestamp)) into a list box and format it as an integer, it looks like a number in the 40,000 range, and formatted as a timestamp it shows as e.g. 01/05/2015 00:00:00- so it is registering as a date, right?

Why aren't my Date, Time or Timestamp fields acting as they should be? Any suggestions welcome.

Adam

[marcus_sommer · Accepted solution]

You used qv functions within a sql-query which will be executed within the database - in this way you could use functions which the database and the odbc-driver support. For such cases is it best practice to use Preceding Load.

- Marcus

[Colin-Albert]

Change your script to this.

The date and time functions are QlikView functions so must be executed in the Qlik part of the load script not in the SQL Query.

load

      PAS_ID,

     date(Timestamp) as Date,

     time(Timestamp) as Time,

     Due,

     Overdue ;

SQL SELECT

     HN AS PAS_ID,

     Timestamp,

     Due,

     Overdue

     FROM DB ;

[#4]

hi

try like this hope it works

temp:

SELECT

HN AS PAS_ID,

Timestamp,

trim(date(Timestamp) as Date,

time(Timestamp) as Time,

Due,

Overdue

FROM DB

no concatinate

Temp1:

*

resident temp where Date>= '01/01/2015';

drop table temp;

Thanks

Mahesh

[#5]

Thanks all, these replies were all helpful. Preceding loads are definitely something I'll need to master, and you've clarified for me which functions I can use where (which I'd been doing before only by sheer luck!).

Much appreciated