DATEDIFF

Question

DATEDIFF

lukebaker

SSC Enthusiast

Points: 132
More actions
April 3, 2012 at 11:05 am

#271479

Hi im a novice with SQL and im trying to build a SQL query which will bring back all the data that the column dateTime (expiry_date) is within 2 months. Below is what im trying
Select * from v_staffTrainingDetails where ((DATEDIFF(MM, GETDATE(), expiry_Date) < 2

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Is there a question here?

< 2 will produce only one month data; it will exclude the second month. You may want to use <= 2. And DATEDIFF is the function to use for this. You may want to move the GETDATE() to the end; like this

select datediff(MM,expiry_date, getdate())

Anders Pedersen SSChampion Points: 11410 More actions · Answer 2

Without regard to optimization.... I would do it this way:

Select * from v_staffTrainingDetails where expiry_date >= DATEADD(mm,-2, getdate())

lukebaker SSC Enthusiast Points: 132 More actions · Answer 3

lukebaker

SSC Enthusiast

Points: 132

April 3, 2012 at 11:27 am

#1468375

Thanks ill give it a try

Jared SSC Guru Points: 61793 More actions · Answer 4

The reason that Anders' solution is better is because you are not running the DATEDIFF function on each row, but instead running it once on getdate() and then comparing that 1 date to each row.

Jared
CE - Microsoft

lukebaker SSC Enthusiast Points: 132 More actions · Answer 5

I am trying the below statement but seem to be getting data back which is longer than 2 months away?

SELECT training_No, training_Type, completed_Date, expiry_Date, qualified_Unqualified, first_Name, last_Name, staff_No

FROM v_stafftrainingDetails

WHERE (DATEDIFF(MM, expiry_Date, GETDATE()) <= 2)

I will try Anders way, thanks for your help

Select * from v_staffTrainingDetails where expiry_date >= DATEADD(mm,-2, getdate())

Jared SSC Guru Points: 61793 More actions · Answer 6

lukebaker (4/3/2012)
I am trying the below statement but seem to be getting data back which is longer than 2 months away?
SELECT training_No, training_Type, completed_Date, expiry_Date, qualified_Unqualified, first_Name, last_Name, staff_No
FROM v_stafftrainingDetails
WHERE (DATEDIFF(MM, expiry_Date, GETDATE()) <= 2)
I will try Anders way, thanks for your help

That's because in this code you are doing <= instead of >=...

Jared
CE - Microsoft

lukebaker SSC Enthusiast Points: 132 More actions · Answer 7

I am still retrieving data which is more than 2 months time ive tried both methods?

Michael L John One Orange Chip Points: 27055 More actions · Answer 8

Look up exactly what DATEDIFF does in Books online.

Then, look at the values of the expiry_date field.

Are there values in this field that are greater than today?

The value returned will be less than 0, so the condition <= 2 will give you more results that you need.

Maybe a second where clause? Use BETWEEN, maybe?

So, try

WHERE expiry_date BETWEEN 'starting date' AND getdate()

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

You would get better answers to your question if you posted more information. To be precise, you should post the DDL (CREATE TABLE statement(s)) for the table(s), sample data (INSERT INTO statements) for the table(s), and expected results based on the sample data.

For help on how to accomplish this, please read the first article I reference below in my signature block regarding "Asking for help." Follow the instructions in that article on what and how to post the information we need to help you and you will get much better answers plus tested code.

Jared SSC Guru Points: 61793 More actions · Answer 10

Let's start by going back... How do you define 2 months back? Beginning of month? 60 days? Please gice some sample dates and desired results for dates inside and outside of the scope.

Ex. Should 2/2/2012 be within the 2 months? If so, why? If not, why? We need your business rules here...

Jared
CE - Microsoft

lukebaker SSC Enthusiast Points: 132 More actions · Answer 11

Basically i am building a windows application using c# and i am trying to filter the data using an sql statement. This is being done with a drop down box which will display all the training which needs to be renewed. So I am trying to display all of the expiry_Dates inbetween now and 2 months time (when they expire). Does that make it more clear? sorry about more explanation it was hard to put

thanks

if todays date is 04/03/2012 , 03/04/2012(uk date format) then I would like it to view all the dates up until 06/03/2012, 03/06/2012 (uk date format)

Jared SSC Guru Points: 61793 More actions · Answer 12

lukebaker (4/3/2012)
Basically i am building a windows application using c# and i am trying to filter the data using an sql statement. This is being done with a drop down box which will display all the training which needs to be renewed. So I am trying to display all of the expiry_Dates inbetween now and 2 months time (when they expire). Does that make it more clear? sorry about more explanation it was hard to put
thanks

No, it does not. What exactly does 2 months mean? It could mean anything within 60 days, it could mean anything including and after 3/1/2012, it could mean anything including or after changing today's date from 4/3 to 2/3 and using that date. So, I will ask again... What does "within 2 months" mean and please prvide some sample data displaying what you want:

USE tempdb

CREATE TABLE #v_staffTrainingDetails (expiry_date datetime)

INSERT INTO #v_staffTrainingDetails

SELECT '2012-01-01'

UNION ALL

SELECT '2012-01-02'

UNION ALL

SELECT '2012-01-03'

UNION ALL

SELECT '2012-01-04'

UNION ALL

SELECT '2012-02-01'

UNION ALL

SELECT '2012-02-02'

UNION ALL

SELECT '2012-02-03'

UNION ALL

SELECT '2012-02-04'

UNION ALL

SELECT '2012-03-01'

UNION ALL

SELECT '2012-03-02'

UNION ALL

SELECT '2012-03-03'

UNION ALL

SELECT '2012-03-04'

SELECT *

FROM #v_staffTrainingDetails

The above generates sample code as you should provide when asking questions here. Now... The results are:

expiry_date

-----------------------

2012-01-01 00:00:00.000

2012-01-02 00:00:00.000

2012-01-03 00:00:00.000

2012-01-04 00:00:00.000

2012-02-01 00:00:00.000

2012-02-02 00:00:00.000

2012-02-03 00:00:00.000

2012-02-04 00:00:00.000

2012-03-01 00:00:00.000

2012-03-02 00:00:00.000

2012-03-03 00:00:00.000

2012-03-04 00:00:00.000

(12 row(s) affected)

So what EXACTLY should not be in the results and why?

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 13

Try this:

SELECT *

FROM #v_staffTrainingDetails

WHERE expiry_date >= DATEADD(d, 0, DATEDIFF(d,0,GETDATE()))

AND expiry_date < = DATEADD(mm, 2, DATEADD(d, 0, DATEDIFF(d,0,GETDATE())))

Jared
CE - Microsoft

lukebaker SSC Enthusiast Points: 132 More actions · Answer 14

After speaking to my client by 2 months they mean 60 days, below is some data from my view (v_stafftrainingdetails).

expiry_date

1009MandotoryC28/10/200928/10/2010QSarahJones1001

1009MandotoryC28/10/200928/10/2010QMichelleBarney1002

1009MandotoryC28/10/200928/10/2010QRachelKing1003

1009MandotoryC28/10/200928/10/2010QDonnaBaker1006

This is how the data looks, there is plenty more of it, so from this I would like to display all of the view where the expiry date is 60 days from now or below?