Compare a date string to datetime in SQL Server

Question

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 gt  20080813  AND date lt  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

User · Accepted Answer

Technique 1    DECLARE  p date DATETIME  SET      p date   CONVERT  DATETIME   14 AUG 2008   106     SELECT     FROM    table1  WHERE   column datetime  gt    p date  AND     column datetime  lt  DATEADD d  1   p date    The advantage of this is that it will use any index on  column datetime  if it exists

User · Answer

Something like this   SELECT    FROM    table1 WHERE   convert varchar  column datetime  111     2008 08 14

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT CONVERT VARCHAR 2  DATEPART  dd  doj                CONVERT VARCHAR 2  DATEPART  mm  doj                CONVERT VARCHAR 4  DATEPART  yy  doj   FROM emp

User · Answer

The best way is to simply extract the date part using the SQL DATE   Function   SELECT    FROM table1 WHERE DATE column datetime     p date

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   CONVERT VARCHAR 15   strDate   112

User · Answer

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

User · Answer

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

User · Answer

SELECT   FROM tablename WHERE CAST FLOOR CAST column datetime AS FLOAT  AS DATETIME     30 jan 2012

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Compare a date string to datetime in SQL Server?

Examples related to sql-server

Examples related to database

Examples related to tsql

Examples related to datetime