Date in SELECT statement - QV script

[#1]

Hi all,

I'm stuck with a problem.

I want to use the current date (7-4-2014) in my SELECT statement.

Codetracker:

SQL

SELECT

Code,
Count(Code) as aantal,
CurrentDate() as afldatum
FROM DATASTORE.dbo."DS_ORDRS"

WHERE Code not in (0,99)

;

I have searched on the internet to find the solution, but all I can find is how to use date outside QV script, or in the WHERE statement in SQL script.

Anyone know how I can solve this?

[#2]

Hi,

Try this

Load Code,

         Today() as afldatum,

         Count(Code) as aantal

Group By Code;

SQL SELECT

Code
FROM DATASTORE.dbo."DS_ORDRS"

WHERE Code not in (0,99)

;

[#3]

Ravi,

That's not working.

It gives me an error of incorrect syntax (no FROM after the first LOAD?)

I tried to give only the important part of the statement, but maybe that's confusing. So this is the whole SQL SELECT:
Today() doesn't work in SQL, so how can I put today's date on the 7-4-2014 line?

SQL

Select Zuil,
Code,
Count(Code) as aantal,
'7-4-2014' as afldatum

FROM DATASTORE.dbo."DS_ORDRS"
Where Code not in (0,99)
and right (Zuil,1) in (3,7)
group by Zuil, Code
Order by Zuil, Code
;

PS: This works. But I need a dump every day, so that's why I need a funtion as Today()

[maxgro]

if you want current date (and / or time) in sql please let we know for which database

sql server     --> getdate()

oracle          --> sysdate

etc....

[#5]

I use the OLE DB Connect in Qlikview.

[qvwarning]

Hi Kevin,

try preceding load of data with 'where' in 2. load

like:

Load

    Date(F1_Date) as DATE_NEW,

    F2

Where Date(F1_Date)='07.04.2014';

Load *  Inline

    [F1_Date, F2

    01.01.2014,10

    02.01.2014,20

    07.04.2014,70];

[#7]

Hi Andreas,

I just want to use the current date of today.

I'm loading from my DB and I want to add a column with the current date.

This script will load every day and will be stored into a QVD file with use of the CONCATENATE function.

[jerrysvensson]

Ravis code works.

"Today() doesn't work in SQL, so how can I put today's date on the 7-4-2014 line?"

Look at his example again:

Today() is called in the LOAD section, NOT in the SQL SELECT section.

Table:

Load Code,

         Today() as afldatum,

  Count(Code) as aantal

Group By Code;

SQL SELECT

Code
FROM DATASTORE.dbo."DS_ORDRS"

WHERE Code not in (0,99)

[qvwarning]

Hi Kevin,

the script below get values from today only.

you get the dimension DATE_Today with current date.

then load the qvd und concatenate with preceding load [ValueToday]

regards

Andreas

[#10]

Thanks for all the help guys.

I found a different solution on the community, using a variable in the SQL statement.

So I didn't check the last two solutions.

This is the solution I used.

Let vToday = Today();

SQL
Select Zuil,
Code,
Count(Code) as aantal,
'$(vToday)' as AflDat

FROM DATASTORE.dbo."DS_ORDRS"
Where Code not in (0,99)
and right (Zuil,1) in (3,7)
group by Zuil, Code
Order by Zuil, Code
;