Using GETDATE 2

[dvannoy]

I have a field called DUE_DATE

I'm trying to show records that have a DUE_DATE of today and anything 10 days out in between. The below shows 10 days out but does not show today's records.

DUE_DATE BETWEEN GETDATE() AND GETDATE() + 10

any help would be appreciated

thanks

 

[Olaf Doschke]

Just look at what you get, when you vanilla plain [tt]SELECT GETDATE()[/tt]

Spoiler

It's a datetime

It will depend what type DUE_DATE is, is it really just a (not so) new date type or datetime type? Typically dates are stored in MSSQL DBs as datetime with 0:00:00 time portion.

Depending on that you can either CAST(GETDATE() as date) or rather go back to midnight with the legacy trcikery general solution of computing [tt]DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)[/tt].

That shows you there is an officially better way to compute a) datedifferences and b) new dates/datetimes via DATEDIFF and DATEADD. That is importatnt to know, because your shorthand GETDATE()+10 could also mean any other time portion than days, you're just lucky, you should rather go the official way and DATEADD(dd,GETDATE(),10) to get the datetime in 10 days, though to get that at midnight the expression I already showed can be used just modified by using the 10 in place of the DATEADD parameter: [tt]DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 10)[/tt].

There are lot of sites providing list of such expressions, for example

http://sqlmag.com/t-sql/cheat-sheet-calculating-important-dates

Some of those are now having a good new function equivalent, eg EOMONTH() for end of month date, but there always is quite a lag of when such things are used in production when the old expressions still do their job. Plus, EOMONTH surely doesn't fit your specific needs here. I just mention it to say Microsoft finally has given us at least a couple of function not needing a convoluted expression anymore.

Bye, Olaf.

 

[Andrzejek]

It would help immensely if you would say:"I have a field called DUE_DATE " and the data looks like:[tt]
DUE_DATE
1/1/2017 00:00:00
2/2/2019 12:23:56
10/17/2017 16:34:23
[/tt]
or
[tt]
DUE_DATE
1/1/2017 00:00:00
2/2/2019 00:00:00
10/17/2017 00:00:00
[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dvannoy]

Thanks for the info

Sorry, here is more detail...

DUE_DATE field is datetime and the data looks like this;

2016-12-31 00:00:00.000

 

[Olaf Doschke]

That was almost to be expected if you didn't have dates as just dates because GETDATE() is at almost any time later than midnight. So unless you don't make that query exactly at midnight DUE_DATE being today's date with 00:00:00.000 time is before any GETDATE() and not between GETDATE() AND GETDATE() + 10.

You can work with

DUE_DATE BETWEEN DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 10)

This, in turn, will fail on any DUE_DATE, which would have any other time portion than 00:00:00.00.

I personally would recommend using date columns at least for new development, though many T-SQL and MSSQL experts and DBAs are so used to working with pseudo dates in the form of special DateTimes, they actually prefer them. If you go for DateTime, you can always go for it the full way, using GETDATE() un"truncated" as a default value of DateTime columns storing "now" at the time of creating them or updating them. Then it get's obvious you are talking about points in time and not a date spanning a whole day. And then it's easier to grasp, what between then has to mean. Any query using BETWEEN GETDATE() and GETDATE()+x then has to fail to get data from column storing past DateTimes, as now always is after any previous time stored in the data. That nature is even more so true for DateTimes at midnight, but you won't ignore the fact a column actually stores a point in time and thus if you want all today's data, that already spans from midnight to next day midnight.

Bye, Olaf.

 

[Andrzejek]

I guess you may try:
[tt]DUE_DATE BETWEEN GETDATE() [BLUE]- 1[/BLUE] AND GETDATE() + 10[/tt]


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[kjv1611]

dvannoy,

Both the methods mentioned here will work fine. Olaf's DATEADD(DATEDIFF()) construction will probably be the most specific, as far as saying between 2 exact dates. However, I like Andy's method, myself, for quick and dirty queries. There were MANY times when I needed to do such a comparison for my last employer, and the GETDATE()-1 covered it quite well.

In case it doesn't make sense, here's why the -1 method works:

