Re: Options when it comes to searching dates?
From: John Kane (jt-kane_at_comcast.net)
Date: 05/18/04
- Next message: Farhan Iqbal: "Ignored Words Problem"
- Previous message: John Kane: "Re: Full Text Service not starting from EM"
- In reply to: Raterus: "Re: Options when it comes to searching dates?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 17 May 2004 21:38:36 -0700
Raterus,
Sure, below is a create table for a temp table for inserting the output of
sp_help_jobhistory - note run_date:
create table #JobHistory (
instance_id int NOT NULL,
job_id uniqueidentifier NOT NULL,
job_name nvarchar(100) collate database_default not null,
step_id int NOT NULL,
step_name nvarchar(100) collate database_default not null,
sql_message_id int NOT NULL,
sql_severity int NOT NULL,
message nvarchar(1024) collate database_default null,
run_status int NOT NULL,
run_date int NOT NULL,
run_time int NOT NULL,
run_duration int NOT NULL,
operator_emailed sysname collate database_default null,
operator_netsent sysname collate database_default null,
operator_paged sysname collate database_default null,
retries_attempted int NOT NULL,
server sysname collate database_default not null
)
insert into #JobHistory exec msdb.dbo.sp_help_jobhistory @job_id =
@job_id, @step_id = 1, @mode = 'FULL'
declare @date int
declare @time int
select @message = message, @runstatus = run_status, @date = run_date,
@time = run_time
from #JobHistory
You if you had the above temp table FT-enabled you could execute the
following FT query:
select job_name from #JobHistory where contains(*, '"job step"') and
run_date between 20040515 and 20040517
My question for you is the column your date is stored in the same textual
column as your FT-enabled column? If so, and in order to do date-range
comparsions, you must parse this "textual" date format into a datatype that
allows valid range comparisons. For example what is the difference between
"foo" and "bar" expressed as a range of values? You must parse and then be
able to capture and maintain the textual dates as valid date/numeric values
inorder to do valid date range comparisions.
Regards,
John
"Raterus" <raterus@spam.org> wrote in message
news:e4nvv2COEHA.1644@TK2MSFTNGP09.phx.gbl...
Can you give me a little more detail about storing the dates as int's, or
just how I would use a full-text query to specify a range. In my
application I know the dates stored will be in this format "4/12/04", but
that could be changed, as it doesn't necessarily have to be stored like
that, just displayed like that.
"John Kane" <jt-kane@comcast.net> wrote in message
news:%23XComtCOEHA.204@TK2MSFTNGP10.phx.gbl...
> Raterus,
> Can I assume that you are referring to free-form date formats, such as
> "4/12/04", "May 11th, 2004", "February 12th", "2004/05/01", et al. and
then
> being able to take any two of these date formats and determine the
> difference in days and return all matching records within that date range?
> Correct?
>
> Assuming I'm correct in the above assumption, SQL FTS and Indexing Service
> cannot out-of-the-box provide this type of functionality. However, if
you're
> able to intelligently parse the textual data and then put the above date
> formats in SQL table columns define as int datatypes, then this is
possible.
> However, it does require a significant amount of programming to detect and
> parse all possible date formats and put them in the correct columns.
>
> So, while you are not dreaming, it is possible, but difficult.
> Regards,
> John
>
>
>
> "Raterus" <raterus@spam.org> wrote in message
> news:#a85eZCOEHA.2780@TK2MSFTNGP09.phx.gbl...
> Are there any options available in queries that are searching textual
dates
> that have been indexed? Perhaps the ability to specify a range and all
> matching records would be returned? Am I just dreaming? :-)
>
>
- Next message: Farhan Iqbal: "Ignored Words Problem"
- Previous message: John Kane: "Re: Full Text Service not starting from EM"
- In reply to: Raterus: "Re: Options when it comes to searching dates?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
