CODEWITHSUNDEEP
sqlsql-server
mKorbel
mKorbel

Reputation: 109823

SQL Query Date conditions

I have table in Ms SQL Server 2005 (2008) in following structure

ID Name     AddDate                   AmendDate                 Status
-----------------------------------------------------------------------
1  Jason    2012-09-03 08:01:00.000   2012-09-03 14:02:00.000   Amended
2  Robert   2012-09-03 08:05:00.000   NULL                      New
3  Celia    2012-09-03 08:10:00.000   2012-09-03 14:02:00.000   Amended
4  Jason    2012-09-03 14:02:00.000   NULL                      New
5  Robert   2012-09-03 14:03:00.000   2012-09-03 20:02:00.000   Amended
6  Celia    2012-09-03 14:07:00.000   2012-09-03 20:02:00.000   Amended
7  Jason    2012-09-03 20:00:00.000   NULL                      New
8  Robert   2012-09-03 20:02:00.000   NULL                      New
9  Celia    2012-09-03 20:04:00.000   NULL                      New

routine runs three times per day as snapshot,

please how can I by using SQL Query

  1. select all record from morning (or evening) for any of days

  2. how can I replace (ISNULL(AmendDate, '2012.09.04')) for column AmendDate where is Null value, in the case when I walking in history, for example returns all row that AddDate <= '2012-09-01' and AmendDate >= '2012-09-02'

  3. question is about SELECT - FROM - WHERE - only

Upvotes: 1

Views: 10379

Answers (3)

Andr&#225;s Ott&#243;
Andr&#225;s Ott&#243;

Reputation: 7695

Do you want something like this for the 2. question? I used a parameter because it is easier to show:

  DECLARE @AddDate datetime

    SELECT *
    FROM yourTable
    WHERE 
    AddDate <= @AddDate 
    and AmendDate >= ISNULL(AmendDate,DATEADD(day, 1, @AddDate))

Upvotes: 1

RePierre
RePierre

Reputation: 9576

To select all records from morning/evening for any of days

select *
from Table t
where t.AddDate > '2012-09-03' 
      and t.AddDate < '2012-09-04'
      and DatePart(HOUR, t.AddDate) < 12 -- Morning
      -- and DatePart(HOUR, t.AddDate) > 18 -- Evening

For the second part (given that the requirement is not very clear) I think you can use Coalesce function with a CTE

;WITH FullData(Id, Name, AddDate, AmendDate, Status)
     AS (SELECT t.Id,
                t.Name,
                t.AddDate,
                COALESCE(t.AmendDate, '2012-09-04'),
                Status
         FROM   Table t)
SELECT *
FROM   FullData d
WHERE  d.AddDate <= '2012-09-01'
       AND t.AmendDate >= '2012-09-02'

Upvotes: 1

Vikdor
Vikdor

Reputation: 24134

For the first one, you can use the DATEPART function on the date to extract the hour and apply your criteria on whether you are interested in the records added in the morning or in the evening.

DATEPART(hh, AddDate) BETWEEN 0 AND 12 -- Morning.

Second question is not clear :(

Upvotes: 1

Related Questions