March 10, 2008 at 11:23 am
All:
The DB I'm working with stores a date in a varchar field (don't blame me - I didn't design it). Because data is imported from different sources, the format is inconsistant. Some dates appear as '20080310' and others appear as '10/03/2008'.
I have been asked to make all the dates appear consistently, as '20080310'. I've tried several variations of this:
update table
set field = (right(field,4) + substring(field,4,2) + left(field,2))
where substring(field,3,1) = '/'
All of my attempts produce the following message:
Msg 512, Level 16, State 1, Procedure CLookup, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
The statement has been terminated.
So what is the right way to re-arriange characters in a varchar field?
March 10, 2008 at 11:57 am
I mocked up your data an ran your query and it worked OK for me (but it was a small dataset).
Beware if you have any dates like '03/2/2008' or '03/02/08' (anything that is not 10 characters long) because your update will format those incorrectly (it is assuming you have character data.
If it was easy, everybody would be doing it!;)
March 10, 2008 at 12:02 pm
dan (3/10/2008)
Msg 512, Level 16, State 1, Procedure CLookup, Line 19
The reference in your error to line 19, yet there isn't that many lines of code in your SQL update. Is there something else in your procedure that is causing the error? Just guessing here...
If it was easy, everybody would be doing it!;)
March 10, 2008 at 12:09 pm
If I may ask - if you're storing a date value - why would you store it as anything other than a datetime? storing dates as character or numeric values will cause you nothing but heartache....
Display them however you want. Just store dates as dates. Anything else is playing soccer in a minefield.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 10, 2008 at 12:43 pm
Matt
I agree with you on the BAD practice of doing so, however, when I'm doing data profiling, I sometimes store dates as varchar, especially when coming from a non database source. In other words, sometimes it may not be easy to avoid it.
As for the issue at hand, there are so many ways the data may be inputted in a varchar field, I don't know if you can create enough conditional statements to satisfy them all, may want to shoot for a 90 and above percentage rate.
Marvin Dillard
Senior Consultant
Claraview Inc
March 10, 2008 at 12:47 pm
That should have been a clue. And the answer is that this table has an update trigger that updates data in another table. That apparently returns another result that breaks the update.
Running the "alter table disable trigger all" command allowed the update to work. Apparently "line 19" refered to the code inside the trigger.
And yes, I re-enabled the trigger when I was done.
March 10, 2008 at 12:54 pm
Very cool...glad I was able to help.
If it was easy, everybody would be doing it!;)
March 10, 2008 at 1:01 pm
Now that you found a trigger - it sounds like the trigger was created assuming that it would only run into single record updates. If it's written in that way - a "batch" update will cause the trigger to error, often enough with that kind of error.
Depending on what it does - it may be worth re-writing the trigger to allow for multi-row updates.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 10, 2008 at 2:06 pm
dan (3/10/2008)
Some dates appear as '20080310' and others appear as '10/03/2008'.I have been asked to make all the dates appear consistently, as '20080310'. I've tried several variations of this:
You picked the wrong format because it's not sortable... instead of the mm/dd/yyyy format, use the ISO format of yyyymmdd so you can at least do that.
Also, you don't need to work so hard at these types of conversions... same simple formula will handle more than 90% of your expected formats...
SELECT CONVERT(CHAR(8),CAST('3/2/2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('03/2/2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('3/02/2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('03/02/2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('3/2/00' AS DATETIME),112),
CONVERT(CHAR(8),CAST('03/2/00' AS DATETIME),112),
CONVERT(CHAR(8),CAST('3/02/00' AS DATETIME),112),
CONVERT(CHAR(8),CAST('03/02/00' AS DATETIME),112),
CONVERT(CHAR(8),CAST('3-2-2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('03-2-2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('3-02-2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('03-02-2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('3-2-00' AS DATETIME),112),
CONVERT(CHAR(8),CAST('03-2-00' AS DATETIME),112),
CONVERT(CHAR(8),CAST('3-02-00' AS DATETIME),112),
CONVERT(CHAR(8),CAST('03-02-00' AS DATETIME),112),
CONVERT(CHAR(8),CAST('20000302' AS DATETIME),112),
CONVERT(CHAR(8),CAST('000302' AS DATETIME),112),
CONVERT(CHAR(8),CAST('Mar 2 2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('2 Mar 2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('March 2 2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('2 March 2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('2000 Mar 2' AS DATETIME),112),
CONVERT(CHAR(8),CAST('Mar 02 2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('02 Mar 2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('March 02 2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('02 March 2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('2000 Mar 02' AS DATETIME),112),
CONVERT(CHAR(8),CAST('Mar 2, 2000' AS DATETIME),112),
CONVERT(CHAR(8),CAST('March 2, 2000' AS DATETIME),112)
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2008 at 2:43 pm
Jeff, I think the OP is trying to convert to 'yyyymmdd' format.
I do like your solution as it is MUCH more robust.
If it was easy, everybody would be doing it!;)
March 10, 2008 at 2:54 pm
With the gigantic caveat that your slashed data is always stored month/day/year.
March 10, 2008 at 3:02 pm
Actually dfalso - not at all. it's fairly remarkable what formats work right out of the box.... just CAST them to datetime. with or without slashes....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 10, 2008 at 3:14 pm
Sorry, I should have been more clear. The poster needs to be very, very sure that none of his dates are in day/month/year format, which you see used in Europe alot. 3/2/2008 to me is March 2nd, but to someone else it's February 3rd. He'd probably only know that through context.
March 10, 2008 at 5:29 pm
Jeff
That is very robust and you are correct as usual, that will handle most of the conversions and should error on the ones it doesn't. Not sure if he should write a function as it is already one, but I think I might use it on some of my web code to ensure the dates are coverted correctly with a heck of a lot less code.
Thanks
Marvin Dillard
Senior Consultant
Claraview Inc
March 10, 2008 at 7:03 pm
Thanks for the feedback, Marvin. Yeah, and if you throw in a check by IsDate, you can control the error instead of throwing it against the wall to see if it sticks with something like Try/Catch.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply