Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rwaldron
Posting Yak Master
131 Posts |
 Posted - 2008-07-02 : 10:43:37
|
| Hi all,I have an SQL Server linked to a Btrieve server.I want to query based on todays date.However in Btrieve there is a column called say inshopdate....Some entries in this column have the date as 00/00/0000ie: there is no date yet.I cannot change the data in this table but I am assuming that thiscolumn should be Null. The data type in Btrive for this table is of type DATE.My problem is that When I query form sql to betrieve I want say a weeks worth of dataso in btrieve this is WHERE inshopdate >= curdat() -7.My problem is that when sql hits a date of type 00/00/0000 the query falls over(and rightly so)saying data in the column inshopdate is not of type datetime.My question is how do I work around these dates so that my query finishes..Would I need to do a make null where inshopdate = 00/00/0000or where inshopdate is not date ?Any ideas please,Ray.. |
|
|
ganeshkumar08
Posting Yak Master
187 Posts |
Posted - 2008-07-02 : 10:53:36
|
| Update 00/00/0000 to 01/01/9999, go for the further process |
 |
|
|
rwaldron
Posting Yak Master
131 Posts |
Posted - 2008-07-02 : 11:00:49
|
| Hi and thx for the reply.I don't know what you mean by Update 00/00/0000 to 01/01/9999, go for the further processCould you please explainThxRay.. |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-07-02 : 11:22:26
|
| Can you have a view created on the BTrieve side that aliases the 00/00/000 values to a valid SQL Server datetime value? |
|
|
|
rwaldron
Posting Yak Master
131 Posts |
Posted - 2008-07-02 : 11:30:34
|
| Hiya,I cany really touch the Btrieve side at all its not my data.The SQL side is mine..Any other help please |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-07-02 : 11:35:03
|
| OK. when you select the data from the linked server, do the 00/00/0000 values come through (i.e. when you do not attempt any comparisons against them)? If so, can you cast or convert these values to varchar(10)? |
|
|
|
rwaldron
Posting Yak Master
131 Posts |
Posted - 2008-07-02 : 12:35:28
|
| Hiya and thx for your help.In Btrieve I know order 361019 has inworkshopdate as 00/00/0000.If I run below query with no comparisons but where refnumber = 361019SQL gives me back the error."Conversion failed because the data valus overflowed the datatype used by the provider"select *FROM (SELECT RefNumber, InWorkShopDate, InWorkShopTime FROM OPENQUERY(sbase_live_link, 'SELECT refnumber,InWorkShopDate,InWorkShopTime FROM JOBSE where refnumber = 361019 order by InWorkShopTime desc ') AS derivedtbl_1) AS JOBSE LEFT OUTER JOIN (SELECT RefNumber, Invoice_Text FROM OPENQUERY(sbase_live_link, 'SELECT RefNumber,Invoice_Text FROM JOBNOTES ') AS derivedtbl_1_1) AS JOBNOTES ON JOBSE.RefNumber = JOBNOTES.RefNumberMy Working Solution is below...The problem is that it takes over 50 Minutes to run !!!!!!!This is where I check for 00/00/0000 and if found make null.My sql query finishes.The dates in SQL don't show up as Null but 0000-00-00Work around so far is below but it takes too longFROM (SELECT RefNumber, InWorkShopDate, InWorkShopTime FROM OPENQUERY(sbase_live_link, 'SELECT refnumber,Case When InWorkShopDate like ''00/00/0000'' then null else Convert(jobse.InWorkShopDate, SQL_VARCHAR)END InWorkShopDate,Convert(jobse.InWorkShopTime, SQL_VARCHAR)AS InWorkShopTime FROM JOBSE where inworkshopdate >= curdate()- 4 order by InWorkShopDate desc ') AS derivedtbl_1) AS JOBSE LEFT OUTER JOIN (SELECT RefNumber, Invoice_Text FROM OPENQUERY(sbase_live_link, 'SELECT RefNumber,Invoice_Text FROM JOBNOTES ') AS derivedtbl_1_1) AS JOBNOTES ON JOBSE.RefNumber = JOBNOTES.RefNumber |
|
|
|
|
|
|
|
|
00/00/0000 Data type Date ?Make Null?