Using LIKE with DateField - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using LIKE with DateField

Author

Topic

Jman0082
Starting Member

8 Posts

Posted - 2006-08-02 : 16:31:44

I want to create a stored procedure that returns all dates matching the prefix entered. I am appending a % onto the end of the prefix

Here is my current stored procedure


ALTER PROCEDURE dbo.GetProductionDates
	(
		@prefix as nvarchar(32) = NULL
	)
AS
	SET NOCOUNT ON;
SELECT Production_Date
FROM         [Production Calendar]
WHERE Production_Date LIKE @prefix OR @prefix is NULL
ORDER BY Production_Date

My Thought is that it cant compare the date time field to a nvarchar. How can I accomplish this?

Thanks

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 17:46:10

If your [Production_Date] column is datatype DATETIME then you will need to convert it to VARCHAR (in a suitable format, see BoL for the options available) before using LIKE.

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-02 : 18:20:53

If your [Production_Date] column is datatype DATETIME then using LIKE will not work. What are you trying to match @prefix with the [Production_Date] column ?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-02 : 20:24:42

Just use

SELECT Production_Date
FROM [Production Calendar]
WHERE Convert(VarChar(10),Production_Date,101) LIKE @prefix OR @prefix is NULL
ORDER BY Production_Date

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-03 : 11:07:45

Can you show us some sample data for @prefix?

Madhivanan

Failing to plan is Planning to fail

Jman0082
Starting Member

8 Posts

Posted - 2006-08-03 : 12:33:54

thanks for the replies.

I have a feeling I'm just going about this the hard way. I'm creating an AJAX style autocomplete to get the date. I may just be overlooking some simple property that alows you to continue typing in a drop down, although I think i would have ran into that by now. Please let me know if I'm wrong.

Date format MM/dd/YYYY
Sample input:
"7" should return all the entries in july of any year
"6/" all entries in june of any year
"6/2" all entries in june where the third character is 2 ie: the 2nd and the 20th - 29th of any year

I'm still not getting any records back if I execute my stored procedure with a prefix. I append a "%" to the end of the prefix when executing. If i dont enter a value i get all the dates which is what I want (for now).


ALTER PROCEDURE dbo.GetProductionDates
	(
	     @prefix as VarChar(16) = NULL
	)
AS
	SET NOCOUNT ON;
SELECT Production_Date FROM [Production Calendar]
WHERE Convert(VarChar(10),Production_Date,101) LIKE @prefix or @prefix is Null
ORDER BY Production_Date

It's nothing crucial to the app just thought it would be nice.

Thanks again

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-03 : 13:49:38

JMan,

There's a little conflict here. <g>

You said "Date format MM/dd/YYYY", but then say "7 should return all the entries in july of any year". There's your problem. Using the date format you indicated, July would be represented by "07", not just "7". So, in your front end application, just pad the left side as needed with zeros and then pass that in as your prefix value.

Ken

Jman0082
Starting Member

8 Posts

Posted - 2006-08-03 : 14:32:06

Thanks, I swear i looked at the date output before. Must of be the 8th hour of the day or something.

Works great.

Kristen
Test

22859 Posts

Posted - 2006-08-03 : 15:05:28

""7" should return all the entries in july of any year"

So does typing "1" give you January, or January + October + November + December?

I think I would wait UNTIL a "/" has been typed, and I can determine the intended Month number, before offering any data.

I think I would also use something like:

SELECT TOP 100 Production_Date FROM [Production Calendar] ...

for the Ajax select list, to reduce the amount of round-trip data - otherwise you run the risk that the user has typed more of a hint and SQL Server is still busy trying to get the broadest "hint-list" which has now become redundant.

An ISAM database would be fine for this job though!!!

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-03 : 15:43:28

I have to be honest, why is a date being used as an ajax-style lookup? Typically, it is a customer name or something like that.

If you are going to use dates for a lookup in this manner, you should create a calendar table that has VARCHAR column with values stored in the format you will use for the lookups, and then have that column indexed. If your table is large and you are querying it using a CONVERT() expression, there will be table scans on the table for each call and it will be very inefficient. Your goal in something like this should be maximum efficiency, especially if you expect this to be called for each keystroke that a user types in.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources