Compare a date string to datetime in SQL Server?

问题:

In SQL Server I have a DATETIME column which includes a time element.

Example:

'14 AUG 2008 14:23:019'

What is the best method to only select the records for a particular day, ignoring the time part?

Example: (Not safe, as it does not match the time part and returns no rows)

DECLARE  @p_date DATETIME
SET      @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

SELECT *
FROM   table1
WHERE  column_datetime = @p_date

Note: Given this site is also about jotting down notes and techniques you pick up and then forget, I'm going to post my own answer to this question as DATETIME stuff in MSSQL is probably the topic I lookup most in SQLBOL.

---

Update Clarified example to be more specific.

---

Edit Sorry, But I've had to down-mod WRONG answers (answers that return wrong results).

@Jorrit: WHERE (date>'20080813' AND date<'20080815') will return the 13th and the 14th.

@wearejimbo: Close, but no cigar! badge awarded to you. You missed out records written at 14/08/2008 23:59:001 to 23:59:999 (i.e. Less than 1 second before midnight.)

回答1:

Technique 1:

 DECLARE @p_date DATETIME
 SET     @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

 SELECT  *
 FROM    table1
 WHERE   column_datetime >= @p_date
 AND     column_datetime < DATEADD(d, 1, @p_date)

The advantage of this is that it will use any index on 'column_datetime' if it exists.

回答2:

In SQL Server 2008, you could use the new DATE datatype

DECLARE @pDate DATE='2008-08-14'  

SELECT colA, colB
FROM table1
WHERE convert(date, colDateTime) = @pDate  

@Guy. I think you will find that this solution scales just fine. Have a look at the query execution plan of your original query.

And for mine:

回答3:

Just compare the year, month and day values.

Declare @DateToSearch DateTime
Set @DateToSearch = '14 AUG 2008'

SELECT * 
FROM table1
WHERE Year(column_datetime) = Year(@DateToSearch)
      AND Month(column_datetime) = Month(@DateToSearch)
      AND Day(column_datetime) = Day(@DateToSearch)

回答4:

Something like this?

SELECT  *
FROM    table1
WHERE   convert(varchar, column_datetime, 111) = '2008/08/14'

回答5:

Technique 2:

DECLARE @p_date DATETIME
SET     @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

SELECT  *
FROM    table1
WHERE   DATEDIFF( d, column_datetime, @p_date ) = 0

If the column_datetime field is not indexed, and is unlikely to be (or the index is unlikely to be used) then using DATEDIFF() is shorter.

回答6:

Good point about the index in the answer you accepted.

Still, if you really search only on specific DATE or DATE ranges often, then the best solution I found is to add another persisted computed column to your table which would only contain the DATE, and add index on this column:

ALTER TABLE "table1" 
    ADD "column_date" AS CONVERT(DATE, "column_datetime") PERSISTED

Add index on that column:

CREATE NONCLUSTERED INDEX "table1_column_date_nu_nci"
ON  "table1" ( "column_date" ASC )
GO

Then your search will be even faster:

DECLARE  @p_date DATE
SET      @p_date = CONVERT( DATE, '14 AUG 2008', 106 )

SELECT   *
FROM     table1
WHERE    column_date = @p_date

回答7:

I normally convert date-time to date and compare them, like these:

SELECT 'Same Date' WHERE CAST(getDate() as date) = cast('2/24/2012 2:23 PM' as date)

or

SELECT 'Same Date' WHERE DATEDIFF(dd, cast(getDate() as date), cast('2/24/2012 2:23 PM' as date)) = 0

回答8:

This function Cast(Floor(Cast(GetDate() As Float)) As DateTime) returns a datetime datatype with the time portion removed and could be used as so.

Select
*
Table1
Where
Cast(Floor(Cast(Column_DateTime As Float)) As DateTime) = '14-AUG-2008'

or

DECLARE  @p_date DATETIME
SET      @p_date = Cast('14 AUG 2008' as DateTime)

SELECT   *
FROM     table1
WHERE    Cast(Floor(Cast(column_datetime As Float)) As DateTime) = @p_date

回答9:

How to get the DATE portion of a DATETIME field in MS SQL Server:

One of the quickest and neatest ways to do this is using

DATEADD(dd, DATEDIFF( dd, 0, @DAY ), 0)

It avoids the CPU busting "convert the date into a string without the time and then converting it back again" logic.

It also does not expose the internal implementation that the "time portion is expressed as a fraction" of the date.

Get the date of the first day of the month

DATEADD(dd, DATEDIFF( dd, -1, GetDate() - DAY(GetDate()) ), 0)

Get the date rfom 1 year ago

DATEADD(m,-12,DATEADD(dd, DATEDIFF( dd, -1, GetDate() - DAY(GetDate()) ), 0))

回答10:

I know this isn't exactly how you want to do this, but it could be a start:

SELECT *
FROM (SELECT *, DATEPART(yy, column_dateTime) as Year, 
      DATEPART(mm, column_dateTime) as Month, 
      DATEPART(dd, column_dateTime) as Day 
      FROM table1)
WHERE Year = '2008'
AND Month = '8'
AND Day = '14'

回答11:

SELECT  *
FROM    table1
WHERE   CONVERT(varchar(10),columnDatetime,121) = 
        CONVERT(varchar(10),CONVERT('14 AUG 2008' ,smalldatetime),121)

This will convert the datatime and the string into varchars of the format "YYYY-MM-DD".

This is very ugly, but should work

回答12:

Date can be compared in sqlserver using string comparision: e.g.

DECLARE @strDate VARCHAR(15)
SET @strDate ='07-12-2010'


SELECT * FROM table
WHERE CONVERT(VARCHAR(15),dtInvoice, 112)>= CONVERT(VARCHAR(15),@strDate , 112)

回答13:

DECLARE @Dat

SELECT * 
FROM Jai
WHERE                                                                                                          
CONVERT(VARCHAR(2),DATEPART("dd",Date)) +'/'+                                                              
             CONVERT(VARCHAR(2),DATEPART("mm",Date)) +'/'+              
                     CONVERT(VARCHAR(4), DATEPART("yy",Date)) = @Dat

回答14:

The best way is to simply extract the date part using the SQL DATE() Function:

SELECT * 
FROM table1
WHERE DATE(column_datetime) = @p_date;

回答15:

SELECT * FROM tablename
WHERE CAST(FLOOR(CAST(column_datetime AS FLOAT))AS DATETIME) = '30 jan 2012'

回答16:

SELECT CONVERT(VARCHAR(2),DATEPART("dd",doj)) + 
    '/' + CONVERT(VARCHAR(2),DATEPART("mm",doj)) + 
    '/' + CONVERT(VARCHAR(4),DATEPART("yy",doj)) FROM emp

回答17:

There are many formats for date in SQL which are being specified. Refer https://msdn.microsoft.com/en-in/library/ms187928.aspx

Converting and comparing varchar column with selected dates.

Syntax:

SELECT * FROM tablename where CONVERT(datetime,columnname,103) 
    between '2016-03-01' and '2016-03-03'

In CONVERT(DATETIME,COLUMNNAME,103) "103" SPECIFIES THE DATE FORMAT as dd/mm/yyyy

回答18:

In sqlserver

DECLARE @p_date DATE

SELECT * 
FROM table1
WHERE column_dateTime=@p_date

In C# Pass the short string of date value using ToShortDateString() function. sample: DateVariable.ToShortDateString();