Parsing smalldatetime - 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)

Parsing smalldatetime

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2002-06-07 : 10:23:31

Hello-

I have a field in my DB that stores date/time as smalldatetime. I am trying to write a report on an ASP page that will allow a user to click the time/date of an order and see all of the other orders that were placed that day.

The current method isn't working because it is trying to find all of the orders that occured on the same day at the same time. I need a way to parse my SQL statement so it will just look at the day, and not the time stamp.

'tblOrders.orderDate=@orderDate' is the relavent part of the SQL statement. Both orderDate and @orderDate are smalldatetime. Any help?

Thanks!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-07 : 10:32:06

day(tblOrders.orderDate) = day(@orderDate)

<O>

Nick
Posting Yak Master

155 Posts

Posted - 2002-06-07 : 10:33:18

Well that we easy enough.. Thanks!

solart
Posting Yak Master

148 Posts

Posted - 2002-06-17 : 12:10:59

The following does not seem to me that it is technically correct.

day(tblOrders.orderDate) = day(@orderDate)

My interpretation of the function "day" is that it returns the "day" part of the datetime being referenced. Therefore if the tblOrders.orderDate is for a month which is different from the @orderDate month, but the day of month is the same, then it seems to me that the query response would include tblOrders for any month which occurs on the "day".

Other views, opinions?

solart

<

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-17 : 12:19:27

oooooooooooooooooops

Yeah, that could be a problem. However, it's easily fixed using DateDiff:

WHERE DateDiff(day, tblOrders.orderDate, @orderDate)=0

That'll do the trick. More on DateDiff in Books Online if you're interested.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-17 : 12:34:22

My first mistake . . . I guess it was inevitable.

Thanks solart . . .

<O>

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-06-18 : 04:31:08

Also inevitable is me saying -- If you want any indexes on orderDate to be usable, you need something like this:
tblOrders.orderDate >= @orderDate AND tblOrders.orderDate < DATEADD(day, 1, @orderDate)

With this sort of thing if you need to move the parameter to midnight:
SET @orderDate = DATEADD(day, DATEDIFF(day, '1900-01-01', @orderDateTime), '1900-01-01')

MakeYourDaddyProud

184 Posts

Posted - 2002-06-18 : 07:52:02

Hi,

you can substitute the
multi-month "day(tblOrders.orderDate) = day(@orderDate)"
and the calculation intensive "WHERE DateDiff(day, tblOrders.orderDate, @orderDate)=0"

for a single line that looks just at each DayMonthYear

"where convert(varchar(10),tblOrders.orderDate,101) = convert(varchar(10),@orderdate1,101)"

I agree with Arnold using key approach for a faster indexed solution too but that depends on your requirement and whether you want to use a GROUP BY within your query.

My quiddies worth

Dan
www.danielsmall.com IT Factoring

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-06-18 : 09:07:58

Certainly, one has to be a little careful, viz:


CREATE TABLE DateTest (id int PRIMARY KEY, dt smalldatetime not null)

DECLARE @i int, @curdt smalldatetime
SET @i = 0
SET @curdt = '2000-01-01'

SET NOCOUNT ON
WHILE @i < 2000
BEGIN
  SET @curdt = DATEADD(s, RAND()*86400, @curdt)
  INSERT INTO DateTest VALUES (@i, @curdt)
  SET @i = @i + 1
END
SET NOCOUNT OFF


SET STATISTICS TIME ON

SELECT COUNT(*)
FROM DateTest D1
CROSS JOIN DateTest D2
WHERE CONVERT(varchar(10), D1.dt, 101) = CONVERT(varchar(10), D2.dt, 101)


SELECT COUNT(*)
FROM DateTest D1
CROSS JOIN DateTest D2
WHERE DATEADD(day, DATEDIFF(day, '1900-01-01', D1.dt), '1900-01-01') =
  DATEADD(day, DATEDIFF(day, '1900-01-01', D2.dt), '1900-01-01')

-- but why bother with a DATEADD here?
SELECT COUNT(*)
FROM DateTest D1
CROSS JOIN DateTest D2
WHERE DATEDIFF(day, '1900-01-01', D1.dt) = DATEDIFF(day, '1900-01-01', D2.dt)

SELECT COUNT(*)
FROM DateTest D1
CROSS JOIN DateTest D2
WHERE DATEDIFF(day, D1.dt, D2.dt) = 0

Clearly the last one is slower, but that's because it has to perform the DATEDIFF on each pair as a loop join, rather than precalculate and then hash and probe. However, DATEADD and DATEDIFF themselves are significantly faster than CONVERT for this, as shown by the second query and third queries.

Edited by - Arnold Fribble on 06/18/2002 09:26:58

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources