CODEWITHSUNDEEP
sql-server-2008
sohel14_cse_ju
sohel14_cse_ju

Reputation: 2521

Query with sql datepart

I have a table with two column as : 

serial_number
1
2
3

dateOfAppoinement
2011-06-30 00:39:04.130
2011-06-30 00:40:01.130
2011-06-30 00:49:04.130

I want to get the highest serial_number of a day. I have to avoid the time part. I'm just using the date part.

Can anyone tell me how can I do this?

Upvotes: 0

Views: 260

Answers (2)

N. Warfield
N. Warfield

Reputation: 346

I updated my answer to use Convert like snkmchnb suggested. However in SQL Server 2008 there is a DATE datatype that is just the date portion of the year so you don't have to specify the 120 code. I tested this and it works perfectly and the SQL is pretty straight forward.

SELECT
    MAX(serial_number),
    CONVERT(DATE, dateOfAppointment) as [Day]
FROM
    #TempSerialsByDate
GROUP BY
    CONVERT(DATE, dateOfAppointment)

Upvotes: 1

RMalke
RMalke

Reputation: 4094

I think this select solves your problem, can't figure if it is the best way:

select dateadd(dd,0, datediff(dd,0, dateOfAppoinement ))
from your_table where serial_number = (select max(serial_number) form your_table)

How truncate date in sql server can be found here: How can I truncate a datetime in SQL Server?

Upvotes: 0

Related Questions