| Author |
Topic |
|
atran80
Starting Member
4 Posts |
 Posted - 2010-03-17 : 15:13:54
|
| Hello,I'm hoping someone out there can help me with the following: I'm trying to create a report in Crystal Report Writer that links two date fields in two different databases. One is a Datetime field {HOSPITAL_TRANS."TRANDATE"}, and the other is a date field {HOSPITAL_CENSUS."CENDATE"}. I don't care about the time, so I'm thinking it would be easiest to remove the time from the Hospital_TranDate data. Here's my query so far:SELECT HOSPITAL_TRANS."TRANDATE", HOSPITAL_TRANS."TRANCODE", HOSPITAL_TRANS."ACCTNO" FROM "PATIENT"."HOSPITAL_TRANS" HOSPITAL_TRANS, "PATIENT"."HOSPITAL_CENSUS" HOSPITAL_CENSUS WHERE HOSPITAL_TRANS."TRANDATE" = HOSPITAL_CENSUS."CENDATE" AND HOSPITAL_TRANS."ACCTNO" = '311323'When I run this query, I get no records presumably because the link between TranDate and CenDate is not working. Any advice? I'm completely new to SQL.Thanks, |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-17 : 15:32:41
|
try to convert both sides to ISO date format (yearmonthday):convert(varchar(10),HOSPITAL_TRANS.TRANDATE,112) = convert(varchar(10),HOSPITAL_CENSUS.CENDATE,112)
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-03-18 : 03:33:16
|
| dateadd(day,datediff(day,0,HOSPITAL_TRANS.TRANDATE),0) = HOSPITAL_CENSUS.CENDATEMadhivananFailing to plan is Planning to fail |
|
|
|
atran80
Starting Member
4 Posts |
Posted - 2010-03-18 : 13:00:39
|
| Thanks for both of your replies. I tried them both: When I used the convert line, it gave me an error "Missing Expression". Here's what it looked like:SELECT HOSPITAL_TRANS."TRANDATE", HOSPITAL_TRANS."TRANCODE", HOSPITAL_TRANS."AMOUNT", HOSPITAL_CENSUS."ACCTNO", HOSPITAL_CENSUS."CENDATE", HOSPITAL_CENSUS."ROOM", HOSPITAL_CENSUS."NURSEUNIT"FROM "PATIENT"."HOSPITAL_TRANS" HOSPITAL_TRANS, "PATIENT"."HOSPITAL_CENSUS" HOSPITAL_CENSUS WHERE CONVERT(VARCHAR(10),HOSPITAL_TRANS."TRANDATE",112) = CONVERT(VARCHAR(10),HOSPITAL_CENSUS."CENDATE",112) When I tried the "dateadd" line, it gave me an error that said "DateAdd: Invalid Identifier". Any more ideas? I'm using Crystal 8.5 if that's at all useful...Thanks in advance,Anh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-18 : 13:13:25
|
| are you using SQL Server as datasource?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
atran80
Starting Member
4 Posts |
Posted - 2010-03-18 : 13:17:50
|
| No, Oracle :P Am I in the wrong forum? I wasn't kidding when I said I was new to this. Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-18 : 13:20:42
|
| yup...you need to post it in Oracle forum. this is MS SQL Server forum. please try your luck at www.orafaq.com------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
atran80
Starting Member
4 Posts |
Posted - 2010-03-18 : 13:22:00
|
| How embarrassing. Thank you anyway! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-18 : 13:28:49
|
quote:
Originally posted by atran80
How embarrassing. Thank you anyway!
What? How did we embarrass you? Isnt it better to direct you to a relevant forum rather than giving a solution which might not work well in your case? What would be a greater embarrassment for you?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-19 : 04:27:03
|
No Visakh,he wants only to say that it was his bad.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-19 : 11:18:37
|
quote:
Originally posted by webfred
No Visakh,he wants only to say that it was his bad.
No, you're never too old to Yak'n'Roll if you're too young to die.
Oops Sorry... I think I misinterpreted...  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Link DateTime and Date Fields