Troublesome Date Data 
Author Message
 Troublesome Date Data

Hi All,

I am running a query via SQL server, which is looking at Index Server data:

------------------------------------------
SELECT DocKeywords,Vpath
FROM OPENQUERY(Filesystem,
'SELECT DocKeywords,Vpath FROM scope(''DEEP TRAVERSAL
OF "/committeeorig" '') ')
DerivedTable1
WHERE CONVERT(DATETIME,DocKeywords,103) BETWEEN '01/01/1998' AND
'31/12/2003'
ORDER BY CONVERT(DATETIME,DocKeywords,103) DESC
------------------------------------------

The data I am querying has a date stored in the DocKeywords property for the
MS Word documents, The query works great, as long as all the dates are
present and correct, but...some of the DocKeywords fields are empty, some
are null, and some are empty. Also, I beleieve some are incorrectly
formatted dates. If this is the case, I recieve the following error:

------------------------------------------
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
------------------------------------------

I know that the problem lies with the WHERE clause, and I know why this is
happening - I just need some way of working round this without updating my
data - Perhaps an IF statement (TSQL) - I dont know TSQL, someone mentioned
it, perhaps you know the syntax? FOr the purposes of this query, i dont
actually care about the files with missing info, its just that they happen
to be stored in the same place as the data I do care about.

BTW: I guess this problem would also be true for any table containing
missing/incorrect date data - So the query could easily also be:
(Excluding my ORDER BY for simplicity sake)

------------------------------------------
SELECT Column1,Column2 FROM Table1
WHERE CONVERT(DATETIME,Column1,103) BETWEEN '01/01/1998' AND '31/12/2003'
------------------------------------------

...Column1 being the place where the troublesome date data is held.

Sorry for the long post, thanks for listening! :)

Simon.

--
Simon Harris

To email me take away the "total"
Please reply to group for the benefit of all



Tue, 27 Sep 2005 14:38:57 GMT
 Troublesome Date Data

If you are using SQL 2K, try isdate() function to check if date data is
valid before converting into any other format.
i.e.
where case when isdate(c.n_dt)=1 then c.n_dt else '000101' end  between
'020101' AND '031231'

However for data comparasion purpose, my suggestion is to use convert
function with style 112.

HTH
Falik


Quote:
> Hi All,

> I am running a query via SQL server, which is looking at Index Server
data:

> ------------------------------------------
> SELECT DocKeywords,Vpath
> FROM OPENQUERY(Filesystem,
> 'SELECT DocKeywords,Vpath FROM scope(''DEEP TRAVERSAL
> OF "/committeeorig" '') ')
> DerivedTable1
> WHERE CONVERT(DATETIME,DocKeywords,103) BETWEEN '01/01/1998' AND
> '31/12/2003'
> ORDER BY CONVERT(DATETIME,DocKeywords,103) DESC
> ------------------------------------------

> The data I am querying has a date stored in the DocKeywords property for
the
> MS Word documents, The query works great, as long as all the dates are
> present and correct, but...some of the DocKeywords fields are empty, some
> are null, and some are empty. Also, I beleieve some are incorrectly
> formatted dates. If this is the case, I recieve the following error:

> ------------------------------------------
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> ------------------------------------------

> I know that the problem lies with the WHERE clause, and I know why this is
> happening - I just need some way of working round this without updating my
> data - Perhaps an IF statement (TSQL) - I dont know TSQL, someone
mentioned
> it, perhaps you know the syntax? FOr the purposes of this query, i dont
> actually care about the files with missing info, its just that they happen
> to be stored in the same place as the data I do care about.

> BTW: I guess this problem would also be true for any table containing
> missing/incorrect date data - So the query could easily also be:
> (Excluding my ORDER BY for simplicity sake)

> ------------------------------------------
> SELECT Column1,Column2 FROM Table1
> WHERE CONVERT(DATETIME,Column1,103) BETWEEN '01/01/1998' AND '31/12/2003'
> ------------------------------------------

> ...Column1 being the place where the troublesome date data is held.

> Sorry for the long post, thanks for listening! :)

> Simon.

> --
> Simon Harris

> To email me take away the "total"
> Please reply to group for the benefit of all



Tue, 27 Sep 2005 15:16:56 GMT
 Troublesome Date Data
Hi Falik,

Thanks for your reply, I have the following query, based on your idea - I
think i have understood your syntax correctly? Still seems to throw the same
error.

SELECT DocKeywords,Vpath
FROM OPENQUERY(Filesystem,
'SELECT DocKeywords,Vpath FROM scope(''DEEP TRAVERSAL
OF "/committeeorig/agendas" '' ')
DerivedTable1
WHERE
CASE WHEN IsDate(CONVERT(DATETIME,DocKeywords,103))=1 THEN
CONVERT(DATETIME,DocKeywords,103) ELSE '01/01/1998' END
BETWEEN '01/01/1998' AND '31/12/1998'
ORDER BY CONVERT(DATETIME,DocKeywords,103) DESC

Anyone got any further ideas?

Thanks,
Simon.



Wed, 28 Sep 2005 04:17:05 GMT
 Troublesome Date Data
I just added 'SET DATEFORMAT DMY' to the uery and it works a treat.

Falik - Thanks very much for your help - This query is for a project thats
already over due - Life saver! :)



Wed, 28 Sep 2005 05:00:36 GMT
 Troublesome Date Data
You are wrong to use the logic suggested by Falik. The
idea is you need to first check the data if it is in date
format or not. If not then use a date., but you are
converting data to date format and then checking if the
data is dataformat or not.

Hope it helps you.

Rabindra Das

Quote:
>-----Original Message-----
>Hi Falik,

>Thanks for your reply, I have the following query, based
on your idea - I
>think i have understood your syntax correctly? Still

seems to throw the same
Quote:
>error.

>SELECT DocKeywords,Vpath
>FROM OPENQUERY(Filesystem,
>'SELECT DocKeywords,Vpath FROM scope(''DEEP TRAVERSAL
>OF "/committeeorig/agendas" '' ')
>DerivedTable1
>WHERE
>CASE WHEN IsDate(CONVERT(DATETIME,DocKeywords,103))=1
THEN
>CONVERT(DATETIME,DocKeywords,103) ELSE '01/01/1998' END
>BETWEEN '01/01/1998' AND '31/12/1998'
>ORDER BY CONVERT(DATETIME,DocKeywords,103) DESC

>Anyone got any further ideas?

>Thanks,
>Simon.

>.



Thu, 29 Sep 2005 16:09:51 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. SQL: Select some data with date criteria, but only by Date part of date field

2. Help With Troublesome Join

3. Troublesome handling of dropped connection

4. troublesome inputs

5. FW: Troublesome SQL

6. troublesome inputs

7. Troublesome Trigger

8. Troublesome DB install.

9. Are duplicated OIDs troublesome?

10. Troublesome one-to-many links

11. Troublesome SQL

12. Troublesome write locks


 
Powered by phpBB® Forum Software