Problem with Select and GetDate()

Question

Post reply

Problem with Select and GetDate()

Jon Gunnarsson

SSC Journeyman

Points: 83
More actions
April 6, 2005 at 11:49 am

#185589

Hi all.
I am currently working on a project issuing support tickets for a.company.
When a ticket is created it gets a timestamp Getdate().
This timestamp has to be down to seconds.
I have an interface where I want to list all the tickets created on a given day.
So I run Select * from Tickets where TicketDate = GetDate()
This of course will return nothing due to minutes and seconds.
So my question is : How can I list out all the records created today?
Any help greatly appreciated.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Select * from dbo.Tickets where dateadd(d,0, datediff(d, 0, TicketDate)) = dateadd(d,0, datediff(d, 0, getdate()))

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

You might test it against this method, because the first one will never use and index seek to find the dates because of all the functions.

Declare @StartDate as datetime

Declare @EndDate as datetime

set @StartDate = dateadd(d, 0, DATEDIFF(d, 0, Getdate()))

set @EndDate = dateadd(ms, -3, dateadd(d, 1, @StartDate))

Select * from dbo.Tickets where TicketDate between @StartDate and @EndDate

Jon Gunnarsson SSC Journeyman Points: 83 More actions · Answer 3

Wow thanks alot .. would I be able to use this in a view ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

yes the first one will work just as is..

the 2nd one will need a little tweaking :

...

where TicketDate between

dateadd(d, 0, DATEDIFF(d, 0, Getdate()))

and --note the 1 in the 2nd parameter to add one day.

dateadd(d, 1, DATEDIFF(d, 0, Getdate()))

Jon Gunnarsson SSC Journeyman Points: 83 More actions · Answer 5

Again thanks alot.

I created a stored procedure that selects from a view so this works just like I wanted it to.

Where do i send the bubbly?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

I'll assume that bubbly is some kind of beer... and as I don't drink I'll politely decline the offer. You could send the thanks to Frank Kalis as he's the one who showed me this neat trick.