Load Dates Greater Than Date.

[bnelson111]

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

[jonathandienst]

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

[greg-anderson]

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

[bnelson111]

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

[greg-anderson]

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]

Hi Brett,

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

Regards,
Kiran

[Colin-Albert · Accepted solution]

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]

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