Using query to retreive records which their createdate is a special date?

Question

I have a table that has four columns. One of them is named "CreateDate" and it's datatype is "DateTime".

Now, what would be a true T-SQL which retreives records that their CreateDate is for example

"2010-02-10" ?

Answer 1

If you wish to select all records with CreateDate on a specific date you could use something like

SELECT *
FROM YourTable
WHERE DATEADD(dd, DATEDIFF(dd,0,CreateDate), 0) = '2010-02-10'

or

DECLARE @Date DATETIME

SELECT @Date = '01 Feb 2010'

SELECT  *
FROM    YourTable
WHERE   CreateDate >= @Date
AND     CreateDate < @Date + 1

EDIT

If you wish to change the display format of the date, from SQL Server Date Formats

You could try

DECLARE @YourTable TABLE(
        CreateDate DATETIME
)

INSERT INTO @YourTable SELECT '01 Feb 2010'
INSERT INTO @YourTable SELECT GETDATE()


SELECT  *,
        CONVERT(VARCHAR(10), CreateDate, 120) DateValue
FROM @YourTable