DATEADD(d, DATEDIFF(d, 0, time_stamp), 0)

quote:

---

Still a slight problem in that now all my dates are text and not dates so i can't sort them into order. I now need to convert the conversion back to a date type!

Hence why called this topic "confusion"

My table holds logs of every member that logged into my site and when. You can log in more than once a day so time is relevent. I just want to output a total for each day, newest date first working back in time.

There must be an easier way?!!?!?

---

quote:

---

Actually there is. It's something I found out recently and been using it ever since.

select convert(datetime,cast(cast(date_col as float) as int)),
count(id_col)
from my_table with (nolock)
group by convert(datetime,cast(cast(date_col as float) as int))

The idea is this:
SQL holds the datetime fields as decimal (actually two 4 bytes integers) where the part left to the dot represents number of days since 1900-01-01 and the right part holds milliseconds since midnight. to group by day you have to get rid of the time part (what's right to the dot).

It works much faster than convert(varchar(10),date_field,120) and gives accurate results. It will also leave you with datetime fields. test it and see for yourself.

---

DECLARE @olddate datetime
SET @olddate = '18990101 10:00:00'

SELECT CONVERT(datetime, CAST(CAST(@olddate AS float) AS int)) AS dt
UNION ALL
SELECT DATEADD(d, DATEDIFF(d, 0, @olddate), 0)

dt                                                     
------------------------------------------------------ 
1899-01-02 00:00:00.000
1899-01-01 00:00:00.000

quote:

---

quote:

---

Actually there is. It's something I found out recently and been using it ever since.

select convert(datetime,cast(cast(date_col as float) as int)),
count(id_col)
from my_table with (nolock)
group by convert(datetime,cast(cast(date_col as float) as int))

The idea is this:
SQL holds the datetime fields as decimal (actually two 4 bytes integers) where the part left to the dot represents number of days since 1900-01-01 and the right part holds milliseconds since midnight. to group by day you have to get rid of the time part (what's right to the dot).

It works much faster than convert(varchar(10),date_field,120) and gives accurate results. It will also leave you with datetime fields. test it and see for yourself.

---

Arnold's expression is still better:

DATEADD(d, DATEDIFF(d, 0, time_stamp), 0)

It doesn't CONVERT anything. In the case of CONVERTing to float, float is an approximate value, and if you need to maintain a date to a certain precision you won't be able to do so with a float.

---

quote:

---

I assumed we would not be needing dates before 1900-01-01.

---

SELECT dt, CONVERT(datetime,CAST(dt AS float))
FROM (SELECT CONVERT(datetime, '1900-01-02 23:59:59.997') AS dt) a

quote:

---

First of all, since we were talking about a date that holds the datetime user logged into that site, I assumed we would not be needing dates before 1900-01-01. :-)

Second, I was talking about dealing with dates already stored on SQL...

Check it guys, it is worth the effort.

---

OK, in the meantime, I'd like to ask that you check something on SQL Team yourself.

Search the forums for '1-1-1900' or 'empty date' and read the posts. Many people mistakenly assume that passing an empty string is the same as a NULL date value. There are probably over 100 posts on how to fix that. Therefore, no only can you "assume" that there ARE dates in existing data in the range mentioned, you can guarantee it.

No one was criticizing your formula. But there are other considerations besides pure performance too, and if precision is lost in conversion, it can compromise the accuracy of the results being returned. If it can be done without conversion, without loss of precision, then it should be done that way.