topic Re: Load Dates Greater Than Date. in QlikView

Load Dates Greater Than Date.

bnelson111 — Fri, 28 Aug 2015 13:32:00 GMT

Trying to load only dates greater than a date.

Currently getting the following error.

SQL##f - SqlState: 22008, ErrorCode: 242, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

SQL SELECT *

FROM "Live PSA".dbo.Job

where ("processtype" like 'PRT')AND ("status" IN ('C'))and("ataskenddt" > '27/03/2014 05:53:00')

Script is

LOAD processtype,

status,

tech4,

tech6,

TEXT((tech6) & '-' & (tech4))AS [LInk%],

Description,

ataskenddt,

ataskendyw,

ordernumber;

SQL SELECT *

FROM "Live PSA".dbo.Job

where ("processtype" like 'PRT')AND ("status" IN ('C'))and("ataskenddt" > '27/03/2014 05:53:00') ;

The format of ataskenddt is 27/03/2014 05:53:00 so in this case i only want the dates greater than 27/03/2014 05:53:00.

Cheers

Re: Load Dates Greater Than Date.

jonathandienst — Fri, 28 Aug 2015 14:06:35 GMT

>>The format of ataskenddt is 27/03/2014 05:53:00

Are you sure? The error clearly says that it does not recognise the date string you supplied. Check the canonical date format on the server (like dd-mm-yyyy hh:mm:ss) or use the SQL Convert() function with the correct formt code to convert the string to a date value. Check SQL BOL for the correct conversion code.

Re: Load Dates Greater Than Date.

greg-anderson — Fri, 28 Aug 2015 14:08:04 GMT

In SQL Server, try this in place of just the datetime string:convert(datetime, '27/03/2014 05:53:00', 131)

Re: Load Dates Greater Than Date.

bnelson111 — Fri, 28 Aug 2015 15:18:03 GMT

okay getting further, load now looks like the following with no error but unfortunately no data selected, i have set the greater than date by an variable.

Let vEndDate = Timestamp(Today()-7);

ODBC CONNECT32 TO [PSA Live] (XUserId is LZGbBSNOGDIA, XPassword is OZVPSZRMGLaA);

//-------- Start Multiple Select Statements ------

[PSA]:

LOAD processtype,

status,

tech4,

tech6,

TEXT((tech6) & '-' & (tech4))AS [LInk%],

Description,

ataskenddt,

ataskendyw,

ordernumber;

SQL SELECT *

FROM "Live PSA".dbo.Job

where ("processtype" like 'PRT')AND ("status" IN ('C')) and ("ataskenddt" >convert(datetime, '$(vEndDate)', 131));

data table

Re: Load Dates Greater Than Date.

greg-anderson — Fri, 28 Aug 2015 15:27:04 GMT

Hi, Brett.

Two things:

1) Don't use 'timestamp' in SQL Server. It's buggy. Use datetime instead.

2) If you declare the variable as a datetime, then you won't need the convert function. It is used only to convert from my data type to another (e.g., varchar to datetime).

Oh, three things:

3) If 'ataskenndt' is a string representing a datetime, then you'll need to use the CONVERT function on it

Re: Load Dates Greater Than Date.

kiranmanoharrode — Fri, 28 Aug 2015 16:07:42 GMT

Hi Brett,

I think you must change the format of filter date to 'DD-MMM-YYYY HH:MM:SS'

Regards,
Kiran

Re: Load Dates Greater Than Date.

Colin-Albert — Fri, 28 Aug 2015 16:17:31 GMT

Try using the date format 'YYYY-MM-DD hh:mm:ss' for your date variable, as this date format does not have any inconsistencies on whether day or month is in the correct order, and always converts correctly in MSSQL.

Let vEndDate = text(date((Today()-7), 'YYYY-MM-DD hh:mm:ss'));

ODBC CONNECT32 TO [PSA Live] (XUserId is LZGbBSNOGDIA, XPassword is OZVPSZRMGLaA);

//-------- Start Multiple Select Statements ------

[PSA]:

LOAD processtype,

status,

tech4,

tech6,

TEXT((tech6) & '-' & (tech4)) AS [LInk%],

Description,

ataskenddt,

ataskendyw,

ordernumber;

SQL SELECT *

FROM "Live PSA".dbo.Job

where ( "processtype" like 'PRT' )

AND ( "status" IN ('C') )

and ( "ataskenddt" > '$(vEndDate)' );

Re: Load Dates Greater Than Date.

bnelson111 — Fri, 28 Aug 2015 20:32:58 GMT

Thanks everyone, sorry for late reply been working flat out. Thanks to everyone