Answered by:
SSIS GETDATE() expression returning null or blank value

-
Using SQL2008 SP3, I have an SSIS package that uses an SQL Task. Within it, I have the following expresion to get the current date and time and insert it in a table:
SUBSTRING((DT_WSTR, 50) GETDATE(), 1, FINDSTRING((DT_WSTR, 50) GETDATE(), ".",1) -1)
it works great 99.9% of the time, yet once in a while, I get the following error message when evaluationg this expression:
The length -1 is not valid for function "SUBSTRING". The length parameter cannot be negative. Change the length parameter to zero or a positive value.So, my guess is that on that run the GETDATE() function returned null or blank or something that was not able to be used by the SUBSTRING function.
Any ideas what could be wrong? Is it just a hick up in the system?
Thanks so much for your feeback.
Paulino
Question
Answers
-
My only guess is when your server is under stress the portion after the . (microseconds) of the time returned by GETDATE() e.g. 2013-06-27 11:27:13.313000000 is not there and therefore the expression fails.
Arthur My Blog
- Proposed as answer by ArthurZMVP, Moderator Wednesday, July 03, 2013 3:23 AM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Thursday, July 04, 2013 1:47 PM
-
The above experession evaluates to
2013-06-27 17:01:20
Do you intend to see this format of date?
In order to get this format, use exec sql task and use this expreession in tha task
select CONVERT(varchar,GETDATE(),120)
save this result set into a variable, that can be later used in insert statemnt to dest table.
Thanks, hsbal
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Sunday, June 30, 2013 6:18 AM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Thursday, July 04, 2013 1:47 PM
All replies
-
My only guess is when your server is under stress the portion after the . (microseconds) of the time returned by GETDATE() e.g. 2013-06-27 11:27:13.313000000 is not there and therefore the expression fails.
Arthur My Blog
- Proposed as answer by ArthurZMVP, Moderator Wednesday, July 03, 2013 3:23 AM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Thursday, July 04, 2013 1:47 PM
-
The above experession evaluates to
2013-06-27 17:01:20
Do you intend to see this format of date?
In order to get this format, use exec sql task and use this expreession in tha task
select CONVERT(varchar,GETDATE(),120)
save this result set into a variable, that can be later used in insert statemnt to dest table.
Thanks, hsbal
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Sunday, June 30, 2013 6:18 AM
- Marked as answer by Mike YinMicrosoft contingent staff, Moderator Thursday, July 04, 2013 1:47 PM
-
-