Just gotta gripe...
-
For as smart as SQL Server can be, it sure can be STUPID sometimes!!!
I've got a varchar(10) field with a date in yyyy-mm-dd format. The data was imported from a Foxpro table so it's century challenged. Dates that should be 2000 are 1900, 2001 - 1901 and so on.
Ok, simple. Use DateAdd in an update query to fix it, right?
update table set field = case when year(field) = 1900 then dateadd(yy, 100, field) else field end
What does it give me?? 47,000 rows with dates like:
Jan 01 200
GRRRRRRRRRR!!!!!!
-
yes, i sometimes finding working with dates in sql server requires a bit more work. like in your scenario, I have to format my date so that it is in the yyyy-mm-dd format so that it fits in the varchar(10) field. I might also consider adding a new date field to store the date value instead.
-
Yah, convert(varchar(10), field, 120) would have taken care of it. It'd just be nice if it would warn that the format would truncate data, or fail because of that, or simply pick a format that would fit in the field size allocated. Ugh.
-
There's nothing stupid to it. Use correct datatypes or correct functions.
Correct Datatyping is "an effort", but has huge advantages in the long run.
Basic rule #1 : Tel your rdbms-engine what you know.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Why are you using a varchar 10 instead of a datetiem field? When using a date time field, your imported data would probably have been imported correctly in the first place since SQL determines the centry upon conversion. An in cases where it was not converted correctly (pre 1950 dates) would be a much smaller dataset to update than updating all dates.
If there is no other choice but to convert dates to strings then one of two thing can be done a. use varchar(12) or add convert to your equasion to convert the date to a numerical format.
convert(varchar(10), dateadd(yy, 100, field), 101)
This will convert the date a string of mm/dd/yyyy. There are other posibilities (see convert in bol)
quote:
For as smart as SQL Server can be, it sure can be STUPID sometimes!!!I've got a varchar(10) field with a date in yyyy-mm-dd format. The data was imported from a Foxpro table so it's century challenged. Dates that should be 2000 are 1900, 2001 - 1901 and so on.
Ok, simple. Use DateAdd in an update query to fix it, right?
update table set field = case when year(field) = 1900 then dateadd(yy, 100, field) else field end
What does it give me?? 47,000 rows with dates like:
Jan 01 200
GRRRRRRRRRR!!!!!!
Chad Hattabaugh MCDBA-SE
Chad Hattabaugh MCDBA-SE
Chattabaugh@earthlink.net -
I'm using a varchar(10) field because some of the data in the field is garbage. If I could count on all of the records coming in from Foxpro to be a valid date, I would have used the proper data type.
-
Scrubbing has to occur. Personally I'd import using varchar, but then clean and move to a datetime to be sure I had valid dates. Those that didn't move could be manually checked.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Y'all are missing the point. Here's an example:
create table #test (field varchar(1))
insert into #test (field) select 'a'
update #test set field = 'abcd'
The update statement will fail with the message:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Now, why wouldn't that same error be raised during the update in my first post? Data is truncated, but SQL allows the update.
-
Is is definitely stored as a varchar field and not a datetime field?
-Bill
-
Yup. Datetime field wouldn't have lost any data...
-
I understand you point about the truncation not ginving you an error -- It should.
I do some data imports from a 4GL/DBMS called "Progress" where we have the same problem of soem dates beign invalid. I import the data into a varchar(12) field then convert the data to a datetime field with the following code
case
when isdate(<string>)=1 then <string>
else '1/1/1800'
end
we use the date '1/1/1800' as a "non date date" this tells us that there was data in the field but it was not a valid date.
You could modify this to check for centry limits of sql
quote:
Y'all are missing the point. Here's an example:create table #test (field varchar(1))
insert into #test (field) select 'a'
update #test set field = 'abcd'
The update statement will fail with the message:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Now, why wouldn't that same error be raised during the update in my first post? Data is truncated, but SQL allows the update.
Chad Hattabaugh MCDBA-SE
Chad Hattabaugh MCDBA-SE
Chattabaugh@earthlink.net -
I had a similar problem with a DTS package. I was using a transform to load data from a text file to a table. One of the columns was a claim number that was incremental. After about a year post-live the claim number became too large for the column and SQL Server went on it's merry way truncating the last character off the end with no errors.
I thought perhaps this was an "option" but couldn't find any documentation in the brief amount of time that I looked.
As for your date issues you might try using the isdate() function. That will definitely error out on an invalid date. Not that it would help with your truncation problem....
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek) -
Right. I used IsDate() in the where clause.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply