0

How can I modify this query to >>> @Date1 is a date (date only no time)

SELECT DISTINCT TOP (200) dbo.tb_ShowTime.VenueId, DATEADD(Hour, DATEDIFF(Hour, 0, dbo.tb_ShowTime.StartDateTime), 0) AS StartDateTime
FROM         tb_ShowTime
WHERE     (dbo.tb_ShowTime.IsDeleted = 0) AND (dbo.tb_ShowTime.StartDateTime BETWEEN @Date1 AND @Date2)
UNION
SELECT DISTINCT TOP (200) dbo.tb_EventSectionTime.VenueId, DATEADD(Hour, DATEDIFF(Hour, 0, dbo.tb_EventSectionTime.EventTime), 0) AS StartDateTime
FROM         tb_EventSectionTime
WHERE     (dbo.tb_EventSectionTime.IsDeleted = 0) AND (dbo.tb_EventSectionTime.EventTime BETWEEN @Date1 AND @Date2)
ORDER BY StartDateTime
Rami Alshareef
  • 7,015
  • 12
  • 47
  • 75
SOF User
  • 7,590
  • 22
  • 75
  • 121
  • 1
    FYI: DISTINCT with UNION is superfluous because UNION removes duplicates. Also, the TOP 200 in each is a random 200 rows. You'd need a derived table to read TOP 200..ORDER BY... before the UNION and the outer ORDER BY – gbn Jul 31 '11 at 21:47
  • What do you mean? A simple `EventTime> @Date1`? What does >>> mean in your question? – gbn Jul 31 '11 at 21:51
  • I read it that the OP needs to change the query so that @Date1 is a date excluding the time, assuming that its currently a `datetime`. – Chris Diver Jul 31 '11 at 21:55

3 Answers3

1

Not sure which dbms you're using, but in Sybase you can convert to string then back to datetime, to lose time portion. Sure you can do something similar with others.

convert(datetime,convert(varchar,@Date1,109))
Vinny Roe
  • 903
  • 4
  • 8
0

You can use these functions
Day(@date1) return day of the date
Month(@date1) return month of the date
year(@date1)return year of the date

Rami Alshareef
  • 7,015
  • 12
  • 47
  • 75
0

When I came accross this problem I used CAST(@Date1 as DATE) That is in SQL-Server. It truncates time piece to 00:00:00. If you are using a different DBMS try to find casting function for the relevant engine.

Oybek
  • 7,016
  • 5
  • 29
  • 49