Selection of Records - date data. DATEDIFF

SELECT show_name, show_address FROM show -----------------------------------------------

show_name show_address Dubbo 23 Wingewarra St, Dubbo Young 13 Cherry Lane, Young Castle Hill Showground Rd, Castle Hill Royal Easter PO Box 13, GPO Sydney Dubbo 17 Fitzroy St, Dubbo

Hi I'm trying to select Dubbo at 'Wingewarra St' and Castel Hill and list the number of days between them

Dubbo at 'Wingewarra St' was held on 05/ July / 1995 and Castle Hill was held on 04/ May/ 1996 and the result should be 304 days

and here is the show table:

SELECT *

FROM show --------------------------------------------------------- show_name show_held show_address Dubbo 1995-07-05 23 Wingewarra St, Dubbo Young 1995-09-13 13 Cherry Lane, Young Castle Hill 1996-05-04 Showground Rd, Castle Hill Royal Easter 1996-04-21 PO Box 13, GPO Sydney Dubbo 1996-07-01 17 Fitzroy St, Dubbo


and Here is the code that I wrote:

SELECT		DATENAME (DAY, show_held.CastleHill) - DATENAME (DAY, show_held.Dubbo) AS number_of_days_between_show
FROM		show
WHERE		show.show_name = 'Dubbo' AND show.show_address LIKE 'Wingewarra St'

-----------------------------------------------------
Msg 4104, Level 16, State 1, Line 24
The multi-part identifier "show_held.CastleHill" could not be bound.
Msg 4104, Level 16, State 1, Line 24
The multi-part identifier "show_held.Dubbo" could not be bound.

I have no idea how to only select the day for Castle Hill and subtract the day of Dubbo that was held at Wingewarra St

Please help thank you



  • Edited by H.Kim Sunday, September 06, 2015 2:56 AM
September 5th, 2015 12:18am

The result was '0'

I guess the show_names, Dubbo and Castle Hill are not assigned correctly

why does your code give me 0?

and how do I select Dubbo that is held at 'Wingewarra' and Castle Hill from show_name

thank you


  • Edited by H.Kim Sunday, September 06, 2015 3:01 AM
Free Windows Admin Tool Kit Click here and download it now
September 5th, 2015 11:31pm

Try

SELECT DATEDIFF(day,sh.show_held,sh1.show_held) AS number_of_days_between_shows,

sh.show_name, sh.show_address, sh1.show_name as secondShow, sh1.show_address as SecondShowAddress FROM show sh, show sh1 WHERE (sh.show_name = 'Dubbo' and sh.show_address like '23 Wingewarra St%')

and sh1.show_name = 'Castle Hill'

September 6th, 2015 3:43am

I think there is a typo in this line: DATEDIFF(day,sh.show_held),sh1.show_held)

and the result didn't show anything..

Free Windows Admin Tool Kit Click here and download it now
September 7th, 2015 9:35am

thank you! this did work but why do I have to declare a show table and insert into show values when I already have a show table?
September 7th, 2015 9:36am

thank you! this did work but why do I have to declare a show table and insert into show values when I already have a show table?

Don't declare anything.

Just replace @show by show as follows:

SELECT		DATEDIFF(day,min(show_held),max(show_held)) AS number_of_days_between_show
FROM		show
WHERE		(show_name = 'Dubbo' and show_address like '23  Wingewarra St%') or show_name = 'Castle Hill' 

Free Windows Admin Tool Kit Click here and download it now
September 7th, 2015 9:45am

You need to use your own table. If both my and Bermi queries don't return results in means the show name is not Dubbo or show_address is something different. Please re-check your data.
September 7th, 2015 2:19pm

the result is still 0 without this:

declare @show table (show_name nvarchar(20),     show_held  date,   show_address nvarchar(50))

insert into @show values
('Dubbo'	   ,   '1995-07-05' ,   '23  Wingewarra St, Dubbo'),
('Young'	   ,   '1995-09-13' ,   '13  Cherry Lane, Young'),
('Castle Hill' ,  '1996-05-04'  , 'Showground Rd, Castle Hill'),
('Royal Easter',  '1996-04-21'  ,  'PO Box 13, GPO Sydney'),
('Dubbo'	   ,   '1996-07-01' ,   '17 Fitzroy St, Dubbo')

 and I still don't know why. it only works with the code above

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 1:37am

Bermi's query did work but only when I declare @show table and so on but without @show table, it doesn't work

and I've tried to figure out what is wrong in your query but I still don't understand it


  • Edited by H.Kim 42 minutes ago
September 8th, 2015 1:40am

Bermi's query did work but only when I declare @show table and so on but without @show table, it doesn't work

and I've tried to figure out what is wrong in your query but I still don't understand it


Could you post the result of the below query?

SELECT		*
FROM		show
WHERE		(show_name = 'Dubbo' and show_address like '23  Wingewarra St%') or show_name = 'Castle Hill' 
I suspect that the show_held dates are the same.

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 3:18am

Bermi's query did work but only when I declare @show table and so on but without @show table, it doesn't work

and I've tried to figure out what is wrong in your query but I still don't understand it


  • Edited by H.Kim Tuesday, September 08, 2015 6:20 AM
September 8th, 2015 5:37am

Hello Kim,

 Use below code, I am getting 304 Days

create table #tmp(show_name      varchar(100), show_held datetime,show_address varchar(1000) )

Insert into #tmp
select 'Dubbo'       ,'1995-07-05'  ,  '23  Wingewarra St, Dubbo'  union
select 'Young'       ,'1995-09-13'  , '13  Cherry Lane, Young'   union
select 'Castle Hill'  , '1996-05-04' ,   'Showground Rd, Castle Hill'  union
select 'Royal Easter' , '1996-04-21' ,   'PO Box 13, GPO Sydney'   union
select 'Dubbo'       ,'1996-07-01'  ,  '17 Fitzroy St, Dubbo'  

select DATEDIFF(day,(
select show_held from #tmp where show_address like '%Wingewarra St%' and show_name ='Dubbo'),
(select show_held from #tmp where show_address like '%Castle Hill%' and show_name ='Castle Hill'))

"Please mark as answer" if its helped you

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 5:40am

SELECT		*
FROM		show
WHERE		(show_name = 'Dubbo' and show_address like '23  Wingewarra St%') or show_name = 'Castle Hill' 

-----------------------------------------------------------
show_id    show_name    show_held                  show_address
03	   Castle Hill	1996-05-04 00:00:00.000	   Showground Rd, Castle Hill
here is the result
September 9th, 2015 1:40am

thank you so much
Free Windows Admin Tool Kit Click here and download it now
September 9th, 2015 1:41am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics