SELECT Datetime values using where clause

Transact-SQL
,
1

I have a dataset similer to below code.

CREATE TABLE TEST.[dbo].[dt] (
[TRAN DATE] datetime
)

INSERT INTO TEST.[dbo].[dt]
([TRAN DATE])
VALUES
('2017-01-01 08:24:03.000'),
('2017-01-03 10:24:03.000'),
('2017-02-10 09:50:03.000'),
('2017-03-05 12:24:03.000'),
('2017-04-17 20:24:03.000')

-- Select one specific date is ok.
SELECT *
FROM TEST.[dbo].[dt]
WHERE CONVERT(VARCHAR(25), [TRAN DATE], 126) LIKE '2017-01-03%'

I want to select several specific dates using "WHERE", IN & LIKE operators appropriatly
I tried using below query, but it's not succesfull.
SELECT *
FROM TEST.[dbo].[dt]
WHERE CONVERT(VARCHAR(25), [TRAN DATE], 126) IN ( '2017-01-03%', '2017-03-05%', '2017-04-17%')

Can someone help me on this please..
Thanks for your effort.

2

when you use the IN operator you should use the exact values and not wildcards.

SELECT *
FROM TEST.[dbo].[dt]
WHERE CONVERT(VARCHAR(25), [TRAN DATE], 126) LIKE '2017-01-03%'
OR CONVERT(VARCHAR(25), [TRAN DATE], 126) LIKE '2017-03-05%'
OR CONVERT(VARCHAR(25), [TRAN DATE], 126) LIKE '2017-04-17%'

1 Like
3

Thank you

4

Rather than trying to convert the dates to strings and then compare, try to use date comparisons. Here are some possibilities; I prefer the first one.

SELECT * 
FROM dbo.dt
WHERE [TRAN DATE] >= '20170103' AND [TRAN DATE] < '20170104';

SELECT * 
FROM dbo.dt
WHERE CAST([TRAN DATE] AS DATE) = '20170103';
1 Like
5

Thanks Jamesk,

So the below code worked it.

SELECT *
FROM TEST.[dbo].[dt]
WHERE CAST([TRAN DATE] AS DATE) IN ( '20170103', '20170305', '20170417')

6

You generally never want to use a function or CAST on a data column unless it's absolutely unavoidable, because it could make searches far less efficient (because any function / CASTing makes the comparison "non-sargable").
While I think SQL internally does special conversions to avoid search issues for casting to date vs datetime (at least in newer versions of SQL Server), It's best not to rely on that.
Thus, although it doesn't "look as nice", you really should code it this way:

WHERE (([TRAN DATE] >= '20170103' AND [TRAN DATE] < '20170104') OR
       ([TRAN DATE] >= '20170305' AND [TRAN DATE] < '20170306') OR
       ([TRAN DATE] >= '20170417' AND [TRAN DATE] < '20170418'))
2 Likes