Date Convert from Char

[zishan876]

Hi all:
I have a field in my sql server table which is a char(10). But it has dates in it as 2006-01-05, etc... Now I am uploaded this into another system which the field on that system is a date field...
It will accept everything only when the date on sql server is 0001-01-01 the other system will kick it back with errors and the job will stop...
I did an update query of whereever 0001-01-01 exists switch it to ''... but that create errors as well...
Anyone got any ideas on what should I do...
Thanks
Zishan

 

[r937]

should've updated the invalid dates to NULL instead

r937.com | rudy.ca

 

[AlexCuse]

you will want to allow nulls, and switch 0001-01-01 to NULL.

OR, you could replace it with some value in the past but I'm not sure how much I like the idea of having a real date being in there.

I suppose it depends on your data.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

the other system will kick it back with errors

We can't possibly tell you what the other system will accept, and on top of that we can't possibly tell you what the business rules are for the things you are working with. What SHOULD be done when the date is missing? What does it represent in the data source? How is that same meaning represented in the data recipient?

Once you answer those questions, you should have all you need to know what to do. Anyone telling you what you should do is simply giving wild stabs in the dark. They might be right. But YOU have to find out about YOUR systems.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[monksnake]

Damn, calm down E^2, it's not so bad (life, that is).

<.

 

[r937]

using NULL for an unknown value is not a wild stab in the dark



r937.com | rudy.ca

 

[AlexCuse]

E[sup]2[/sup] - I think that storing a null in place of the unknown date would be the best possible route to take in this situation. Sure, the best thing to do is ensure that you don't have unkown dates when your data is collected, but it is clearly too late for that.

I'm not sure what the alternative is, really. I just threw out the use of some arbitrary date in case the column in question did not allow nulls (and could not be forced to allow nulls for some reason).

Do you have any ideas for what to put there besides null? You have piqued my interest.

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

Suggesting random input to an unknown system is dicey. NULL is probably the best guess (and it would be my guess too), but it's nothing more than that.

So I still say that using NULL IS a wild stab in the dark until you test it with the other system and learn its behavior. Why not find out what that other system expects, first?

I am calm. If I sounded upset it was because of passion for data integrity and not because I have lost any of my equanimity.

See, how do you guys know that the output system accepts NULLs or what NULLs mean to that system?

For all we know, the other system is designed so that it has another column that tells it what to do when the value is NULL, or it has an override column that means to ignore the date value.

What if that system expects "1901-01-01" for "no date" or "1899-12-31"? These are values that many systems do use.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]