Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

bnelson111
Contributor

Load Dates Greater Than Date.

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

1 Solution

Accepted Solutions

Re: Load Dates Greater Than Date.

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)' );

7 Replies
MVP
MVP

Re: Load Dates Greater Than Date.

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Luminary
Luminary

Re: Load Dates Greater Than Date.

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

bnelson111
Contributor

Re: Load Dates Greater Than Date.

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

data table.png

Luminary
Luminary

Re: Load Dates Greater Than Date.

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 

kiranmanoharrode
Contributor III

Re: Load Dates Greater Than Date.

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.

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)' );

bnelson111
Contributor

Re: Load Dates Greater Than Date.

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