Post reply

Return all rows for todays date, and yesterday.

  • October 17, 2006 at 6:06 am

    #114707

    Hi all,

    I have searched the forum but to no avail.

    Whats the best way for me to return all rows that have a datetime for today and yesterday, where yesterday can go back to being from a previous month?

    Thanks

    Tryst

  • October 17, 2006 at 6:10 am

    #666170

    Does "today" go back to prvious month with "yesterday"?

    _____________
    Code for TallyGenerator

  • October 17, 2006 at 6:40 am

    #666185

    Just semantics but how can yesterday be from last month

    Two possible interpretations I can see from your request

    1.

    DECLARE @today datetime, @yesterday datetime

    SET @today = '20061017'

    SET @yesterday = '20060916'

    SELECT [column]

    FROM

    WHERE [date] >= @yesterday

    AND [date] <= @today+1

    2.

    DECLARE @today datetime, @yesterday datetime

    SET @today = '20061017'

    SET @yesterday = '20060916'

    SELECT [column]

    FROM

    WHERE ([date] >= @today AND [date] <= @today+1)

    OR ([date] >= @yesterday AND [date] <= @yesterday+1)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • October 17, 2006 at 7:01 am

    #666189

    "Just semantics but how can yesterday be from last month?"

    Well, I suppose it was meant as "if today is the first of a month, yesterday is the last day from previous month". Trystan probably wanted to avoid solutions based on subtracting 1 from the day number.

    I would suggest this solution, which also takes care of any possible time portions in the date:

    SELECT col1, col2

    FROM tbl

    WHERE tbl.date >= DATEADD(d, DATEDIFF(d, 1, GETDATE()), 0) /*date is greater or equal to yesterday*/

    AND tbl.date < DATEADD(d, DATEDIFF(d, -1, GETDATE()), 0) /*date is less than tomorrow*/

    If you need to parametrize it (using some other date instead of actual as a "today" date), just replace GETDATE() with a variable.

  • October 17, 2006 at 7:10 am

    #666194

    Good catch Vladan

    Obvious when someone shows you how

    Far away is close at hand in the images of elsewhere.
    Anon.

  • October 17, 2006 at 7:10 am

    #666193

    why have the datediff and dateadd?  Would it be easier to just do something like this:

    declare

    @offset int

    set

    @offset = 1

    select

    * from tbl

    where tbl.datefield > getdate()-@offset

    C

    -- Cory

  • October 17, 2006 at 7:13 am

    #666196

    "Well, I suppose it was meant as "if today is the first of a month, yesterday is the last day from previous month". Trystan probably wanted to avoid solutions based on subtracting 1 from the day number."

    Exactly that, Vladan

    Ok, will check some of these out, but as its to be run on SQL Mobile, the use of variables must be avioded.

    Thanks.

    Tryst

  • October 17, 2006 at 7:40 am

    #666203

    Ok, I am going to throw a spanner in the works here, as it seems that I need todays date (as in my orginal requirement) but then need the next latest date that resides in the table.

    So display all entries/rows that have been created today, and also all other rows that the last day in which rows were entered into the database. This fixes the issue for scenarios where I would run this on a Monday, which would be get all entries for today, and then also get the next last day in which rows where entered, which would be the Friday (poss sat) - but if the queries works, it wouldn't matter what day it was as it would return all those for either Friday or Sat.

    (i'm using SQL Mobile, so variables, and Sub Queries are not allowed, apart from in the IN clause).

    (I am guessing I may need to perform more than one query to get this type of data??)

    Tryst

  • October 17, 2006 at 7:55 am

    #666209

    No, it wouldn't. That is, it may be easier, but it will not work properly.

    GETDATE() returns for example 2006-10-17 16:34:27. DATEADD/DATEDIFF are there mainly to cut off the time portion, and I tend to use them anywhere, where I have to compare some dates. Even if it is just to pull data from today, without any offset.

    That DATEADD allows to manipulate the date as required, to get yesterday, today and tomorrow, is fortunate and IMHO this is the easiest way to do it - although of course, there several ways to get at the same result.

    Generally, when working with datetime data in SQL Server, you always have to remember the time portion (and also the fact that time portion is ALWAYS part of the date - even if it is 00:00:00, which means midnight, i.e. first second of the new day). If you don't strip away time portion, you will get rows that were entered not during yesterday and today, but from 2006-10-16 16:34:27 to now (using the above GETDATE() result). You want to have there all rows from yesterday.

  • October 17, 2006 at 8:00 am

    #666212

    Thanks for the explaination.  Another way to look at this may be that if you want everything from today and yesterday, you need the DATAADD/DATEDIFF.  If you want everything for the past 24 hours from now, you need the getdate()-1.  A fair amt of the the stuff I have done needs just 24 hours.

    Thanks!

    -- Cory

  • October 17, 2006 at 8:15 am

    #666228

    That's precisely what I was trying to put across - it depends on what you need. I probably never needed anything for last 24 hours . Most of the stuff I do requires data from a certain day (or day range).

  • October 17, 2006 at 8:22 am

    #666233

    I have managed to get this to work by using the following query (quite messy query )

    SELECT *

    FROM tbl_NSP_Inspection

    WHERE ((CreatedDate >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)) AND (CreatedDate < DATEADD(d, DATEDIFF(d, -1, GETDATE()), 0)))

    OR (SUBSTRING(CONVERT(NVARCHAR(20), CreatedDate, 21), 1, 10) IN (SELECT SUBSTRING(CONVERT(NVARCHAR(20), MAX(CreatedDate), 21), 1, 10)

    FROM tbl_NSP_Inspection

    WHERE (DATEPART(DAY, CreatedDate) != DATEPART(DAY, GETDATE()))

    OR (DATEPART(MONTH, CreatedDate) != DATEPART(MONTH, GETDATE()))

    OR (DATEPART(YEAR, CreatedDate) != DATEPART(YEAR, GETDATE()))

    ))

    Tryst

  • October 17, 2006 at 8:35 am

    #666242

    Trystan,

    Try this for your where clause:

    dateadd

    (d,datediff(d,0,CreatedDate),0) in (select top 2 dateadd(d,datediff(d,0,CreatedDate),0) from tbl_NSP_Inspection order by TestDate desc)

  • October 17, 2006 at 8:37 am

    #666245

    I don't know anything about limitations of Mobile SQL, but to hold to the style with which you started, it would be better to write it this way:

    SELECT col1, col2, ...

    FROM tbl_NSP_Inspection i

    WHERE ((i.CreatedDate >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))

     AND (i.CreatedDate < DATEADD(d, DATEDIFF(d, -1, GETDATE()), 0)))

    OR DATEADD(d, DATEDIFF(d, 0, i.CreatedDate), 0) =

     (select DATEADD(d, DATEDIFF(d, 0, max(i2.CreatedDate)), 0)

     from tbl_NSP_Inspection i2

     where i2.CreatedDate < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))

    If you wonder why I'm using aliases everywhere, look here:

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=315200

  • October 17, 2006 at 8:43 am

    #666252

    Sorry, missed something in my testing, this is what you should try:

    dateadd(d,datediff(d,0,CreatedDate),0) in (select distinct top 2 dateadd(d,datediff(d,0,CreatedDate),0) from tbl_NSP_Inspection order by dateadd(d,datediff(d,0,CreatedDate),0) desc)

     

    Also noticed I forgot to change one other column name in my previous post.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply