Date Comparisons - 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

SQL Server Development (2000)

Date Comparisons

Author

Topic

Harwinder
Starting Member

2 Posts

Posted - 2004-10-06 : 11:49:29

I have have two date fields in a table, date1 & date 2. I know that in these fields, both the date and time is stored.

I want to perform comparison functions (>, >=, <, <=, =) on these two dates BUT I waould like the time part to be ignored in the comparison. A simple date1 < date2 will compare the times as well as the dates and I do not want to do this. Is there a simple method here?

I know the convulated method of doing this by using the CONVERT functions etc, but that seems over the top.

Also, is there any simple method to compare just the TIME parts of the dates? I know this may seem wierd (why would someone want to compare time where the dates are different!!) but it applies to something specific I am doing.

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-06 : 12:06:44

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 datetime, Col2 datetime)
GO

INSERT INTO myTable99(Col1,Col2)
SELECT '1/1/2004 1:00:00','1/2/2004 2:00:00' UNION ALL
SELECT '1/1/2004 1:00:00','1/3/2004 3:00:00' UNION ALL
SELECT '1/1/2004 1:00:00','1/4/2004 4:00:00' UNION ALL
SELECT '1/1/2004 1:00:00','1/5/2004 5:00:00'
GO

SELECT DATEDIFF(dd,Col1,Col2) AS DayDifferent
, DATEDIFF(hh,Col1,Col2) AS TrueHoursDifferent
, DATEDIFF(hh,CONVERT(varchar(10),Col1,108),CONVERT(varchar(10),Col2,108)) AS HoursDifferent
FROM myTable99
GO

SET NOCOUNT ON
DROP TABLE myTable99
GO

[/code]

Brett

8-)

Harwinder
Starting Member

2 Posts

Posted - 2004-10-06 : 16:25:53

Thanks for the help Brett

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources