CODEWITHSUNDEEP
sql-serverdatetimewhere-clause
GHINOTE
GHINOTE

Reputation: 5

SQL-Server Datetime in WHERE Clause Issue

enter image description hereI am creating a query of two values, a period number and the corresponding date. The periods are accounting periods that are similar but not identical to our months. The dates are largely correct, but there are a few inconsistencies that I thought I would clean up with a WHERE statement.

For example, the date 4/1/2015 belongs in period 3, but it shows up in both period 3 and period 4. To fix this, I thought that I would create a WHERE statement stating:

SELECT DISTINCT table1.period,
                table1.datetime
FROM table1
WHERE table1.period <> 4 AND table1.datetime <> '4/1/2015'

This took away all of period 4 away instead of just the one I needed. I have also tried the datetime syntax: 

AND table1.datetime <> '20150401 00:00:00.000'

Both syntaxes had the same results. I am fairly inexperienced at SQL and have always hated dealing with datetime values, so any help would be great.

-EDIT- Forgot to add single quotations to the first datetime WHERE clause.

Upvotes: 0

Views: 614

Answers (2)

xbb
xbb

Reputation: 2163

Try following:

SELECT DISTINCT table1.period,
            table1.datetime
FROM table1
WHERE CASE WHEN table1.period = 4 AND CONVERT(VARCHAR(8),table1.datetime,112) = '20150401' THEN 1 ELSE 0 END = 0

This only get rid of rows that both has period=4 and datetime=20150401, while your query first get rid of anything that has period=4(no matter what [datetime] is), then get rid of anything has datetime=20150401.

Upvotes: 1

GuidoG
GuidoG

Reputation: 12014

Did you say that period is a string field ? you mean a varchar ?
Also you want to check on the date part only, not the time ?

then you can try this

SELECT DISTINCT table1.period,
                table1.datetime
FROM table1
WHERE table1.period <> '4' 
AND convert(date, table1.datetime) <> '20150401'

Upvotes: 0

Related Questions