topic Re: Converting timestamp to a number in SQL in QlikView

Converting timestamp to a number in SQL

Wed, 11 Mar 2015 11:47:06 GMT

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

Re: Converting timestamp to a number in SQL

Gysbert_Wassenaar — Wed, 11 Mar 2015 11:52:37 GMT

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

Re: Converting timestamp to a number in SQL

Wed, 11 Mar 2015 11:56:09 GMT

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?

Re: Converting timestamp to a number in SQL

awhitfield — Wed, 11 Mar 2015 12:08:25 GMT

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.'

Re: Converting timestamp to a number in SQL

spividori — Wed, 11 Mar 2015 12:10:07 GMT

Hi.

I use the following:

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

Regards.

Re: Converting timestamp to a number in SQL

Wed, 11 Mar 2015 12:13:24 GMT

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

Re: Converting timestamp to a number in SQL

awhitfield — Wed, 11 Mar 2015 12:20:16 GMT

Can you post your SQL select please?

Re: Converting timestamp to a number in SQL

spividori — Wed, 11 Mar 2015 12:21:28 GMT

Sorry, this is for use in the load.

Regards.

Re: Converting timestamp to a number in SQL

gandalfgray — Wed, 11 Mar 2015 12:21:30 GMT

Hi Katarzyna

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

Re: Converting timestamp to a number in SQL

Gysbert_Wassenaar — Wed, 11 Mar 2015 12:37:04 GMT

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

Re: Converting timestamp to a number in SQL

MK_QSL — Wed, 11 Mar 2015 12:54:53 GMT

cast(mycol as varchar2(30))

Re: Converting timestamp to a number in SQL

anbu1984 — Wed, 11 Mar 2015 15:59:36 GMT

If you have to_date() in your database, then try this to convert character string to date

to_date(Substr('2015-02-04 00:00:00:.0000000',1,19),'YYYY-DD-MM HH24:MI:SS')