Search between dates and times in SQL Server 2008

sql-server-2008

Solution

you should look at the date time formats available in SQL Server: http://msdn.microsoft.com/en-us/library/ms187928.aspx

`yyyy-mm-dd hh:mi` is what you should use:

try:

SELECT
    *
    FROM Records
    WHERE DateCreated>='2007-02-30 10:32' AND DateCreated<='2008-06-21 14:19'

in the above query the strings will be converted to datetime data type if `DateCreated` is a datetime column. and the query will work.

you can create local variables of datetime data type and use a query like:

DECLARE @StartDate datetime, @EndDate datetime

SELECT @StartDate='2007-02-30 10:32', @EndDate='2008-06-21 14:19'

SELECT
    *
    FROM Records
    WHERE DateCreated>=@StartDate AND DateCreated<=@EndDate

I like using <, <=, >=, or > because it allows more flexibility than `BETWEEN` and forces you to think about including endpoints or not.

Another thing to consider is getting all data from a complete day:

DECLARE @StartDate datetime, @EndDate datetime

--set the days you want
SELECT @StartDate='2007-02-30 10:32', @EndDate='2008-06-21 14:19'

--remove the time
SELECT @StartDate=DATEADD(day,DATEDIFF(day,0,@StartDate),0), @EndDate=DATEADD(day,DATEDIFF(day,0,@EndDate),0)

--get everything on '2007-02-30' up to the end of the day on '2008-06-21'
SELECT
    *
    FROM Records
    WHERE DateCreated>=@StartDate AND DateCreated<@EndDate+1

Problem

I need to search between dates and times. For example, between `date: 30/02/2007, time: 10:32` and `date: 21/06/2008, time: 14:19` What is the most simple query for this? Thanks in advance.

Original source