April 3, 2012 at 12:56 pm
Not sure what you mean from now to below?
April 3, 2012 at 1:02 pm
So any date from now up until 60 days. 1 to 60 days from now so from today (04/03/2012) to (06/02/2012) and display all data inbetween those 2 dates from the expiry date column
April 3, 2012 at 1:02 pm
Also, since you are new here, you will get much better answers to your questions if you post the DDL (CREATE TABLE statements) for the table(s), provide sample data (a series of INSERT INTO statements) the the table(s), expected results based on the sample data.
Please take the time to read the first article I reference below in my signature block regarding "Asking for help'" Follow the instructions in that article regarding what and how to post the information needed to help you. Doing this will get you better answers and test code.
April 3, 2012 at 1:03 pm
lukebaker (4/3/2012)
So any date from now up until 60 days. 1 to 60 days from now so from today (04/03/2012) to (06/02/2012) and display all data inbetween those 2 dates from the expiry date column
Need to be sure that this includes 6/2/2012. Also, do these dates have a time component?
April 3, 2012 at 1:07 pm
it must include 06/02/2012 aswell so im presuming a <= sign somewhere. And no they do not have time component. I will make sure I read that paper and make sure in the future I will be more detailed about my questioning. Sorry about the lack of detail as im just in a bit of rush to finish this project, however it hasnt paid of here.
April 3, 2012 at 1:08 pm
Here is a possible where clause that may meet your requirements.
WHERE
EXPIRE_DATE >= dateadd(dd, datediff(dd, 0, getdate()), 0) and
EXPIRE_DATE < dateadd(dd, datediff(dd, 0, dateadd(dd, 60, getdate())) + 1, 0)
April 3, 2012 at 1:15 pm
This is what I tried
SELECT *
FROM v_stafftrainingDetails
WHERE (expiry_Date >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) AND (expiry_Date < DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd, 60, GETDATE())) + 1, 0))
and these were the results which im afriad are incorrect
1011SafeGuardingAdults12/04/201112/04/2012QSarahJones1001
1011SafeGuardingAdults12/04/201112/04/2012QMichelleBarney1002
1011SafeGuardingAdults12/04/201112/04/2012QRachelKing1003
1011SafeGuardingAdults12/04/201112/04/2012QDonnaBaker1006
1011SafeGuardingAdults12/04/201112/04/2012QHelenPowell1007
1011SafeGuardingAdults12/04/201112/04/2012QLouiseBridges1009
1011SafeGuardingAdults12/04/201112/04/2012QLornaPhelps1010
Could it have something to do with because I'm in the uk and the dateformat is different??
April 3, 2012 at 1:23 pm
Absolutely no idea. I have nothing with which to test against, so I can't tell you. Plus, if the dates are stored as DATETIME datatype, it doesn't matter if you are in the UK or the US. Dates are stored the same way.
Please read the article I told you to read in an earlier post. Help us help you.
April 3, 2012 at 1:29 pm
Run this and post the results:
SELECT TOP 10 expiry_date, DATEADD(d,1,expiry_date)
FROM v_stafftrainingDetails
and this:
SELECT GETDATE()
Jared
CE - Microsoft
April 3, 2012 at 1:36 pm
Results are -
expiry_Date Expr1
09/12/201210/12/2012
10/11/201211/11/2012
09/12/201310/12/2013
22/10/201323/10/2013
10/12/201311/12/2013
29/08/201330/08/2013
11/01/201212/01/2012
30/10/201131/10/2011
28/10/201029/10/2010
09/02/201210/02/2012
April 3, 2012 at 1:41 pm
How are you getting these results? These are not SQL results, they are formatted... Where are you running your query?
Jared
CE - Microsoft
April 3, 2012 at 1:44 pm
lukebaker (4/3/2012)
Results are -
expiry_Date Expr1
09/12/201210/12/2012
10/11/201211/11/2012
09/12/201310/12/2013
22/10/201323/10/2013
10/12/201311/12/2013
29/08/201330/08/2013
11/01/201212/01/2012
30/10/201131/10/2011
28/10/201029/10/2010
09/02/201210/02/2012
Again, please read and follow the instructions provided in the first article I reference below in my signature block regarding asking for help. You are only giving us tiny little glimpses at what is going on. We can't see what you are doing, nor are we mind readers. Help us help you, give us what we need to create your environment (a small part of it, anyway) so we can write and test code to meet your requirements.
April 3, 2012 at 1:46 pm
I'm using visual studio 2010 with sql server 2008 express this might be why??
April 3, 2012 at 1:52 pm
Well, I ran the query that gave you incorrect results above and I get the right results. So, the problem is not the query, it is your data formatting or your data. Without you giving us the information we require, as Lynn has mentioned numerous times, we can't help you any further.
Jared
CE - Microsoft
April 3, 2012 at 2:01 pm
lukebaker (4/3/2012)
I'm using visual studio 2010 with sql server 2008 express this might be why??
No.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply