Click here to Skip to main content
15,867,888 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have 3 columns in Table TransactionMaster in sql server

1)transaction_amount
2)Card_No
3)transaction_date-- datetime datatype


So, I want to fetch SUM of transaction_amount where Card_No=' 123' and transaction_date= todays date.<----- excluding time
IN SQL

HELP ME...!!
Posted
Comments
chaau 3-Feb-14 1:47am    
what version of SQL Server it is? In SQL Server 2008 you do it like this: CAST(todaysDate AS DATE)

Try this solution:

SQL
create table #temp
(
transaction_amount decimal(5,2),
Card_No nvarchar(50),
transaction_date dateTime
)


Insert Into  #temp values(100.23,'123', '2014-02-03 12:22:36.140'),
                        (120.23,'123', '2014-02-02 12:22:36.140'),
                        (140.23,'123', '2014-02-03 12:25:36.140')


SELECT SUM(transaction_amount) as SUM from #temp where Card_No ='123'
            AND convert(varchar(12), transaction_date, 105) = convert(varchar(12), getdate(), 105)

drop table #temp
 
Share this answer
 
Try:
CONVERT(DATE, MyDateColumn)

For pre-SQL2008, then:
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, MyDateColumn))
 
Share this answer
 
With the help of C# i have written this below code for getting only date(todya's date):

string date= DateTime.Now.ToString("yyyy-MM-dd");

and then passed today's date to the following query...

"select sum(transaction_amt) as sum from TransactionMaster where card_no='123' and CONVERT(varchar,trnasaction_Date,120) LIKE '"+date+"%'".


Also thanks to all of you for Helping me....Thanks a LOT..!!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900