August 3, 2011 at 11:51 am
Got to be something simple I'm not seeing here. Trying to query a datetime column.
Example date: 2011-01-02 00:00:00.000
SET @ETD = '%' --works as expected, all rows
SET @ETD = '' --works as expected, all rows
SET @ETD = '2011' --works as expected, all rows
SET @ETD = '201101' -- No results
SET @ETD = '20110102' -- No results
SET @ETD = '2011-01-02 00:00:00.000' -- No results
What am I getting wrong?
In the end, I'm also want to query for 'this week' or 'this month' as well.
thanks!
DECLARE @ETD varchar(50),@ETA varchar(50)
SET @ETD = '%'
SELECT ETD, ETA
FROM OceanShipments
WHERE (ETD LIKE Convert(varchar, '%'+ @ETD +'%', 112))
ORDER BY ETD
August 3, 2011 at 12:33 pm
what is the purpose of doing a convert to varchar?
I would suggest that the problem is that you ae not finding any results as none exist.
Is the time always 00:00:00.0000 or are there other times involved. if that is the case then you should not be doing a convert on the vaiable you should be doing a convert on the field.
So
convert(varchar(10),EDT,112)='08/03/11'
If you do not convert EDT the '08/03/11' which is read as 08/03/11 00:00:00.0000 would not return anything if the records had a valid time stamp.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
August 3, 2011 at 12:45 pm
you should be doing a convert on the field
That was it!
Thanks!
August 6, 2011 at 8:51 am
Yes, but doing a CONVERT on the column (field) is nasty because out there in the real world it'll stop the optimiser from using an index on that column.
I'm not sure why you declare your variables as varchar(50), unless the table data is stored as varchar? (And if it is .... 50 bytes??)
Assuming your table data is stored as datetime, the following would be better:
DECLARE @ETD datetime, @ETA datetime
SET @ETD = '2011-01-02 00:00:00.000'
SELECT ETD, ETA
FROM OceanShipments
WHERE ETD >= @ETD
ORDER BY ETD
August 6, 2011 at 8:54 am
Or, if you want everything for that day, not for the next day
SELECT ETD, ETA
FROM OceanShipments
WHERE ETD >= @ETD AND ETD < DATEADD(dd,1,@ETD)
ORDER BY ETD
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2011 at 2:21 pm
shank-130731 (8/3/2011)
Got to be something simple I'm not seeing here. Trying to query a datetime column.Example date: 2011-01-02 00:00:00.000
SET @ETD = '%' --works as expected, all rows
SET @ETD = '' --works as expected, all rows
SET @ETD = '2011' --works as expected, all rows
SET @ETD = '201101' -- No results
SET @ETD = '20110102' -- No results
SET @ETD = '2011-01-02 00:00:00.000' -- No results
What am I getting wrong?
In the end, I'm also want to query for 'this week' or 'this month' as well.
thanks!
DECLARE @ETD varchar(50),@ETA varchar(50)
SET @ETD = '%'
SELECT ETD, ETA
FROM OceanShipments
WHERE (ETD LIKE Convert(varchar, '%'+ @ETD +'%', 112))
ORDER BY ETD
I know that you think you have a solution for this but I'm compelled to ask... is the ETD column in the OceanShipments table a VARCHAR() or a DATETIME? If it's a VARCHAR(), does it only contain the date or does it contain something else.
I'm asking because if it's a VARCHAR(), there could be a "world-of-hurt" waiting for you at your next turn and would be willing to help you clean it up so you can correctly do "range searches" as the Gila Monster pointed out.
--Jeff Moden
August 8, 2011 at 10:18 am
The column ETD is datetime. The variable submitted from the website is varchar(50) (I was lazy and should have limited it to varchar(8). ETD is formatted as '2011-08-08 00:00:00.000'
"00:00:00.000" will always be constant.
WHERE (Convert(varchar,ETD,112) LIKE '%'+ Replace(@ETD,'-','') +'%')
Appears to be working as expected for me.
thanks!
August 8, 2011 at 10:31 am
Just hope that the table isn't big, because that cannot use index seeks because of the convert. You're building a potential performance problem.
btw, you have a convert to varchar without a length specified. Do you know what the default length is? Is it long enough?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2011 at 8:13 am
DECLARE @ETD datetime
SET @ETD = '2011'
SELECT ETD
FROM IXT_OceanShipments
WHERE (ETD >= @ETD)
ORDER BY ETD
As predicted by Jeff, I ran into problems as we increased records. Formalities: SQL2005. ETD is formatted as: '2011-04-09 00:00:00.000'. The above works fine except when I'm trying to filter it by Year, Month, etc. I'm thinking I want to use wildcards to build a string but I'm not getting it to work. So, how do I get wildcards to work with datetime fields?
thanks!
August 10, 2011 at 8:24 am
shank-130731 (8/10/2011)
DECLARE @ETD datetime
SET @ETD = '2011'
SELECT ETD
FROM IXT_OceanShipments
WHERE (ETD >= @ETD)
ORDER BY ETD
As predicted by Jeff, I ran into problems as we increased records. Formalities: SQL2005. ETD is formatted as: '2011-04-09 00:00:00.000'. The above works fine except when I'm trying to filter it by Year, Month, etc. I'm thinking I want to use wildcards to build a string but I'm not getting it to work. So, how do I get wildcards to work with datetime fields?
thanks!
How do your users enter the date they want to search by? Do they get to select "year", "month", and "day" separately?
If they search by just a "month", is the "year" defaulted? If they search by a "day", is the "year" and "month" defaulted?
Essentially, what you're after are three distinct SQL queries: -
--Day data
SELECT ETD
FROM IXT_OceanShipments
WHERE (ETD >= @ETD) AND ETD < DATEADD(dd,1,@ETD)
ORDER BY ETD
--Month data
SELECT ETD
FROM IXT_OceanShipments
WHERE (ETD >= @ETD) AND ETD < DATEADD(mm,1,@ETD)
ORDER BY ETD
--Year data
SELECT ETD
FROM IXT_OceanShipments
WHERE (ETD >= @ETD) AND ETD < DATEADD(yy,1,@ETD)
ORDER BY ETD
How you implement that is based entirely on how your app works.
August 10, 2011 at 8:46 am
shank-130731 (8/10/2011)
DECLARE @ETD datetime
SET @ETD = '2011'
SELECT ETD
FROM IXT_OceanShipments
WHERE (ETD >= @ETD)
ORDER BY ETD
As predicted by Jeff, I ran into problems as we increased records. Formalities: SQL2005. ETD is formatted as: '2011-04-09 00:00:00.000'. The above works fine except when I'm trying to filter it by Year, Month, etc. I'm thinking I want to use wildcards to build a string but I'm not getting it to work. So, how do I get wildcards to work with datetime fields?
thanks!
What data type is that column?
If it is a datetime column, see my earlier advice:
For one day of data
SELECT ETD, ETA
FROM OceanShipments
WHERE ETD >= @ETD AND ETD < DATEADD(dd,1,@ETD)
ORDER BY ETD
For all data from a certain date up until now
SELECT ETD, ETA
FROM OceanShipments
WHERE ETD >= @ETD
ORDER BY ETD
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2011 at 8:49 am
I have a drop down that's populated from a recordset that I create. Below is the data. The [Abbrev] field is submitted.
Abbrev - Descrip
2010 - Last Year
2011 - This Year
201101 - January
201102 - February
201103 - March
201104 - April
201105 - May
201106 - June
201107 - July
201108 - August
201109 - September
201110 - October
201111 - November
201112 - December
August 10, 2011 at 8:51 am
Once more with feeling....
What is the data type of the ETD and ETS columns in the OceanShipments tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2011 at 8:58 am
shank-130731 (8/10/2011)
I have a drop down that's populated from a recordset that I create. Below is the data. The [Abbrev] field is submitted.Abbrev - Descrip
2010 - Last Year
2011 - This Year
201101 - January
201102 - February
201103 - March
201104 - April
201105 - May
201106 - June
201107 - July
201108 - August
201109 - September
201110 - October
201111 - November
201112 - December
Change the submitted to be as follows: -
Abbrev - Descrip
2010-01-01 - Last Year
2011-01-01 - This Year
2011-01-01 - January
2011-02-01 - February
2011-03-01 - March
2011-04-01 - April
2011-05-01 - May
2011-06-01 - June
2011-07-01 - July
2011-08-01 - August
2011-09-01 - September
2011-10-01 - October
2011-11-01 - November
2011-12-01 - December
Then use Gail's queries to do the search.
So if "year" was selected, you want to run this query: -
--Year data
SELECT ETD
FROM IXT_OceanShipments
WHERE (ETD >= @ETD) AND ETD < DATEADD(yy,1,@ETD)
ORDER BY ETD
If a month is selected, you want to run this query: -
SELECT ETD
FROM IXT_OceanShipments
WHERE (ETD >= @ETD) AND ETD < DATEADD(mm,1,@ETD)
ORDER BY ETD
August 10, 2011 at 12:29 pm
DECLARE @ETD varchar(20), @ETDvar datetime, @TimeData varchar(1)
SET @ETD = '2010-01-01Y' --Last Year
IF SubString(@ETD,11,1)='Y'
SET @ETDvar = SubString(@ETD,1,10)
SET @TimeData = SubString(@ETD,11,1)
SELECT ETD
FROM IXT_OceanShipments
WHERE CASE @TimeData
WHEN 'Y' THEN (ETD >= @ETDvar) AND ETD < DATEADD(yy,1,@ETDvar) --Year
WHEN 'N' THEN (ETD >= @ETDvar) AND ETD < DATEADD(mm,1,@ETDvar) --Month
END = @TimeData
My pain continues. If I have to change the WHERE clause if searching either year or month, I figured CASE in the WHERE clause would be the way to go. What I would do is add a Y (year) or M (month) on the incoming data. I get an error Incorrect syntax near >. Cause appears to be a conditional statement in a THEN clause. Is there a way around this?
thanks!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply