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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy