Ignoring time stamp in my date parameter

Hi,

I'm pretty new at this, writing SQL and reporting services. I created a report with a date parameter. I need the report to ignore the timestamp. My @.Startdate is fine because the timestamps is at 12:00:00AM but my @.EndDate also has this timestamp. I need to pull all the data up to the end date the user enters without taking the timestamp into consideration.

If someone can help me out with, I would greatly appreciate it.

Thanks,

Hello,

If I understand correctly, you want to include the date that your user selects in your results. The problem is that when a parameter is used, it assumes midnight, so any values that are on that day but have a time other than midnight will not be included. In order to fix this, just add one day to your EndDate parameter.

In your SQL query, add this:

... where DateField >= @.StartDate and DateField < dateadd(d, 1, @.EndDate)

Hope this helps.

Jarret

|||

You got it! I've been adding one day when I enter the dates when I test the report. I don't know why I did not think of just adding one day in the code. I guess I was thinking too hard trying to completely ignore the timestamp.

Anyways, I think this will work with the users. THey will definitely be happy that they do not have to type in the timestamp when they run their reports.

Thank you so much!!

|||

Glad I could help!

Jarret

Ignoring NULL values in LOOKUP transformation.

Hi,

Can you please tell me the way to configure the LOOK UP transformation so that it will ignore all the null values ? I want to configure a Look up component for the column "Col1" as follows

All the NULL values of Col1 should not be considered for look-up process. They should be passed to the downstream component as valid rows.

All NOT NULL values of Col1 should be processed by the Look up component.

If there is no matching value present for any NOT NULL value of Col1 then it should be directed to error output.

Regards,

Gopi

Use a conditional split to direct the not nulls to the lookup(!ISNULL(Col1)). use a union to combine the results from the lookup with the records where Col1=NULL from the other conditional split path. set your error on the lookup to redirect rows to handle the records that have no match.

Frank

|||

Frank,

Thank you for your reply.

But, Is there any way to avoid conditional split ? I would like to configure Look up component itself.

Cheers,

Gopi

|||

If you pass the NULL Col1 records to the lookup, you would need an entry in the lookup table to handle the NULL. otherwise, it will fail the lookup and end up in the error table along with the not NULL Col1 records that failed the lookup.

Frank

Ignoring Noise words in SQL 2005 Full Text Search

To ignore the noise words in the query microsoft remommends to execute the following statements, by which we can take advantage of the new transformation of noise words in CONTAINS queries:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'transform noise words', 1
GO
RECONFIGURE
GO

But this never works. Can any one please suggest how to make this work.(without modifying the full text text file)

Did you restart the service, this is a non-runtime value, so you will have to restart the service to apply the change.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I had restarted the Full Text Index Service, but still it did not work

Also tried re-starting SQL Service, still it did not work.

Ignoring Noise words in SQL 2005 Full Text Search

To ignore the noise words in the query microsoft remommends to execute the following statements, by which we can take advantage of the new transformation of noise words in CONTAINS queries:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'transform noise words', 1
GO
RECONFIGURE
GO

But this never works. Can any one please suggest how to make this work.(without modifying the full text text file)

Did you restart the service, this is a non-runtime value, so you will have to restart the service to apply the change.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I had restarted the Full Text Index Service, but still it did not work

Also tried re-starting SQL Service, still it did not work.

Ignoring Noise words in SQL 2005 Full Text Search

To ignore the noise words in the query microsoft remommends to execute the following statements, by which we can take advantage of the new transformation of noise words in CONTAINS queries:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'transform noise words', 1
GO
RECONFIGURE
GO

But this never works. Can any one please suggest how to make this work.(without modifying the full text text file)

Did you restart the service, this is a non-runtime value, so you will have to restart the service to apply the change.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I had restarted the Full Text Index Service, but still it did not work

Also tried re-starting SQL Service, still it did not work.