Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

GetDate() function increment with +/- days as Integer

Posted on 2006-07-19
7
Medium Priority
?
927 Views
Last Modified: 2008-03-17
I have a program that needs to have records land in a certain column based of off a date.  The value is stored in a SQL database as:

2006-08-23 00:00:00:000


By using today's date withe the GetDate() function in my SQL select statement, it looks like this.

where due_date between GetDate()+34 and GetDate()+42


I've come to manually counting out the days and +34 equates to Aug 22 while +42 equates to Aug 30 according to my math.

So, with between I would want this to show me records with Aug 23 thru Aug 29 due dates.


This bucket (+34 thru +42) does show the record from Aug 23 = Good!



What I can't figure out is why previous bucket (between GetDate()+27 and GetDate()+35) also shows this record??

According to my calculation, +27 would be Aug 15 and +35 days would be Aug 23.  

Add the between logic and seems to me it should show records for Aug 16 thru Aug 22.


I'm thinking this may have to do with hours, minutes, seconds from the date field in the sql database and that falling in bucket for earlier than getdate() function run at the time of script(now for instance).

Any thoughts would be appreciated.

Thanks,
Jon

0
Comment
Question by:JMO9966
7 Comments
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17140245
BETWEEN:
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17140289
you're probably correct...

time is always part of a datetime so you have to factor it in to any range/equality tests you perfom with dates...

whilst in a single sql statement getdate() will resolve to the same time no matter howmany places its reference...

between individual statements the time will/may change...  its best to assign the "start" time to a variable and
then reference to that to ensure consisitency...

e.g.

declare @now datetime
set @now=getdate()
or set @now = convert(char(8),getdate(),112)   if you just want the start of the current day...  


select ...
   from ...
  where thedate between dateadd(d,+27,@now) and dateadd(d,35,@now)


select ...
   from ...
  where thedate between dateadd(d,+23,@now) and dateadd(d,26,@now)

0
 
LVL 7

Expert Comment

by:FDzjuba
ID: 17140301
use DATEADD function instead!

DATEADD(d,@days,getdate());
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17140305
Due_Date Between CAST(CONVERT(varchar(8),getdate(),113) as Datetime)+34  AND  CAST(CONVERT(varchar(8),getdate(),113) as Datetime)+42
0
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17140582
Unless I misunderstand the problem:
>>>According to my calculation, +27 would be Aug 15 and +35 days would be Aug 23.  
>>>Add the between logic and seems to me it should show records for Aug 16 thru Aug 22.
If your math show +27 as Aug 15 and +35 as Aug 23, then between will return true if the date tested is >=Aug 15 and <=Aug 23.

So both Aug 15 and Aug 23 will be a match. If you don't want that, make it +28 and +34
0
 

Author Comment

by:JMO9966
ID: 17142564
I tried some of the methods mentioned above with no luck, I did not try the cast(convert(varchar.......

Whether this is by design or not I'm not sure, but i did find the following scheme worked to get 7 days in each bucket starting with Wed through Tuesday since today is Wednesday.  

July 19th - 25th  = between GetDate()-1 and GetDate()+6
July 26th - Aug 1 = between GetDate()+6 and GetDate()+13
Aug 2 - Aug 8 = between GetDate()+13 and GetDate()+20
Aug 9 - Aug 15 = between GetDate()+20 and GetDate()+27
Aug 16 - Aug 22 = between GetDate()+27 and GetDate()+34
Aug 23 - Aug 29 = between GetDate()+34 and GetDate()+41

It appears the second date range in the between statement includes and "or equal to" operator while the beginning date does not from what I see.


Thanks guys,
Jon
 
0
 
LVL 26

Accepted Solution

by:
DireOrbAnt earned 750 total points
ID: 17142683
JMO9966,

Run this in Query Analyzer:
SELECT GetDate()+27, GetDate()+35

For me, it returns:
2006-08-15 19:43:30.133, 2006-08-23 19:43:30.133

So, your first post states that 2006-08-23 00:00:00:000 should NOT be in there.

Run this:
SELECT CASE WHEN '2006-08-23 00:00:00:000' BETWEEN GetDate()+34 and GetDate()+42 THEN 'Yes' ELSE 'No' END

You get a Yes (well I do on ET).

BETWEEN will include both sides. It does a >= and <=.

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question