x
login about faq Site discussion (meta-askssc)

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

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.

more ▼

asked May 17 '12 at 01:58 PM in Default

cdurham gravatar image

cdurham
230 15 21 23

what version of SQL Server are you working with?

May 17 '12 at 02:21 PM Fatherjack ♦♦

@Fatherjack

I am using SQL Server 2008 R2.

May 17 '12 at 02:23 PM cdurham

in that case use

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

@FatherJack

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

May 17 '12 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 '12 at 02:05 PM

Tim gravatar image

Tim
31.5k 20 31 116

@Tim

Thank you so much!

May 17 '12 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.

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



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x84
x22

asked: May 17 '12 at 01:58 PM

Seen: 666 times

Last Updated: May 17 '12 at 02:32 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.