Converting timestamp to a number in SQL

[#1]

Hi

I created a standard incremental loader based on the Date field. I'm having difficulties changing the data format of my date field to a number. Date format in SQL database: 2015-02-04 00:00:00:.0000000', I want to replace this with a number format, i.e. 42040, within the SQL part of the load script (I need this in my where clause). Any ideas how this can be achieved?

Thanks

[Gysbert_Wassenaar]

Timestamps are numbers. You don't have to convert the timestamp value to use it as a number.

[#3]

Actually it is a character string, I just run the script again and this is the error:

Conversion failed when converting date and/or time from character string.

any ideas?

[awhitfield]

What's the datatype of your date in SQL?

if it's datetime2 then: 'Explicit conversion from data type datetime2 to int is not allowed.'

[spividori]

Hi.

I use the following:

num(date(left(ColumnDate,10),'DD-MM-YYYY'))

Regards.

[#6]

I tried this but 'date' is not a recognized built-in function name

my where clause is in the SQL part of the script under SQL Select

[awhitfield]

Can you post your SQL select please?

[spividori]

Sorry, this is for use in the load.

Regards.

[gandalfgray]

Hi Katarzyna

can you post the relevant part of your where clause here, please?

[Gysbert_Wassenaar]

Use the CAST function in sql or the timestamp# function in a qlikview load statement.