Extending minimum datetime values

Question

Extending minimum datetime values

danielagger98

Mr or Mrs. 500

Points: 502
More actions
February 17, 2011 at 2:12 pm

#235271

I want to extend the minimum datetime value(1973), some of the oracle date fields have date 01-01-0006, not that it makes any sense.
But if the management wants it, then they want it. So the SSIS pacakge is failing because it can't import datetime less than 1973 year. Please give me some ideas here. thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You're pretty much going to be stuck using a string data type for these, and then doing transformations/redirects on them based on the logic your target database needs to comply with.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

danielagger98 (2/17/2011)
I want to extend the minimum datetime value(1973), some of the oracle date fields have date 01-01-0006, not that it makes any sense.
But if the management wants it, then they want it. So the SSIS pacakge is failing because it can't import datetime less than 1973 year. Please give me some ideas here. thanks.

IIRC, the minimum datetime value in SQL Server 2005 is actually 1753... not that that will help with dates like 01-01-0006.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 3

Jeff Moden (2/17/2011)
danielagger98 (2/17/2011)
I want to extend the minimum datetime value(1973), some of the oracle date fields have date 01-01-0006, not that it makes any sense.
But if the management wants it, then they want it. So the SSIS pacakge is failing because it can't import datetime less than 1973 year. Please give me some ideas here. thanks.
IIRC, the minimum datetime value in SQL Server 2005 is actually 1753... not that that will help with dates like 01-01-0006.

SSIS has a different means of dealing with date values than SQL Server (which doesn't make a lot of sense, but seems to be true). I think SSIS's version does have less range, closer to smalldatetime, if I'm not mistaken.

As mentioned, moot point because of the not-a-date values that'll need to be cleaned up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

Ah... didn't know that. Thanks, Gus.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

danielagger98 Mr or Mrs. 500 Points: 502 More actions · Answer 5

Sorry. It is 1753, was a typo. and SQL Server 2005.

So I see the only way is either to change the data type of the destination table to varchar

or do data transformation(if date less than 1973'oops' then 1753) and bring it to the destination.

GSquared SSC Guru Points: 260824 More actions · Answer 6

Or define a user-defined-data-type that'll hold the date range you're looking for.

Dates in SQL Server are stored as numbers. You could easily extend to bigint for the date part, and you'd get "valid" values going WAY back and forward. You wouldn't be able to use the built in date functions on them, unless they're in-range for datetime, but that may not matter.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

GSquared (2/21/2011)
Or define a user-defined-data-type that'll hold the date range you're looking for.
Dates in SQL Server are stored as numbers. You could easily extend to bigint for the date part, and you'd get "valid" values going WAY back and forward. You wouldn't be able to use the built in date functions on them, unless they're in-range for datetime, but that may not matter.

If you predate 1753, then wouldn't you also have to juggle by country and date of adoption for the Gregorian Calendar?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 8

Jeff Moden (2/21/2011)
GSquared (2/21/2011)
Or define a user-defined-data-type that'll hold the date range you're looking for.
Dates in SQL Server are stored as numbers. You could easily extend to bigint for the date part, and you'd get "valid" values going WAY back and forward. You wouldn't be able to use the built in date functions on them, unless they're in-range for datetime, but that may not matter.
If you predate 1753, then wouldn't you also have to juggle by country and date of adoption for the Gregorian Calendar?

For "year" and "month", yes. For "how many days since day 0", no.

You could define various geo-calendar definitions against the UDDT. That's pretty much what they've done with all the date definition stuff in SQL Server anyway. Archaelogy databases do that kind of thing, since they definitely need dates WAY outside the "usual" range. You'd be re-inventing a whole set of wheels, but there's data out there on how to do it. (Paleontology and Astronomy/Astrophysics databases use an even wider "date range", but often don't need "day" precision.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

paul.knibbs SSCoach Points: 15320 More actions · Answer 9

Silly question: this 0006 date isn't supposed to be 2006, is it? Would be a lot easier to handle if that's the case.