July 28, 2015 at 12:40 pm
I was looking for a way to use a wild card on a date column, but could only convert the file first then use the wildcard Is there another way of conducting the wild card search on a date column without conducting a conversion on the specified column? Sybase has the ability to use a wildcard on the datetime column so I would assume SQL SERVER does too.... Right? I can see that there are some workaround to get the information I need but I would like to know if anyone has an explanation for this. I have conducted multiple searches and I still cannot find a suitable answer. Anyway, below is some links where I received some of the information:
http://stackoverflow.com/questions/13048141/using-wildcard-for-datetime-column-in-sql-query
July 28, 2015 at 12:46 pm
anthony.r.colvin (7/28/2015)
I was looking for a way to use a wild card on a date column, but could only convert the file first then use the wildcard Is there another way of conducting the wild card search on a date column without conducting a conversion on the specified column? Sybase has the ability to use a wildcard on the datetime column so I would assume SQL SERVER does too.... Right? I can see that there are some workaround to get the information I need but I would like to know if anyone has an explanation for this. I have conducted multiple searches and I still cannot find a suitable answer. Anyway, below is some links where I received some of the information:http://stackoverflow.com/questions/13048141/using-wildcard-for-datetime-column-in-sql-query
What exactly are you trying to do? Maybe some examples of what you have tried to do might help understand what you're looking for.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 28, 2015 at 12:56 pm
I was trying to use a wild card for a date column such as July of 2015 and the query would show me all orders made within the month of July 2015. I have found a work around, so I was just trying to see if anyone knows why the wildcard is not an available option on a datetime column in SQL Server.
July 28, 2015 at 1:10 pm
Well datetime data types aren't stored as a string so you can't process it like a string. If you wanted to get a date range you would do something like date_column >= '6/1/2015' AND date_column < '7/1/2015'
July 28, 2015 at 1:13 pm
Internally, a datetime is a numeric. That's why you can do a "SELECT GETDATE() -1".
If you are always looking for a month, then check out the "Date/Time routines" link in my signature for the fastest ways to do this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 28, 2015 at 1:13 pm
For that it is much better to use the appropriate comparisons with >= and < on the beginning and end dates of the range.
Even though you can use wildcards with datetime in Sybase, it's just a bit of syntactic sugar; under the covers it's still doing the same thing you'd have to in SQL Server, which is converting the dates to varchar and then using the wildcards (see http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1570/html/blocks/X28482.htm for reference).
It's best to avoid such conversions if you can, since those can get costly in a hurry. It also would prevent you from effectively using an index on the datetime column. That's a lot of expense to save a few keystrokes 🙂
Cheers!
July 28, 2015 at 1:30 pm
anthony.r.colvin (7/28/2015)
I was trying to use a wild card for a date column such as July of 2015 and the query would show me all orders made within the month of July 2015. I have found a work around, so I was just trying to see if anyone knows why the wildcard is not an available option on a datetime column in SQL Server.
You could try something like:
WHERE DATEPART(YEAR, OrderDate) = 2015
AND DATEPART(MONTH, OrderDate) = 7
WHERE DATEPART(YEAR, OrderDate) = 2015
AND DATENAME(MONTH, OrderDate) = 'July'
From a performance point of view, this may not be the best option
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 28, 2015 at 2:38 pm
I'm glad to have helped!
Also, be sure you're aware of exactly how BETWEEN works with datetime; if you're not careful it's easy to get the boundaries wrong.
My favorite write-up on this is here: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx.
Cheers!
July 28, 2015 at 3:07 pm
anthony.r.colvin (7/28/2015)
@Jacob WilkinsThanks for the breakdown I was unaware that Sybase was conducting the conversion behind the scenes. I will continue to use "between" from this point foward.
When working with date ranges it is usually better to use >= and < to bracket the range. For example, all orders entered in July 2015:
declare @SearchDate = '2015-07-10'; -- arbitrary date for illustration
...
where
OrderDate >= dateadd(month,datediff(month,0,@SearchDate),0) and
OrderDate < dateadd(month,datediff(month,0,@SearchDate) + 1,0);
July 29, 2015 at 6:44 am
Thanks All
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply