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

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, 2012 at 01:58 PM in Default

avatar image

cdurham
230 22 22 26

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

avatar image

Tim
40.4k 39 84 166

@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.

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:

x80
x4

asked: May 17, 2012 at 01:58 PM

Seen: 2021 times

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

Copyright 2016 Redgate Software. Privacy Policy