Comparing datetimes only by the *day* (not time)

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

SQL Server Development (2000)

Comparing datetimes only by the *day* (not time)

Author

Topic

nmg196
Yak Posting Veteran

70 Posts

Posted - 2003-05-13 : 12:22:21

Hi,

I'm logging some stuff (by date) and I want to only create a new row if there isn't already a row in the log table for *today*. (ie, one row per day). If a row exists for today, then I need to increase a counter on the existing row, otherwise I need to insert a new row to represent today's date.

How can I do a compare GetDate() with something else and ignore all the time information? ie, reset everything that isn't the day/month or year to 0 so that my comparisons work? I'd like to keep the datetime column in the database as a real datetime column, but with the time always at 00:00.00.

Sorry if the above isn't very clear, but I think you can see my problem... comparing GetDate with 2003-05-13 will fail most of the time as the time will be different.

Thanks,

Nick...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-13 : 12:39:13

Arnold Fribble has a different (=better!) formula somewhere on this site, but I usually just use:

convert(date, convert(int, @GetDate()))

but i think if you are 3 ms away from midnight, it puts you on the next day. hopefully that will be OK.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 12:50:28

How about:


CREATE TABLE bk_Log (Col1 datetime)
GO
INSERT INTO bk_Log(Col1) SELECT '05/12/2003'
GO

IF (SELECT CONVERT(varchar(10),MAX(Col1),112) FROM bk_Log) <> (SELECT CONVERT(varchar(10),GETDATE(),112))
	BEGIN
		INSERT INTO bk_Log(Col1) SELECT CONVERT(varchar(10),GETDATE(),112)
	END
GO
SELECT col1 FROM bk_Log
GO
DROP TABLE bk_LOG
GO

Brett

8-)

nmg196
Yak Posting Veteran

70 Posts

Posted - 2003-05-13 : 13:08:15

quote:

Arnold Fribble has a different (=better!) formula somewhere on this site, but I usually just use:

convert(date, convert(int, @GetDate()))

but i think if you are 3 ms away from midnight, it puts you on the next day. hopefully that will be OK.

- Jeff

Nope - it just *always* puts you on the next day for some reason!:

select GetDate()
= 2003-05-13 18:00:54.920

select convert(datetime, convert(int, GetDate()))
= 2003-05-14 00:00:00.000

But the following user defined function given to me by a co-worker has done the trick:

/****** Object:  User Defined Function dbo.x_get_start_of    Script Date: 2002-05-14 10:46:20 ******/
CREATE FUNCTION x_get_start_of (@IntervalType varchar(5), @theDate datetime)
RETURNS DateTime
AS
BEGIN
	DECLARE @tempDate datetime
	SELECT @tempDate = 
	CASE @IntervalType
		WHEN 'week' THEN
			DATEADD(dd,-1 * (DATEPART(dw,CONVERT(DateTime, (CAST(DatePart(yyyy, @theDate) AS varchar) + '-' + CAST(DatePart(mm, @theDate) AS varchar) + '-' + CAST(DatePart(dd, @theDate) AS varchar)), 120))-1),CONVERT(DateTime, (CAST(DatePart(yyyy, @theDate) AS varchar) + '-' + CAST(DATEPART(mm, @theDate) AS varchar) + '-' + CAST(DATEPART(dd, @theDate) AS varchar)), 120))
		WHEN 'month' THEN
			CONVERT(DateTime, (CAST(DatePart(yyyy, @theDate) AS varchar) + '-' + CAST(DatePart(mm, @theDate) AS varchar) + '-01'), 120)
		ELSE
			CONVERT(DateTime, (CAST(DatePart(yyyy, @theDate) AS varchar) 
				+ '-' + CAST(DatePart(mm, @theDate) AS varchar) 
				+ '-' + CAST(DatePart(dd, @theDate) AS varchar)), 120)
	END
	RETURN @tempDate
END

select dbo.x_get_start_of('day', getdate())
= 2003-05-13 00:00:00.000

It can even do weeks and months too :)

Nick...

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-13 : 13:26:36

select dateadd(dd,datediff(dd,0,getdate()),0)

Although, I've failed out of the Fribble School of Dates several times ...

Jay White
{0}

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-13 : 14:59:43

select dateadd(dd,0,datediff(dd,0,getdate()))

works too

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources