Copying text -> Datefield

Question

Copying text -> Datefield

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323741 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

p.van.kuyen SSC Veteran Points: 230 More actions · Answer 2

First of all, thanks for the reply.

Without the DISTINCT it indeed returns the correct amount of records

p.van.kuyen SSC Veteran Points: 230 More actions · Answer 3

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

Lowell SSC Guru Points: 323741 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

p.van.kuyen SSC Veteran Points: 230 More actions · Answer 5

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