May 5, 2011 at 6:52 am
Simply said : I have a table named Intermediate Invoices, within this table i have two filled columns that have a date and a due date value, due to importing limitations they are stored in two text columns called Description 7 and 8 (real columns are date and due date, datetime sql columns). The notation within 7 and 8 are dd-mm-yyyy, they hold 9.4k records of which 500 are either empty or filled with a NULL value.
I'm trying to get the data within the table moved from :
Description 7 -> Date
Description 8 -> Due date
I've tried updating the first one while using the below query :
INSERT INTO [Intermediate invoices] (Date)
Select distinct convert(datetime,[Description 7],105) from [Intermediate invoices]
For some reason this only affects 428 rows and truly inserts the converted datetime value as new records within the table increasing the records to a rough 9.8k where only date is filled in and all other data within the table for those records remains empty. For one i dont know why it does that (hell, i dont even know which records it used to get those date's) and second i dont get why it only affects the 428 rows and not the full 9.4k. I could understand issues with null's or empty ones but i dont think thats the problem.
Any thoughts ?
May 5, 2011 at 7:18 am
I've tried updating the first one while using the below query :
INSERT INTO [Intermediate invoices] (Date)
Select distinct convert(datetime,[Description 7],105) from [Intermediate invoices]
you said trying to UPDATE, but your command is INSERTING...and it's inserting just distinct dates, which makes sense that it is less dates than all rows.
forget the insert for a moment, and just try to get the data itself.
does this return what you are after? it should return 9.4K rows, because there is no DISTINCT.
Select convert(datetime,[Description 7],105) from [Intermediate invoices]
Lowell
May 5, 2011 at 7:47 am
First of all, thanks for the reply.
Without the DISTINCT it indeed returns the correct amount of records
May 5, 2011 at 7:48 am
I guess i'm partly approaching this wrongly figuring i am still inserting into a field, it makes sense if you narrow it down that it actually inserts as new
May 5, 2011 at 7:58 am
i think this is clsoer to what you are trying to do:
IF you can find a date in the description, set the [Date] field to that value:
UPDATE [Intermediate invoices]
SET [Date] = CASE
WHEN ISDATE(convert(datetime,[Description 7],105)) =1
THEN convert(datetime,[Description 7],105)
ELSE NULL
END,
Then After we have the [Date] field populated., do we calculate a logical [Due Date]?
--Due Date is + 30 days more than The Regular Date?
UPDATE [Intermediate invoices]
SET [Due date] = DATEADD(mm,1,[Date])
Lowell
May 5, 2011 at 8:16 am
Dear Mister Lowell,
(9465 row(s) affected)
Not only does it work but i actually understand why to go this way. I'm on a SQL course and trying to pick up as much as i can but sadly the company i work for needs some stuff fixed and we needed it fast hence my attempt so far. Your solution works, truly appreciated !
For the due date there's basically the same query only it reads from description 8 instead of 7, just adjusted the query for it and it is now also updated. The database itself is from a credit management system where due date is dependant on how "well" the customer has paid in the past so it varies hence it comes from a manually entered field.
Thank you so much for the time spent and the solution supplied !
Sincerely,
Patrick
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply