13
Views
8
Comments
Solved
How do I retrieve the data that has the nearest date to the current date?
Question

Hi Community,

How can I write this SQL query to retrieve a ticket that has the closest date to the current date? 

There might be multiple ticket with similar mobile number but I would want it to be link with the one closes to the current date.

Please advice... the CURDATE() is not working.


mvp_badge
MVP
Rank: #89
Solution

Hi Alvin,

In terms of SQL server I would have implemented as shown below...

SELECT Top 1 {Ticket}.[TicketNumber]
FROM {Ticket}
WHERE {Ticket}.[ContactNo] = @MobileNumber
      AND {Ticket}.[CreatedDate] <= CONVERT(date, getdate())
ORDER BY {Ticket}.[CreatedDate] DESC


Hope this helps you!


Regards,

Benjith Sam

Rank: #112

Hello Alvin,

Can you please try with GETDATE() function instead?


Hope that this helps you!


Kind regards,

Rui Barradas

Rank: #112

Hello Alvin,

That's a different problem :)

It is related with your DATEDIFF() function.

The syntax for this function is DATEDIFF(interval, date1, date2) and you are passing only 2 arguments instead of 3. It seems that you're missing the interval argument.


You can check this documentation: https://www.w3schools.com/sql/func_sqlserver_datediff.asp


Hope that this helps you!


Kind regards,

Rui Barradas

Rank: #93

Hi Alvin,


In Oracle (I assume you're using Oracle because of the LIMIT syntax instead of the TOP in SQL Server), you can just subtract dates so you can just set:


ORDER BY ABS({Ticket}.[CreatedDate] - GETDATE())


Hope it helps.


Cheers,

João

mvp_badge
MVP
Rank: #89
Solution

Hi Alvin,

In terms of SQL server I would have implemented as shown below...

SELECT Top 1 {Ticket}.[TicketNumber]
FROM {Ticket}
WHERE {Ticket}.[ContactNo] = @MobileNumber
      AND {Ticket}.[CreatedDate] <= CONVERT(date, getdate())
ORDER BY {Ticket}.[CreatedDate] DESC


Hope this helps you!


Regards,

Benjith Sam

Rank: #5201

Thanks everyone for your help :)