Query with sql datepart

Question

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?

N. Warfield · Accepted Answer · 2011-06-29T20:06:50.377

1

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)

edited Jun 29 '11 at 20:06

answered Jun 29 '11 at 19:48

N. Warfield

346
2
14

i just need the maximum serial number of a day..i tried with your approach...not working..any suggestion? – sohel14_cse_ju Jun 29 '11 at 20:01
Try it now. It works, I just made a temp table locally and tried it. The query previously only worked per month. – N. Warfield Jun 29 '11 at 20:07
yes..it works for me....nice query..100% perfect......i must invite you for a coffee... – sohel14_cse_ju Jun 29 '11 at 20:19
Please take a look at this query : it's another better solution : select MAX(sl_no) as sl_no from Appointment where CAST(appointment_date_time as DATE)='2011-06-30' – sohel14_cse_ju Jun 30 '11 at 07:14

score 0 · Answer 2 · edited May 23 '17 at 10:24

0

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?

edited May 23 '17 at 10:24

Community

1
1

answered Jun 29 '11 at 19:43

RMalke

4,048
29
42

Sai's query seem a better way – RMalke Jun 29 '11 at 19:44
i am getting error after the where clause..and i want the serial_number as select result..please keep that in mind – sohel14_cse_ju Jun 29 '11 at 19:52

Query with sql datepart

2 Answers2