If "now" is say 8:00am, you can't just say "GETDATE()" b/c that'll say today at 8:00am. If you do Olaf's long function (which I've also used same and similar many times b/c I wanted the specific midnight or just before midnight time), then you'll tell SQL to compare against today at midnight. Well, if you just use Andy's GETDATE()-1, it'll work without having to think about the DATEADD(DATEDIFF)) structure. It works, b/c you're telling it to compare to Yesterday at 8:00am, so "today" at midnight is greater than yesterday at 8am, and therefore falls within the possible results.

I hope that helps. I know it seems simple and silly to think about this, but sometimes, when you're buried under a big work load, even the simple things can seem heavy or difficult to think through.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[dvannoy]

thanks everyone for your info and examples, I appreciate it.

I ended up using DUE_DATE BETWEEN GETDATE() - 1 AND GETDATE() + 10

Now, I have another question how would you use BETWEEN < GETDATE() AND GETDATE() + 10 ??? I get a syntax error on the <

 

[Andrzejek]

Drop < and try:[tt]
Where SomeDate BETWEEN GETDATE() AND GETDATE() + 10
[/tt]
Unless you want to do something else.
If so, provide more explanation....

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

BETWEEN already is an operator, you can't do BETWEEN < combination of > only works with = and >, but in that case <= and <> are themselves another operator.

If you don't want to use normal comparison operators, don't use BETWEEN.

Notice DUE_DATE > GETDATE() AND DUE_DATE < GETDATE() + 10 would also not work, as DUE_DATE is midnight and GETDATE is NOT a date only, it is a datetime, and it is not having midnight as time portion, it is simply NOW. I told you to test SELECT GETDATE() at the SQL Server Management Studio to see for yourself what value you handle here.

BETWEEN GETDATE()-1 AND GETDATE()+10 is a quick and dirty hack, as kjv1611 already said and tried to explain. Since your date stored is say 08/11/2017 00:00:00 any GETDATE() of this day is later with a time after 00:00:00, so every GETDATE()-1 going back one day is a datetime of the previous day 08/10/2017 hh:mm:ss, which is larger than 08/10/2017 00:00:00 and smaller than 08/11/2017 00:00:00 for the whole day, so it always includes the datetime stored in due_date, that you want to include.

If you want to exclude today, then you simply can go with DUE_DATE BETWEEN GETDATE() AND GETDATE() + 10 or +11. because that lower bound GETDATE() is now and that means the due_dates need to be in the future, if you don't store them with a time portion the earliest due_date within that interval is the coming midnight, which stands for the next day.

Just remember the one thing most important: Your stored date isn't a date it is a datetime. A date is the same for a whole day, if GETDATE() would be as the name suggests and give you a date, that value wouldn't change for a whole day. But GETDATE() gives you a different datetime every millisecond or faster (depends on the precision of the datetime type you choose, there are several datetime types). So you act on time points and your upper and lower bounds of BETWEEN also are time points. BETWEEN translates to intervals including the lower and upper bound, which makes perfect sense, when it comes to querying BETWEEN integer numbers, eg BETWEEN 1 AND 10, you wouldn't expect that to exclude 1 and 10, would you?

Bye, Olaf.

 

[dvannoy]

so if due_date = 08/11/2017 and it's now 08/12/2017 then GETDATE() would not show any records that are < then GETDATE()

what I now need to do is show the records that the due_date is < "Today" and also due 10 days from "Today" so if the due_date has passed I still need to show it.

 

[Olaf Doschke]

so if due_date = 08/11/2017 and it's now 08/12/2017 then GETDATE() would not show any records that are < then GETDATE()

GETDATE in itself is just a value. What do you really mean here?

BETWEEN A and B never shows anything <A, no matter what A is.

The point is GETDATE() done in the query will be later than GETDATE() stored in due_date beforehand (it has to be done beforehand, because you can only query data stored beforehand). Since GETDATE() is changing right after the insert, and not staying the same for a whole day, yes, the BETWEEN you first tried never will cover the current day data up to then.

What is so difficult to comprehend about GETDATE(), it is returning values which rise over time, as time itself passes. When you store GETDATE(), the next millisecond that is already lower than what any later GETDATE() call returns, so it is before.

I don't know where you're at and what you want to achieve, but one last try: If you don't want to define a lower bound, then don't use BETWEEN.

