CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Mohammad hossein
Mohammad hossein

Reputation: 255

how select from table where datetime=date

i have payment table and one of columns is p_craetedate type of p_createdate is DateTime

CREATE TABLE [dbo].[tbl_Payments](
    [p_id] [bigint] IDENTITY(100000,1) NOT NULL,
    [p_amount] [int] NOT NULL,
    [p_status] [tinyint] NOT NULL,
    [p_createdate] [datetime] NOT NULL,
    [p_creditor] [int] NOT NULL,
    [p_debtor] [int] NOT NULL,
    [p_type] [int] NOT NULL,
 CONSTRAINT [PK_tbl_Payments] PRIMARY KEY CLUSTERED )

i want get rows that created at '2013-07-16'

Upvotes: 0

Views: 575

Answers (3)

No Idea For Name
No Idea For Name

Reputation: 11607

SELECT (list of fields)
FROM dbo.YourTable
WHERE dateValue >= DATEADD(dd, DATEDIFF(dd,0,'2013-07-16 00:00'), 0) 
AND dateValue < DATEADD(dd, DATEDIFF(dd,0,'2013-07-17 00:00'), 1)

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33391

You can use queries from some previous answer, but read about sargable first. 

SELECT * FROM tbl_Payments
WHERE p_createdate >= '20130716'
    AND p_createdate < '20130717'

Upvotes: 1

marc_s
marc_s

Reputation: 755541

Use the DATE datatype present in SQL Server 2008 and newer:

SELECT (list of columns)
FROM dbo.tbl_Payments
WHERE CAST(p_createddate AS DATE) = '20130716'

The DATE datatype contains only the date - no time portion - therefore comparing to 20130716 in the ISO-8601 format (which works for any language/regional settings of your SQL Server) will give you all the rows that where created on the 16th of July, 2013 - no matter what time of day.

Upvotes: 3

Related Questions