CODEWITHSUNDEEP
t-sqldatetime
Mike
Mike

Reputation: 1261

SQL help on Date and Time Range

I got a little stump on this expanding my below query. The query below works and gives me all the rows that I need. 

select (e.Firstname+' '+ e.lastname) as ApprovedBy
    , wd.TargetDate
    ,wd.ACTDate
    ,wd.UploadDate
    ,wd.ApproveDate 
From Approved wd 
JOIN Employee e 
    on EmployeeID = wd.ApproveDateId 
where wd.ApproveDate Between '4/2/2012 14:30:00.000' and '4/30/2012 23:00:59.000'
order by ApproveDate

now I have to put this in a date range? What I need is some direction on how to get all the rows that are greater than '4/2/2012 14:30:00.000' but between 4/2/2012 and '4/30/2012? I just want the rows whose times are greater than 14:30:00.000 for the date range?

thanks

Upvotes: 0

Views: 324

Answers (2)

HeavenCore
HeavenCore

Reputation: 7683

assuming you mean you want a DATE range (i.e. no time) then a 2nd condition including time, that could be done as follows (SQL 2008+):

SELECT  ( e.Firstname + ' ' + e.lastname ) AS ApprovedBy ,
        wd.TargetDate ,
        wd.ACTDate ,
        wd.UploadDate ,
        wd.ApproveDate
FROM    Approved wd
        JOIN Employee e ON EmployeeID = wd.ApproveDateId
WHERE   CONVERT(DATE, wd.ApproveDate) BETWEEN CONVERT(DATE, '4/2/2012', 101)
                                      AND     CONVERT(DATE, '4/30/2012', 101)
        AND wd.ApproveDate > CONVERT(DATETIME, '4/2/2012 14:30:00.000', 101)
ORDER BY ApproveDate

If you want all days (within the range) where the time was greater, replace the 2nd condition above with this:

AND CONVERT(TIME(0), wd.ApproveDate) > CONVERT(TIME(0), '14:30:00')

Upvotes: 1

Padrig
Padrig

Reputation: 193

The format should be 

'YYYY-MM-DD HH:MM:SS'

so the 2nd of April 2012 would be

'2012-04-02 14:30'.

You can you use BETWEEN to compare dates like this.

Upvotes: 0

Related Questions