x

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<br>
 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
more ▼

asked May 17, 2012 at 01:58 PM in Default

cdurham gravatar image

cdurham
230 22 22 24

what version of SQL Server are you working with?
May 17, 2012 at 02:21 PM Fatherjack ♦♦

@Fatherjack

I am using SQL Server 2008 R2.
May 17, 2012 at 02:23 PM cdurham

in that case use

CONVERT(DATE, cashout_stopdate) as cashout_stopdate
May 17, 2012 at 02:30 PM Fatherjack ♦♦

@FatherJack

I tried this and it works. Thank you so much!!!!!
May 17, 2012 at 02:32 PM cdurham
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
There are lots of posts on how to convert date time. Here are some excellent examples. examples
more ▼

answered May 17, 2012 at 02:05 PM

Tim gravatar image

Tim
36.4k 38 41 139

@Tim

Thank you so much!
May 17, 2012 at 02:21 PM cdurham
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x94
x29

asked: May 17, 2012 at 01:58 PM

Seen: 1424 times

Last Updated: May 17, 2012 at 02:32 PM