For example if I had a database with orders with due_dates, it wouldn't matter if due_dates are past or not, I always will also be interested to prioritize already late orders, where due_date is already past, then I would only query for orders still due by a status processed = 0, for example and due_date<GETDATE()+10, to limit the view to orders of current interest and not look too far into even later due_dates.

You really might want to take a break or rewatch some sesame street videos about lower and higher, earlier and later, between and not between, I don't know. You have my total compassion about this needing concentration, but it's just about understanding datetimes are points in time and nothing else.

Bye, Olaf.

 

[dvannoy]

thanks for the info Olaf....

I'm not an expert like you so that's why I asked the question in the first place. I appreciate your time and help.

BTW, I'm searching for those sesame street episodes right now, great stuff.

 

[Andrzejek]

Let me try this explanation:

If you run this code (in VB):

Debug.Print "Today's value is " & CDec(Date)
Debug.Print "Now's value is " & CDec(Now)

You get:
[tt]Today's value is 42958
Now's value is 42958.6003935185[/tt]

Both are Dates, but first one does not have Time portion, it points to Midnight of that Date. You may also express it as 42958.000000, right?

dvannoy,
In your data base you store your date as whole numbers, no Time portions. So it looks pretty much this way ( it's just formatted to you to show it to you as date you can recognize)
[tt]
DUE_DATE
42970
42958
42999[/tt]

GETDATE() function gives you the Now value - with the Time portion.


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

I'm searching for those sesame street episodes right now

Legendary stuff. Thanks for taking it with humour.

Finally, if you simply told us what timespan you're interested in to see and whether all your due_dates are as you posted (2016-12-31 [highlight #FCE94F]00:00:00.000[/highlight]), then it's perhaps easiest you say in laymen terms, natural language.

Just one more time let me also point out, MSSQL also offers a pure date type, fields just storing the date portion of a datetime. That is a more naturally known data type. In reality we normally don't even think of a type called datetime, date and times are separate, but if you think about it, a time only is a type that is valuable for any regular things not related to a certain date, any concrete point in time only existing once in all time is a date and time or short datetime.

If you would change your due_date column to date as type, storing GETDATE() into it would always truncate off the time portion. It's not recommended, if this database is well established and much code written about it, as changing a column data type can harm already existing code to stop working, as it expects a time portion. But as I called it "for new development" I would recommend to store dates in date fields, it is less confusing and you don't need the time precision. Aside from that the functions concerned with adding time intervals or computing a difference in a time interval granularity are also applying to date types, obviously only down to the time intervals of at least a day, i.e. it makes no sense to add a second to a date, that will be rounded down to the same date anyway. And when I talk of time intervals, I simply mean seconds, minutes, hours, days, weeks, months, years, just the usual time intervals you're used to think in and calculate in. Just look at DATEADD() and DATEDIFF() alone, each of them is very straight forward to understand.

And last not least, as the data type date was only established in (let me lie) 2008, still almost 10 years after MSSQL DB developers and DBAs are used to only working with datetimes. Once you get into the "mood" or "groove" of how to think about them everything is logical and fine, but it almost guarantees newbies to fail to create filter conditions.

Bye, Olaf.

 

[dvannoy]

This worked for what I need to do...

due_date<GETDATE()+10

thanks for that. I don't know why I was stuck on the between factor. you guys gave me a lot of info and I appreciate it. I will use this info for the future as well.

Thanks again guys

 

[Olaf Doschke]

OK, good.

Now the final thing to understand is that in all but the one millisecond at midnight of a day GETDATE()+10 is after the midnight in 10 days, so the day in 10 days (stored with midnight time portion) is included in the result data. Just a consequence of thinking in datetimes. If this function would return dates only, the < operator would exclude that last day. Since you seldom will work midnight and even if, catch exactly that point, GETDATE()+10 might be 1 day off of what you want. If you, for example, would like an overview about a week starting today you'd query due_date<GETDATE()+6 only, not +7, because +7 would include today's weekday twice, today and in a week. That only changes if what is stored in due_date would contain the time, then to count in all datetimes after midnight of the same day means to calculate the sharp endpoint of midnight of the date you don't want anymore and exclude that by using < instead of <=. That's the kind of logical thoughts you need to have about datetimes. It depends both on what is stored in columns and what you define as belonging to a day/date.

Bye, Olaf.