datetime manipulation in SQL 2

[FutureTech]

I have records with datetime columns where the date portion is valid, but the time portion is incorrect. I want to correct the time portion for several records to read "11:59:59:990 PM" instead of the default "12:00:00:000 PM".

How can I do this in an update statement?

 

[hneal98]

something like the following:

convert(datetime,convert(char(10),DateField) + ' 11:59:59:990 PM')

Make sure you put the space between the date and the time.

 

[roccorocks]

Here you go, this is if you are using SQL Server 2000.
I just ran this with some of my data in the Query Analyzer.

USE yourDatabase
GO
SELECT DATEADD(millisecond, -009,yourcollumnname ) AS alias
FROM yourtable
GO

Results:

1899-12-30 20:14:42.990
1899-12-30 20:14:42.990
1899-12-30 20:14:42.990
2000-05-08 12:35:28.990

Hope this helps you!

Rocco

 

[roccorocks]

OOPS! I goofed. I see you wanted to UPDATE, my bad. Here you go:

USE yourDatabase
GO
Update yourTable set yourcolumn = DATEADD(millisecond, -009,yourcolumn )
FROM yourTable
GO

Rocco

 

[FutureTech]

Still working on it.

roccorocks, I need to update the entire time, not just the milleseconds.

hneal98, I got an arithmetic overflow when trying the convert, so I'm trying to resolve that.

My datetime column needs to remain a datetime column after the update.

My latest attempt is to begin the transaction, update each datepart separately, then commit the transaction. It just seems kinda awkward to do it this way, but it works. I'm thinking there must be a better, cleaner way.

I appreciate the suggestions so far. Thanks.

 

[hneal98]

Did you make sure you added the space like I suggested? That should work.

 

[hneal98]

Sorry, that last part where you have '59:990', it should be '59.990'. If you change that, it should work.

 

[roccorocks]

Future, if the time is 12:00:00.000 (as you have stated) and you subtract .009 from this (actually you should -010) this will give you 11:59:59:990 PM. Is this NOT what you want? Try it out.

Rocco

 

[FutureTech]

This is what I have in a datetime column:
prod_end_dt
12/31/2002 12:00:00.000 AM
12/31/2049 12:00:00.000 AM
12/31/2002 12:00:00.000 AM
12/31/2002 12:00:00.000 AM

This is what I want:
prod_end_dt
12/31/2002 11:59:59.990 PM
12/31/2049 11:59:59.990 PM
12/31/2002 11:59:59.990 PM
12/31/2002 11:59:59.990 PM

hneal98, I copied your code, corrected the colon to a period and still got the overflow.

roccorocks, when I subtracted the time, it actually changed the date, too (not good).

I'll try again tomorrow.
Thanks.

 

[roccorocks]

Hmm, try this then

USE sprint
GO

Update tbltest1 set datenew = DATEADD(second, -1,datenew )
FROM tbltest1

Update tbltest1 set datenew = DATEADD(day, 1,datenew)
from tbltest1
GO

This will not affect the time. Make sure to add a WHERE clause for each, so not to update all the info in your table. Sorry for the hassel.

Rocco

 

[hneal98]

Can you copy the code as you wrote it here so we can see what you are doing. I know it seems like a waste of time, but just humor me. I would like to check it out because that should work.

Thanks.

 

[roccorocks]

Here is a list of different arguments you can use with the DATEADD:

Year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond.

You will want to use Day and Millisecond as shown in the previous posts.

When I said "This will not affect the time" I meant the DATEADD(day, 1, datenew) will not affect the time!

Rocco

 

[hneal98]

The solution that Rockorocs put forth should work because you are esentially subtracting .009 milliseconds that should take you form 12:00:00.990 to 11:59:59.990. Give it a try. It think his would work better than my idea.

 

[JamesLean]

There is a much simpler solution to your problem:

update mytable
set mycol = mycol + '23:59:59.990'
where convert(varchar(10), mycol, 108) = '00:00:00'

Note I've added a WHERE clause to only update rows which have 0 time.

--James

 

[billchris]

Nice work, JamesLean. Very succinct.

“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal

 

[FutureTech]

Thanks for all the help. On Friday morning (6/27) I got laid off from my job. I didn't get to try all your suggestions, but I think I learned something from all of you anyway. Thanks, again.