April 3, 2012 at 2:03 pm
lukebaker (4/3/2012)
I'm using visual studio 2010 with sql server 2008 express this might be why??
It is certainly a reason why your results do not coincide with SQL Server, but it still means someone has already defined a format for the data. If you have not, then we are not getting all of the information.
Jared
CE - Microsoft
April 3, 2012 at 2:08 pm
Below are my create table, insert into (5 of each) and the create view statements. Ive also provided a database diagram to give more idea. Hope this helps
CREATE TABLE staff(
staff_No numeric(5) not null,
first_Name varchar(15) not null,
last_Name varchar(15) not null,
username varchar(20) not null,
password varchar(20) not null,
DOB date not null,
qualified_Unqualified varchar(1) not null,
emailAddress varchar(35))
ALTER TABLE staff ADD CONSTRAINT PK_Staff PRIMARY KEY(staff_No);
/*Create training table*/
/* four constraints are declared */
/*three are not null constraints and the other is a primary key constraint*/
CREATE TABLE training(
training_Nonumeric(5)not null,
training_Typevarchar(20)not null,
completed_Date date,
expiry_Datedate,
qualified_Unqualifiedvarchar(1) not null)
ALTER TABLE training ADD CONSTRAINT PK_training PRIMARY KEY(training_No);
/*Create staffTraining table*/
/* six constraints are declared */
/*three are not null constraints, two is foreign key and the one is primary key constraint*/
CREATE TABLE staffTraining(
STIDnumeric(5) not null,
staff_Nonumeric(5) not null,
training_Nonumeric(5)not null)
ALTER TABLE staffTraining ADD CONSTRAINT PK_staffTraining PRIMARY KEY(STID);
ALTER TABLE staffTraining ADD CONSTRAINT FK_staff_No FOREIGN KEY(staff_No) REFERENCES staff(staff_No);
ALTER TABLE staffTraining ADD CONSTRAINT FK_training_No FOREIGN KEY(training_No) REFERENCES training(training_No);
/*insert into staff*/
insert into staff values (1001,'Sarah','Jones','Jones1001','password', '03/22/1989','Q','SJones@hotmail.com')
insert into staff values (1002,'Michelle','Barney','Barney1002','password', '07/02/1986','Q','Barney@hotmail.com')
insert into staff values (1003,'Rachel','King','King1003','password', '03/05/1966','Q','RKing@hotmail.com')
insert into staff values (1004,'Robert','Merrel','Merrel1004','password', '12/10/1978','U','RMerrel@hotmail.com')
insert into staff values (1005,'Lucy','Steele','Steele1005','password', '12/29/1986','U','LSteel@hotmail.co.uk')
/*insert into training */
insert into training values (1001,'PIN','12/09/2011','12/09/2012','Q')
insert into training values (1002,'BloodTransfusion1','11/10/2011','11/10/2012','Q')
insert into training values (1003,'BloodTransfusion2','01/05/2012','12/09/2013','Q')
insert into training values (1004,'IV','10/22/2010','10/22/2013','Q')
insert into training values (1005,'Epidural','12/10/2010','12/10/2013','Q')
/*insert into staffTraining */
insert into staffTraining values(1001,1001,1001)
insert into staffTraining values(1002,1001,1002)
insert into staffTraining values(1003,1001,1003)
insert into staffTraining values(1004,1001,1004)
insert into staffTraining values(1005,1001,1005)
/*v_staffTrainingDetails View*/
CREATE VIEW v_stafftrainingDetails
AS
SELECT training.training_No, training.training_Type, training.completed_Date, training.expiry_Date, training.qualified_Unqualified, staff.first_Name, staff.last_Name,
staff.staff_No
FROM training, staff, staffTraining
WHERE
training.training_No = staffTraining.training_No
AND
staffTraining.staff_No = staff.staff_No
April 3, 2012 at 2:13 pm
Also I have just realised my data in the tables is MM/DD/YYYY where when i just did
Select GETDATE()
the results were
03/04/2012 21:11:04
which is
DD/MM/YYYY
April 3, 2012 at 2:16 pm
In your sample data, I see no dates in expiry_date that are between today and 60 days from now.
April 3, 2012 at 2:25 pm
SOLVED!!
I have reformated the dates in my table and used the below query and it worked 🙂
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))#
Thanks all so much for your help and sorry if I was a pain!!
April 3, 2012 at 2:58 pm
lukebaker (4/3/2012)
SOLVED!!I have reformated the dates in my table and used the below query and it worked 🙂
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))#
Thanks all so much for your help and sorry if I was a pain!!
using WHERE expiry_Date BETWEEN DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd, 60, GETDATE())) + 1, 0)
will clean up your where clause a little and i believe give you the same results.
EDIT: added code tags
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 3, 2012 at 3:02 pm
capn.hector (4/3/2012)
lukebaker (4/3/2012)
SOLVED!!I have reformated the dates in my table and used the below query and it worked 🙂
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))#
Thanks all so much for your help and sorry if I was a pain!!
using WHERE expiry_Date BETWEEN DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd, 60, GETDATE())) + 1, 0)
will clean up your where clause a little and i believe give you the same results.
Actually, not. If you use BETWEEN (which can be used since this is SQL Server 2008 and expiry_date is using the date data type) you need to take out the + 1 from the computation for the upper end. BETWEEN is inclusive, the code as currently written excludes the upper end.
April 3, 2012 at 4:22 pm
Lynn Pettis (4/3/2012)
capn.hector (4/3/2012)
lukebaker (4/3/2012)
SOLVED!!I have reformated the dates in my table and used the below query and it worked 🙂
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))#
Thanks all so much for your help and sorry if I was a pain!!
using
WHERE expiry_Date BETWEEN DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND DATEADD(dd, DATEDIFF(dd, 0, DATEADD(dd, 60, GETDATE())) + 1, 0)will clean up your where clause a little and i believe give you the same results.
Actually, not. If you use BETWEEN (which can be used since this is SQL Server 2008 and expiry_date is using the date data type) you need to take out the + 1 from the computation for the upper end. BETWEEN is inclusive, the code as currently written excludes the upper end.
yep. missed the + 1 when i said should give the same results.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply