SQL Query Date conditions

Question

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

select all record from morning (or evening) for any of days
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'
question is about SELECT - FROM - WHERE - only

what do you mean by "walking in history"? also, do you want to consider null AmendDate as 2012.09.04? — Vikdor
– Vikdor, Commented Sep 4, 2012 at 7:24
@Vikdor "walking in history" I meaning resultset from (for example) morning from january, april or e.i. ..., and in the case that value is NULL I want to add NOW() or DATE() +1, or better is use AmendDate >= '2012-01-15' OR AmendDate IS NULL — mKorbel
– mKorbel, Commented Sep 4, 2012 at 7:29
Sorry, I still couldn't get it, would you be able to add the query with select list and where clauses and then tell what you intend to get the output as? — Vikdor
– Vikdor, Commented Sep 4, 2012 at 7:32
@Vikdor I want to determine if Record is Amended or not, maybe replace NULL to DATE +1 is wrong way, sorry for my Navajo language — mKorbel
– mKorbel, Commented Sep 4, 2012 at 7:40
Since amendDate carries null for records that are not amended, you filtering criteria would be amendDate IS NOT NULL to exclude records that are not amended. Hope I got you right this time :) — Vikdor
– Vikdor, Commented Sep 4, 2012 at 7:42

RePierre · Accepted Answer · 2012-09-04 07:40:09Z

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'

Vikdor · Accepted Answer · 2012-09-04 07:22:06Z

1

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 :(

answered Sep 4, 2012 at 7:22

Vikdor

24.2k10 gold badges66 silver badges86 bronze badges

Comments

András Ottó · Accepted Answer · 2012-09-04 07:50:36Z

1

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))

edited Sep 4, 2012 at 7:50

answered Sep 4, 2012 at 7:35

András Ottó

7,7351 gold badge30 silver badges38 bronze badges

Collectives™ on Stack Overflow

SQL Query Date conditions

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related