Datatime Field Overflow

[I_Server_Whale]

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.

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

[iwin]

Thanks

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.

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

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

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.

[I_Server_Whale]

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.

[iwin]

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

[narasimha]

Add the date range check to your constraints, that should work.

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

[I_Server_Whale]

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.

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

[narasimha]

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

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

[I_Server_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

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.

[I_Server_Whale]

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:

Code: Select all

inlink.date[1,4] > 1753

Whale.