CODEWITHSUNDEEP
sqldatedatetimecastingsql-server-2012
Samadhi Sankalani
Samadhi Sankalani

Reputation: 91

Cast datetime in the where clause of the query

SELECT *
FROM tblClassAppointments
INNER JOIN tblClassGroups ON tblClassAppointments.GroupID = tblClassGroups.GroupID
WHERE (
        ('2015-07-13' >= StartTime)
        AND ('2015-07-13' <= EndTime)
        AND ('2015-07-13' > StartTime)
        AND ('2015-07-13' >= EndTime)
        )
    OR (
        ('2015-07-13' >= StartTime)
        AND ('2015-07-13' < EndTime)
        AND ('2015-07-13' > StartTime)
        AND ('2015-07-13' <= EndTime)
        )
    OR (
        ('2015-07-13' <= StartTime)
        AND ('2015-07-13' < EndTime)
        AND ('2015-07-13' >= StartTime)
        AND ('2015-07-13' <= EndTime)
        )
    OR (
        ('2015-07-13' <= StartTime)
        AND ('2015-07-13' >= EndTime)
        )

I use above query for getting class appointments which are placed for a certain time period. I need to cast the StartTime(tblClassAppointments.StartTime) and EndTime(tblClassAppointments.EndTime) as yyyy-mm-dd.

What I tried :

I tried 

 CONVERT(char(10), StartTime, 126)

and 

CONVERT(char(10), EndTime, 126)

instead of StartTime and EndTime. It gave an error.

What I need :

How I convert StartTime and EndTime as yyyy-mm-dd inside the where clause itself? (both the fields are in type of datetime)

Please advice me. Thanks

Upvotes: 1

Views: 1434

Answers (2)

Greg
Greg

Reputation: 4045

Your WHERE clause is too confusing, I don't understand it at all, I would try using BETWEEN when possible.

I am willing to bet you are getting a truncation error, because you are trying to convert a datetime datatype to a char(10), which is way too small. If all you care about is the date portion, then cast/convert to DATE datatype.

Here is an example of one way to do this:

; with CTE1 as (
    SELECT *
    , cast(StartTime as date) as StartTime2
    , cast(EndTime as date) as EndTime2
    FROM tblClassAppointments
    INNER JOIN tblClassGroups ON tblClassAppointments.GroupID = tblClassGroups.GroupID
)
select *
from CTE1
WHERE
    ('2015-07-13' between StartTime2 and EndTime2)
OR (
    ('2015-07-13' <= StartTime2)
    AND ('2015-07-13' < EndTime2)
    AND ('2015-07-13' >= StartTime2)
    AND ('2015-07-13' <= EndTime2)
    )
OR (
    ('2015-07-13' <= StartTime2)
    AND ('2015-07-13' >= EndTime2)
    )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

This is too long for a comment and doesn't directly address any formatting issue with strings. Your date comparisons are just complicated. If StartTime and EndTime have a time component, and you want to know if the entire day of '2015-07-13' is covered, then use:

WHERE StartTime <= '2015-07-13' and EndTime >= '2015-07-14'

That is, the period starts before the day begins and ends after the day starts.

If you want to know if any part of the date is covered:

WHERE StartTime < '2015-07-14' and EndTime >= '2015-07-13'

That is, the period starts before the day ends and begins after the day starts.

If you don't like '2015-07-14' in the logic, then use DATEADD(day, 1, '2015-07-13').

And, if you want to be independent of internationalization settings, you can also use CAST('20150714' as DATE).

Upvotes: 0

Related Questions