June 24, 2008 at 5:56 am
Hi all,
I'm having a problem with a date in an Access table that's linked to SQL. I have a default value of GETDATE() set on a couple of fields. There's some program logic somewhere that compares another field to this one, and errors out if there is a difference.
The problem seems to be that Access can handle dates up to the second and rounds off any milliseconds, but SQL does not round those off. It's causing a problem when a date - 6/24/08 07:47:40.793, for example - is rounded up to 6/24/08 07:47:41 and suddenly these two fields are no longer equal. At least in the short term, I'd like to be able to round a GETDATE() result to the second. Is this possible?
Thanks!
June 24, 2008 at 6:20 am
try
convert(datetime, convert(varchar, getdate(), 120), 120)
Kev
June 24, 2008 at 6:26 am
Thanks for the response.
I have tried that one, but it doesn't round the date - it just drops the millisecond value.
June 24, 2008 at 6:42 am
but if you use this as your default than all default dates will be stored with 00 milliseconds..
do you catch my drift??
Kev
June 24, 2008 at 6:45 am
Ah, I see what you're getting at. I've been racking my brain trying to figure out a way to fix all the dates in there from the past week that have milliseconds that I forgot what my original goal was. 🙂
Thanks for setting me straight!
June 24, 2008 at 6:52 am
so are you going to go over the exisitng records and replace the dates?
June 24, 2008 at 7:01 am
Probably so. It's a week's worth of data but the tables on which I was using GETDATE() as a default are few and far between, and I don't think they're updated all that frequently. Shouldn't be more than a hundred edits.
June 24, 2008 at 7:26 am
UPDATE Table1
SET Col1 = cast(convert(varchar(30), Col1, 120) AS datetime)
N 56°04'39.16"
E 12°55'05.25"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply