Inaccurate date displaying in query

Page 1 of 1

4 Replies - 625 Views - Last Post: 04 May 2011 - 06:18 AM

#1 samuraitux  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 58
  • Joined: 29-April 09

Inaccurate date displaying in query

Posted 28 April 2011 - 09:33 AM

I am hoping for from assistance with a query. I am able to get the time from a smalldatetime. However I am looking to just get the HH:MM from the datetime and I perfer that it is in 12 hour format not military time. I am posting the query and what the time field looks like. 
SELECT "pat"."name_display", "pat"."home_phone", convert(date,"appt"."start_datetime") AS 'date', convert(time,"appt"."start_datetime") AS 'time', "visit"."pat_acct_num", "res_primaryprac"."res_id", "probooking"."name", "probooking"."pro_id", "res_primaryprac"."name", "loc"."name"
 FROM   "prod"."dbo"."visitapptlist" "visitapptlist" 
 INNER JOIN "prod"."dbo"."resunit" "resunit" 
 INNER JOIN "prod"."dbo"."loc" "loc" ON "resunit"."resunit_id"="loc"."loc_id"
 INNER JOIN "prod"."dbo"."appt" "appt" ON "resunit"."resunit_id"="appt"."resunit_id" ON "visitapptlist"."appt_id"="appt"."appt_id"
 INNER JOIN "prod"."dbo"."visit" "visit" ON "visitapptlist"."visit_id"="visit"."visit_id" 
 INNER JOIN "prod"."dbo"."probooking" "probooking" ON "appt"."appt_id"="probooking"."appt_id" 
 INNER JOIN "prod"."dbo"."pat" "pat" ON "visit"."pat_id"="pat"."pat_id"
 LEFT OUTER JOIN "prod"."dbo"."res" "res_primaryprac" ON "probooking"."prim_pract_id"="res_primaryprac"."res_id"
 WHERE  ("appt"."start_datetime">={ts '2011-04-21 00:00:00'} AND "appt"."start_datetime"<{ts '2011-04-22 00:00:01'}) AND ("loc"."name"='CARDIOLOGY CLINIC' OR "loc"."name"='ENDOCRINE CLINIC' OR "loc"."name"='ENDOSCOPY SUITES' OR "loc"."name"='ORTHOPEDIC CLINIC')




07:30:00.0000000
07:30:00.0000000
08:00:00.0000000
08:00:00.0000000
08:15:00.0000000
08:15:00.0000000
08:15:00.0000000
08:30:00.0000000
08:30:00.0000000
08:30:00.0000000
08:45:00.0000000
08:45:00.0000000
09:00:00.0000000
09:00:00.0000000
09:15:00.0000000
09:15:00.0000000
09:15:00.0000000
09:30:00.0000000
09:30:00.0000000
09:30:00.0000000
09:45:00.0000000
09:45:00.0000000
09:45:00.0000000
10:00:00.0000000
10:30:00.0000000
10:45:00.0000000
11:00:00.0000000
12:15:00.0000000
12:30:00.0000000
12:45:00.0000000
12:45:00.0000000
12:45:00.0000000
13:00:00.0000000
13:00:00.0000000
13:00:00.0000000
13:00:00.0000000
13:00:00.0000000
13:15:00.0000000
13:15:00.0000000
13:15:00.0000000
13:30:00.0000000
13:30:00.0000000
13:30:00.0000000
13:30:00.0000000
13:45:00.0000000
13:45:00.0000000
14:00:00.0000000
14:00:00.0000000
14:15:00.0000000
14:15:00.0000000
14:30:00.0000000
14:30:00.0000000
14:30:00.0000000
14:45:00.0000000
14:45:00.0000000
15:00:00.0000000
15:00:00.0000000
15:15:00.0000000
15:15:00.0000000
15:30:00.0000000


Is This A Good Question/Topic? 0
  • +

Replies To: Inaccurate date displaying in query

#2 samuraitux  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 58
  • Joined: 29-April 09

Re: Inaccurate date displaying in query

Posted 28 April 2011 - 10:18 AM

Ok I was able to finally figure this thing out. What I had to do was use the following format: 

Select Ltrim(right(convert(char(19),getdate(),100),7))


What was confusing me was the getDate() piece. But what can be done is switch the getDate() function for your table name. Here is the full output of my query with the addition made ot it. 

SELECT "pat"."name_display", "pat"."home_phone", convert(date,"appt"."start_datetime") AS 'date', Ltrim(right(convert(char(19),"appt"."start_datetime", 100),7)) AS 'time', "visit"."pat_acct_num", "res_primaryprac"."res_id", "probooking"."name", "probooking"."pro_id", "res_primaryprac"."name", "loc"."name"
 FROM   "prod"."dbo"."visitapptlist" "visitapptlist" 
 INNER JOIN "prod"."dbo"."resunit" "resunit" 
 INNER JOIN "prod"."dbo"."loc" "loc" ON "resunit"."resunit_id"="loc"."loc_id"
 INNER JOIN "prod"."dbo"."appt" "appt" ON "resunit"."resunit_id"="appt"."resunit_id" ON "visitapptlist"."appt_id"="appt"."appt_id"
 INNER JOIN "prod"."dbo"."visit" "visit" ON "visitapptlist"."visit_id"="visit"."visit_id" 
 INNER JOIN "prod"."dbo"."probooking" "probooking" ON "appt"."appt_id"="probooking"."appt_id" 
 INNER JOIN "prod"."dbo"."pat" "pat" ON "visit"."pat_id"="pat"."pat_id"
 LEFT OUTER JOIN "prod"."dbo"."res" "res_primaryprac" ON "probooking"."prim_pract_id"="res_primaryprac"."res_id"
 WHERE  ("appt"."start_datetime">={ts '2011-04-21 00:00:00'} AND "appt"."start_datetime"<{ts '2011-04-22 00:00:01'}) AND ("loc"."name"='CARDIOLOGY CLINIC AT NGH' OR "loc"."name"='ENDOCRINE CLINIC AT NGH' OR "loc"."name"='ENDOSCOPY SUITES' OR "loc"."name"='ORTHOPEDIC CLINIC AT NGH')

Was This Post Helpful? 0
  • +
  • -

#3 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 172
  • View blog
  • Posts: 1,560
  • Joined: 17-April 07

Re: Inaccurate date displaying in query

Posted 04 May 2011 - 03:26 AM

i think you don't need to convert the date
seems it is in string type(varchar), then why would you convert it again a char

may i know what is the datatype for that field
convert(date,"appt"."start_datetime") AS 'date', Ltrim(right(convert(char(19),"appt"."start_datetime", 100),7)) AS 'time'


if the field is in date then why would you convert the first one,
if not(i.e) the field is in string format then why would you convert the second as char

please write down your field and it's datatype
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 4888
  • View blog
  • Posts: 11,282
  • Joined: 16-October 07

Re: Inaccurate date displaying in query

Posted 04 May 2011 - 04:06 AM

I'm afraid I'm still not sure what you're asking. I also don't know why you have all the quotes or how this could work:
INNER JOIN prod.dbo.appt appt 
	ON resunit.resunit_id=appt.resunit_id 
	ON visitapptlist.appt_id=appt.appt_id


Two ONs? This should crap out entirely. This also shouldn't really work: AS 'date'.

If I understand what I think you're after, I'd probably do it like:
SELECT pat.name_display,
		pat.home_phone,
		convert(varchar(10), appt.start_datetime, 101) AS [date], 
		select convert(char(5), appt.start_datetime, 8) AS [time], 
		visit.pat_acct_num, 
		res_primaryprac.res_id, 
		probooking.name, 
		probooking.pro_id, 
		res_primaryprac.name,
		loc.name
	FROM prod.dbo.visitapptlist visitapptlist 
		INNER JOIN prod.dbo.resunit resunit 
		INNER JOIN prod.dbo.loc loc 
			ON resunit.resunit_id=loc.loc_id
				AND loc.name IN ('CARDIOLOGY CLINIC AT NGH', 'ENDOCRINE CLINIC AT NGH', 'ENDOSCOPY SUITES', 'ORTHOPEDIC CLINIC AT NGH')
		INNER JOIN prod.dbo.appt appt 
			ON resunit.resunit_id=appt.resunit_id 
				AND visitapptlist.appt_id=appt.appt_id
				AND appt.start_datetime>={ts '2011-04-21 00:00:00'} 
				AND appt.start_datetime<{ts '2011-04-22 00:00:01'}
		INNER JOIN prod.dbo.visit visit 
			ON visitapptlist.visit_id=visit.visit_id 
		INNER JOIN prod.dbo.probooking probooking
			ON appt.appt_id=probooking.appt_id 
		INNER JOIN prod.dbo.pat pat
			ON visit.pat_id=pat.pat_id
		LEFT OUTER JOIN prod.dbo.res res_primaryprac 
			ON probooking.prim_pract_id=res_primaryprac.res_id


Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#5 samuraitux  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 58
  • Joined: 29-April 09

Re: Inaccurate date displaying in query

Posted 04 May 2011 - 06:18 AM

View Postbaavgai, on 04 May 2011 - 06:06 AM, said:

I'm afraid I'm still not sure what you're asking. I also don't know why you have all the quotes or how this could work:
INNER JOIN prod.dbo.appt appt 
	ON resunit.resunit_id=appt.resunit_id 
	ON visitapptlist.appt_id=appt.appt_id


Two ONs? This should crap out entirely. This also shouldn't really work: AS 'date'.

If I understand what I think you're after, I'd probably do it like:
SELECT pat.name_display,
		pat.home_phone,
		convert(varchar(10), appt.start_datetime, 101) AS [date], 
		select convert(char(5), appt.start_datetime, 8) AS [time], 
		visit.pat_acct_num, 
		res_primaryprac.res_id, 
		probooking.name, 
		probooking.pro_id, 
		res_primaryprac.name,
		loc.name
	FROM prod.dbo.visitapptlist visitapptlist 
		INNER JOIN prod.dbo.resunit resunit 
		INNER JOIN prod.dbo.loc loc 
			ON resunit.resunit_id=loc.loc_id
				AND loc.name IN ('CARDIOLOGY CLINIC AT NGH', 'ENDOCRINE CLINIC AT NGH', 'ENDOSCOPY SUITES', 'ORTHOPEDIC CLINIC AT NGH')
		INNER JOIN prod.dbo.appt appt 
			ON resunit.resunit_id=appt.resunit_id 
				AND visitapptlist.appt_id=appt.appt_id
				AND appt.start_datetime>={ts '2011-04-21 00:00:00'} 
				AND appt.start_datetime<{ts '2011-04-22 00:00:01'}
		INNER JOIN prod.dbo.visit visit 
			ON visitapptlist.visit_id=visit.visit_id 
		INNER JOIN prod.dbo.probooking probooking
			ON appt.appt_id=probooking.appt_id 
		INNER JOIN prod.dbo.pat pat
			ON visit.pat_id=pat.pat_id
		LEFT OUTER JOIN prod.dbo.res res_primaryprac 
			ON probooking.prim_pract_id=res_primaryprac.res_id


Hope this helps.


Thanks baavgai. I will work on cleaning up the query. Basically our PHS analyst created this query using I believe Crystal Reports then asked if we could script it so that it would run every day and send the results out to a call back service. So I am not sure why Crystal Reports put quotes around everything. But again thanks for the informtaion.
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1