63

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

DoNotArrestMe
  • 1,285
  • 1
  • 9
  • 20
  • http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype?rq=1 – Pellared Feb 03 '14 at 06:48

5 Answers5

125

Simply cast your timestamp AS DATE, like this:

SELECT CAST(tstamp AS DATE)

SQLFiddle Demo

In other words, your statement would look like this:

SELECT SUM(transaction_amount)
FROM mytable
WHERE Card_No='123'
  AND CAST(transaction_date AS DATE) = target_date

What is nice about CAST is that it works exactly the same on most SQL engines (SQL Server, PostgreSQL, MySQL), and is much easier to remember how to use it. Methods using CONVERT() or TO_DATE() are specific to each SQL engine and make your code non-portable.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • 1
    This doesn't work in SQL Server 2005 or earlier as the *date* data type was added in SQL Server 2008. – Kenny Evitt Oct 28 '16 at 19:40
  • 1
    Extended support for SQL Server 2005 [ended on April 12, 2016](https://www.microsoft.com/en-us/cloud-platform/sql-server-2005). If you are still running SQL Server 2005, you will no longer receive security updates. Upgrade to SQL Server 2014, SQL Server 2016, or Azure SQL Database. – mvp Nov 08 '16 at 19:30
7

You can use 

select * 
from transaction 
where (Card_No='123') and (transaction_date = convert(varchar(10),getdate(),101))
Milen
  • 8,697
  • 7
  • 43
  • 57
Milan Raval
  • 1,880
  • 1
  • 16
  • 33
  • So here you should consider that the RHS side of equal to will return date in DD/MM/YYYY format. You can refer to more format options in this link: http://msdn.microsoft.com/en-us/library/ms187928(SQL.105).aspx – Milan Raval Feb 03 '14 at 06:55
  • CAST() didn't work for me using SSRS but convert did. Thanks! – BrianLegg Jun 09 '15 at 13:36
2

use the following 

select sum(transaction_amount) from TransactionMaste
where Card_No = '123' and transaction_date = CONVERT(VARCHAR(10),GETDATE(),111)

or the following 

select sum(transaction_amount) from TransactionMaste
where Card_No = '123' and transaction_date = CONVERT(VARCHAR(10), GETDATE(), 120)
Developerzzz
  • 1,123
  • 1
  • 11
  • 26
2

Use Getdate()

 select sum(transaction_amount) from TransactionMaster
 where Card_No=' 123' and transaction_date =convert(varchar(10), getdate(), 102)
Nagaraj S
  • 13,316
  • 6
  • 32
  • 53
1

Try this:

SELECT SUM(transaction_amount) FROM TransactionMaster WHERE Card_No ='123' AND CONVERT(VARCHAR(10),GETDATE(),111)

The GETDATE() function returns the current date and time from the SQL Server.

Ajay
  • 6,418
  • 18
  • 79
  • 130