CODEWITHSUNDEEP
t-sql
Tony
Tony

Reputation: 12705

T-SQL How to check if DataTime contains a Date string?

I have a DateTime column row and I declare a date string:

Row:

2010-08-27 13:45:55

My string:

'2010-08-27'

How can I check if that string is in that row ?

I tried the following query:

declare @year as nvarchar(4)
declare @month as nvarchar(2)
declare @day as nvarchar(2)

set @year = '2010'
set @month = '08'
set @day = '23'

 select * FROM [dbo].[customer_import] CsrImport

 where
    (YEAR(CsrImport.import_date) = @year
       AND MONTH(CsrImport.import_date) = @month
        AND DAY(CsrImport.import_date) = @day)

but I see that it returns all rows (even that are not contains that date)

Upvotes: 0

Views: 5612

Answers (4)

SQLMenace
SQLMenace

Reputation: 135121

Like this, this will also be able to use the index, do not use function on the column itself..it is not SARGable!!

where import_date >= convert(datetime,@year + @month + @day)
and import_date < convert(datetime,@year + @month + @day) + 1

The best way for you would be to use dates and not 3 different parameters, what if someone passes in 13 for month?

Here is an example which checks that the values that are passed in can be converted to a date, if not it will show an error message

DECLARE @year AS NVARCHAR(4)
DECLARE @month AS NVARCHAR(2)
DECLARE @day AS NVARCHAR(2)

SET @year = '2010'
SET @month = '08'
SET @day = '23'


DECLARE @date DATETIME
IF ISDATE(@year + @month + @day) = 0
    BEGIN
        RAISERROR('values passed in are not a valid date',16,1)
        RETURN
    END
ELSE
    BEGIN
        SET @date = @year + @month + @day
    END
SELECT * FROM [dbo].[customer_import] CsrImport
WHERE import_date >=@date
AND import_date < @date + 1

Upvotes: 3

Charles Bretana
Charles Bretana

Reputation: 146557

Just turn the datestring into a date (or datetime) variable and use a where clause: Since your table has times in it, you have to strip them out or compare them to the midnioght before and after

Declare @myDate DateTime
Set @myDate = 'August 23 2010'

 Select * FROM [dbo].[customer_import] CsrImport 
 Where DateDiff(day, myDate,import_date) = 0  -- Not Sargable

or 

 Declare @myDate DateTime
 Set @myDate = 'August 23 2010'

 Select * FROM [dbo].[customer_import] CsrImport 
 Where import_date) Between @mydate And @Mydate + 1  -- Sargable

Upvotes: 0

Paul Hadfield
Paul Hadfield

Reputation: 6136

That should work, howabout if you make the values INTS

declare @year as INT
declare @month as INT
declare @day as INT

set @year = 2010
set @month = 08 
set @day = 23

select * FROM [dbo].[customer_import] CsrImport 

where 
    (YEAR(CsrImport.import_date) = @year 
        AND MONTH(CsrImport.import_date) = @month 
        AND DAY(CsrImport.import_date) = @day)

EDIT: Make sure all the statement is highlighted when you run it too. As simple as it seems, is it possible you mised the where clause if you highlighted the statement.

Upvotes: 1

Pranay Rana
Pranay Rana

Reputation: 176946

Sql server : ISDATE (Transact-SQL)

----Invalid date
SELECT ISDATE('30/2/2007')
RETURNS : 0 (Zero)
----Valid date
SELECT ISDATE('12/12/20007)'
RETURNS : 1 (ONE)
----Invalid DataType
SELECT ISDATE('SQL')
RETURNS : 0 (Zero)

Upvotes: 3

Related Questions