Page 2 of 2
Posted: Fri Jan 05, 2007 3:05 pm
by I_Server_Whale
iwin wrote:Hey Ken,
I just noticed that the min date value in oracle is "0016/11/10" where as for Sql server it is "1990/01/03". I am assuming this maight be the problem.
Why assume? You can test it by passing only these values through by having a constraint in your transformer. Then, you will know for sure.
Whale.
Posted: Fri Jan 05, 2007 3:12 pm
by I_Server_Whale
The valid date ranges for SQL Server Date datatypes are as below:
Code: Select all
Data type Range Accuracy
datetime January 1, 1753, through December 31, 9999 3.33 milliseconds
smalldatetime January 1, 1900, through June 6, 2079 1 minute
Whale.
Posted: Fri Jan 05, 2007 3:13 pm
by iwin
Thanks
I_Server_Whale wrote:The valid date ranges for SQL Server Date datatypes are as below:
Code: Select all
Data type Range Accuracy
datetime January 1, 1753, through December 31, 9999 3.33 milliseconds
smalldatetime January 1, 1900, through June 6, 2079 1 minute
Whale.
Posted: Fri Jan 05, 2007 9:58 pm
by I_Server_Whale
iwin,
Your reply causes wonder
whether the problem was solved or not. If it is solved, it would be great if you could share the solution and mark the topic as resolved.
If not, then we will dig into it further as per your response.
Thanks,
Whale.
Posted: Mon Jan 08, 2007 11:26 am
by iwin
Hey Whale,
Sorry for late response. Actually the issue has not been resolved. The issue is that sql server is not accepting a date '0016-01-01 00:00:00'. It is one of Y2k problem.
Any suggestions please...
Thanks.
I_Server_Whale wrote:iwin,
Your reply causes wonder
whether the problem was solved or not. If it is solved, it would be great if you could share the solution and mark the topic as resolved.
If not, then we will dig into it further as per your response.
Thanks,
Whale.
Posted: Mon Jan 08, 2007 11:42 am
by I_Server_Whale
iwin wrote:Hey Whale,
The issue is that sql server is not accepting a date '0016-01-01 00:00:00'. It is one of Y2k problem.
Any suggestions please...
I don't think it is a Y2K problem.
Is 0016 equal to 16 A.D ? Or is it 2016? If its 16 AD (0016), then SQL Server will not accept it because the value is not within the range of acceptance.
I have provided the acceptable range of values for both 'datetime' and 'smalldatetime' datatypes in my previous post.
Year 0016 falls below the minimum date value required for both datatypes, hence the rejection by SQL Server. Ask your business folks on how they want to address this. May be you can assign a default date (like 1900-01-01) for a date which doesn't fall within SQL Server valid date range. If you need a routine which checks whether a date falls within the valid range, I can surely help you with that.
Whale.
Posted: Mon Jan 08, 2007 11:57 am
by iwin
I_Server_Whale wrote:iwin wrote:Hey Whale,
Year 0016 falls below the minimum date value required for both datatypes, hence the rejection by SQL Server. Ask your business folks on how they want to address this. May be you can assign a default date (like 1900-01-01) for a date which doesn't fall within SQL Server valid date range. If you need a routine which checks whether a date falls within the valid range, I can surely help you with that.
Whale.
Hey,
I was told to reject the row if it doesnt fall within the date range. Can you please provide me the routine.
thanks
Posted: Mon Jan 08, 2007 12:01 pm
by narasimha
Add the date range check to your constraints, that should work.
Posted: Mon Jan 08, 2007 12:10 pm
by I_Server_Whale
Just wanted to let you know, there is no need to quote previous posts most of the time unless it is absolutely necessary.
The routine codes are as below, I didn't get much time to write the code, but they do work.
If it's a 'smalldatetime' datatype, then use this one,
Code: Select all
RoutineName = 'SQLServerSmallDateTimeRangeValidation'
If IConv(Arg1[1,10],"D-YMD[4,2,2]") >= -24835 and IConv(Arg1[1,10],"D-YMD[4,2,2]") <= 40700
Then
Ans = Arg1[1,16]
End
Else
Ans = '1900-01-01 00:00'
End
Return(Ans)
If it's a 'datetime' datatype then use this one,
Code: Select all
RoutineName = 'SQLServerDateTimeRangeValidation'
If IConv(Arg1[1,10],"D-YMD[4,2,2]") >= -78525 and IConv(Arg1[1,10],"D-YMD[4,2,2]") <= 2933598
Then
Ans = Arg1[1,16]
End
Else
Ans = '1900-01-01 00:00'
End
Return(Ans)
HTH,
Whale.
Posted: Mon Jan 08, 2007 12:12 pm
by I_Server_Whale
narasimha wrote:Add the date range check to your constraints, that should work.
Narasimha, I didn't get that. Could you elaborate on that if possible?
Many Thanks,
Whale.
Posted: Mon Jan 08, 2007 12:24 pm
by chulett
The point is there's no need to write routines to do this validation. Check the year portion of the date and if it is below an acceptable range, reject it. Stage variables would work nicely for this and if named appropriately make it all 'self documenting'.
Worst case, send it to SQL Server and catch whatever it spits back.
Posted: Mon Jan 08, 2007 12:24 pm
by narasimha
I_Server_Whale wrote:narasimha wrote:Add the date range check to your constraints, that should work.
Narasimha, I didn't get that. Could you elaborate on that if possible?
Many Thanks,
Whale.
Sure.
You can limit the rows that pass through using constraints.
The some conditions you have used in your routine can be modified and be used in the constraints.
It can directly be put there, or you can use a Stage variable to do the calculations and use it in your constraint condition.
Only the conditions that are true, i.e date inside the date range pass through.
There can be many ways to skin the cat
Posted: Mon Jan 08, 2007 12:29 pm
by iwin
Thanks guys.
The issue has been resolved. I have a used a constraint to reject rows which do not fall within the date range., i.e.. iconv(arg1[1,10],"D-DYM") > -78525.
Posted: Mon Jan 08, 2007 12:31 pm
by I_Server_Whale
iwin wrote:
Hey,
I was told to reject the row if it doesnt fall within the date range. Can you please provide me the routine.
thanks
Well! I didn't read the post with attention.
I just couldn't see the
reject portion. I totally missed out on that.
And in turn, I assumed that the OP wanted to load a default date if the incoming date is out of range.
Hence, all the confusion. Thanks everybody for making me open my eyes wide. Was nice wake-up call.
Whale.
Posted: Mon Jan 08, 2007 12:34 pm
by I_Server_Whale
iwin wrote:Thanks guys.
The issue has been resolved. I have a used a constraint to reject rows which do not fall within the date range., i.e.. iconv(arg1[1,10],"D-DYM") > -78525.
You can just use:
Whale.