Date time rounding - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date time rounding

Author

Topic

ringworm
Starting Member

6 Posts

Posted - 2006-07-18 : 04:04:45

Hi,

I am comparing dates , in vb I am using the following format "dd/mmm/yyyy HH:NN:SS"

But it the DB the dates have been saved with milliseconds. So they never match. I have used Convert(varchar, date_time,20) which rounds down the time, but the VB is rounding normally which courses a lot of mismatches.

So how can I round the db date time to seconds(the software is already in use so I can not update the VB)?

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-18 : 04:15:55

modify the sproc or function that does the retrieval

--------------------
keeping it simple...

ringworm
Starting Member

6 Posts

Posted - 2006-07-18 : 04:43:32

that really was not helpfull, I think I know I need to modfiy it!!!!! I want to know if there is a command to round to secs instead of going to milisecs.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-18 : 05:17:40

quote:
Originally posted by ringworm

that really was not helpfull, I think I know I need to modfiy it!!!!! I want to know if there is a command to round to secs instead of going to milisecs.

Ringworm, did you know what I just did? I opened Books Online and searched for how to convert datetime, and do you also know what I found? The CONVERT and CAST functions.

SELECT CONVERT(SMALLDATETIME, GETDATE(), 120)
SELECT CAST(GETDATE() AS SMALLDATETIME)

But that only works up to year 2079 something. But maybe that is enough for you?

Peter Larsson
Helsingborg, Sweden

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-07-18 : 05:30:17

Well, this seems to work - hth:


set nocount on
declare @DT Datetime
Set @DT = getdate()

select	@DT as originaldate,
		dateadd(ms,-1*(datepart(ms,@DT)),@DT) as RoundedMSDate

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-18 : 06:32:29

quote:
Originally posted by Wanderer

Well, this seems to work

It does?

My understanding was that rounding was needed, rather than rounding down. Anyway, here's a modified version to clarify the difference...

set nocount on
declare @DT Datetime
Set @DT = getdate()

select @DT as originaldate,
       dateadd(ms, -datepart(ms,@DT), @DT) as RoundedDownMSDate,
       dateadd(ms, 1000-datepart(ms,@DT), @DT) as RoundedUpMSDate,
       dateadd(ms, round(datepart(ms,@DT), -3) - datepart(ms,@DT), @DT) as RoundedMSDate

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-19 : 06:18:26

quote:
Originally posted by ringworm

I am comparing dates , in vb I am using the following format "dd/mmm/yyyy HH:NN:SS"

Didn't see the mmm until now. This is VB format of DateTime information.
I think he is comparing SQL varchar against VB String. I don't believe he is comparing dates.

Ringworm, use this code to accomplish your task

DECLARE @Now DATETIME

SELECT	@Now = DATEADD(second, .5, GETDATE())

SELECT	RIGHT('0' + CAST(DAY(@Now) AS VARCHAR(2)), 2) +
	'/' +
	LEFT(DATENAME(MONTH, @Now), 3) +
	'/' +
	CONVERT(VARCHAR, YEAR(@Now)) + 
	' ' + 
	CONVERT(VARCHAR, @Now, 108)

Output is 19/Jul/2006 13:18:26.

Peter Larsson
Helsingborg, Sweden

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-07-20 : 04:48:02

quote:
Originally posted by RyanRandall

quote:
Originally posted by Wanderer

Well, this seems to work
It does?

My understanding was that rounding was needed, rather than rounding down. Anyway, here's a modified version to clarify the difference...

My bad - I read to quickly, and missed the piece where he said VB was rounding normally

quote:
Originally posted by RyanRandall
set nocount on
declare @DT Datetime
Set @DT = getdate()

select @DT as originaldate,
dateadd(ms, -datepart(ms,@DT), @DT) as RoundedDownMSDate,
dateadd(ms, 1000-datepart(ms,@DT), @DT) as RoundedUpMSDate,
dateadd(ms, round(datepart(ms,@DT), -3) - datepart(ms,@DT), @DT) as RoundedMSDate

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources