Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)' );
>>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.
In SQL Server, try this in place of just the datetime string:convert(datetime, '27/03/2014 05:53:00', 131)
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
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
Hi Brett,
I think you must change the format of filter date to 'DD-MMM-YYYY HH:MM:SS'
Regards,
Kiran
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)' );
Thanks everyone, sorry for late reply been working flat out. Thanks to everyone