Trouble Setting a Variable

[rittermd]

In my script I am trying to handle what happens when we switch to a new year.

So I want to set a variable with the value of the date-time for the start of the year.

For example when we move to 2018  I want the variable to contain the numeric value for 01/01/2018 00:00:00

I have not quite figured out the correct combination to make this happen.

Thanks for your help.

[sunny_talwar]

May be this

TimeStamp(YearStart(Today()), 'DD/MM/YYYY hh:mm:ss')

[Anonymous]

Set it to the value of :

    timestamp(yearstart(today()))

[rittermd]

I guess I didn't ask the question correctly.

I don't want the variable to have the value '01/01/2018 00:00:00'

I want it to have the value 43101.00000000000 if that is possible

[sunny_talwar]

May be this

Num(YearStart(Today())

or

Num(YearStart(Today(), #.00000000000)

[Anonymous]

Timestamps are held as dual values, as in they hold both the numeric and human readable values.

This is an old Blog Post by HIC, but the concepts are still the same as they were 5 years ago.

Get the Dates Right

[rittermd]

Here is my issue. 

The data in my file is formatted with the Qlik numeric value for date and time. 

So the field contains a value like 41264.748611111

In my script I need a variable that I can compare this to and select the records that were added after this date/time.

So I want my variable to have the value 43101.00000000 (start of 2018). 

I have tried all of the recommendations above and none of them are working.  I can create a variable using them.  But when I run the script with the where clause below it does not work.

Here is my load:

LOAD * ;

  SQL SELECT *

  from ArmorData.dbo.StopData

  WHERE ImportTime > $(vImportTime);

[sunny_talwar]

Try this

LET vImportTime = Date(YearStart(Today()), 'DD-MMM-YYYY');

LOAD * ;

SQL SELECT *

FROM ArmorData.dbo.StopData

WHERE ImportTime > TO_DATE('$(vImportTime)', 'DD-MON-YYYY');

[rittermd]

Get an error on TO_Date and get an error on format of the LET statement.  Doesn't like the comma.

[sunny_talwar]

Can you share a screenshot for the variables? What date format do you supply when you run the query outside of QlikView in Toad?