I need help in converting the hh:mm:ss to 00:00:00 or getting rid of the hh:mm:

Question

question

cdurham asked May 17, '12

I need help in converting the hh:mm:ss to 00:00:00 or getting rid of the hh:mm:

Hello all, When I run the following query: SELECT cashoutid, cashoutidbystore, sum(cashouttot_systemtotal) as systemtotal, SUM(actualtotal) AS actualtotal, netdiffrence, PaymentName, cashout_stopdate
FROM Details_CashoutsAndTotals WHERE cashouttot_systemtotal <> 0.00 and cast(cashout_stopdate as date) = '02/27/2012' GROUP by cashoutid, cashoutidbystore, netdiffrence, PaymentName, cashout_stopdate I get this:
![alt text][1]
Looking at the cashout_stopdate column, you will see that there are actual hrs, minutes, and seconds. I need to know if I can convert these to 00:00:00.000, or remove them somehow to where I only have 2012-02-27 date? I have to get rid of the hh:mm:ss in order for the sums to total correctly. any help would be appreciated. [1]: http://ask.sqlservercentral.com/storage/temp/201-needing-date-mod.jpg

datetime cast-convert

needing-date-mod.jpg (117.0 KiB)

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ · May 17, 2012 at 02:21 PM

what version of SQL Server are you working with?

0 ·

cdurham · May 17, 2012 at 02:23 PM

@Fatherjack I am using SQL Server 2008 R2.

0 ·

Fatherjack ♦♦ · May 17, 2012 at 02:30 PM

in that case use CONVERT(DATE, cashout_stopdate) as cashout_stopdate

0 ·

cdurham · May 17, 2012 at 02:32 PM

@FatherJack I tried this and it works. Thank you so much!!!!!

0 ·

Answer 1 · 2012-05-17T14:05:35Z

Tim answered May 17, '12

There are lots of posts on how to convert date time. Here are some excellent examples. [examples]( http://linesofcode.net/snippets/45)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cdurham · May 17, 2012 at 02:21 PM

@Tim Thank you so much!

0 ·

question