Syntax help
-
Trying to do what I thought would be a simple LIKE wildcard query but I can't get the syntax correct.. can anyone help?
select * from mytable where UploadDate LIKE '2012-07-18%'
Create table dbo.mytable (
UploadDate datetime null,
OnUs VARCHAR(30) null,
AuxOnUs VARCHAR(20) null,
Routing VARCHAR(10) null,
Amount VARCHAR(10) null,
VfyFile VARCHAR(30) null,
Check21File VARCHAR(50) null,
OverrideCode VARCHAR(50) null)
on [PRIMARY] ;
Sample Data
UploadDate,OnUs,AuxOnUs,Routing,Amount,VfyFile,Check21File,OverrideCode
2012-07-18 14:38:23.000,9860002654953/,001172,211174181,2001,CREDKOFC\F0718154.vfy,,
2012-07-16 17:34:43.000,535021050/3119,,211370066,11723,CREDKOFC\F0716083.vfy,,
2012-07-24 16:14:26.000,00264145/0774,,071108407,3710,CREDKOFC\F0724157.vfy,,
2012-07-24 16:14:26.000,4206128729/,002055,041000124,9482,CREDKOFC\F0724157.vfy,,
-
Wildcards only work on character fields.
To get all records for a date you can do this:
select * from mytable where UploadDate BETWEEN '2012 July 18' AND '2012 July 19'
or you can be more specific
select * from mytable where UploadDate BETWEEN '2012 July 18 00:00:00:000' AND '2012 July 19 23:59:59:997'
-
I would change the UploadDate to a date in the where clause and do a comparision that way
SELECT
*
FROM
MyTable
WHERE
CONVERT(DATE,UploadDate) = '2012-07-18'
-
Oops... make that '2012 Jul 18 23:59:59:997'!
BTW DateTime is only accurate to 3 milliseconds.
-
laurie-789651 (7/26/2012)
Oops... make that '2012 Jul 18 23:59:59:997'!BTW DateTime is only accurate to 3 milliseconds.
For a specific day, use Anthony's suggestion (I think I read somewhere that in that case SQL Server could still use an index on that column), or
WHERE UpLoadDate >= '20120718' and UpLoadDate < '20120719'
When selecting against a range of datetime values it is better to use a closed end comparision on the lower end and an open end comparision on the upper end. Then you don't have to worry about the accuracy of the time portioin, which changes from DATETIME to DATETIME2.
-
SELECT * FROM iaxChecksCurrent
WHERE
CONVERT(DATE,UploadDate) = '2012-07-18'
gives me this:
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type (I neglected to say I was on SQL Server 2005)
SELECT * FROM iaxChecksCurrent
WHERE UpLoadDate >= '20120718' and UpLoadDate < '20120719'
Works great for my purposes!!
Thank you ALL!!!!!!!
-
Jpotucek (7/26/2012)
SELECT * FROM iaxChecksCurrent
WHERE
CONVERT(DATE,UploadDate) = '2012-07-18'
gives me this:
Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type (I neglected to say I was on SQL Server 2005)
SELECT * FROM iaxChecksCurrent
WHERE UpLoadDate >= '20120718' and UpLoadDate < '20120719'
Works great for my purposes!!
Thank you ALL!!!!!!!
Yes you did, and you posted this in a SQL Server 2008 forum which is why you got a SQL Server 2008 solution.
-
my bad. Thank you for the help!
-
pls try below code
SELECT * FROM iaxChecksCurrent
WHERE
CONVERT(varchar(10),UploadDate,120) = '2012-07-18'
-
subbareddy542 (7/27/2012)
pls try below codeSELECT * FROM iaxChecksCurrent
WHERE
CONVERT(varchar(10),UploadDate,120) = '2012-07-18'
This query won't use an index if one exists on UploadDate as the conversion from datetime to character string must be done on every record.
-
Just to add to the conversation. You could also use casting to do a like search:
select * from mytable where cast(UploadDate as varchar(10)) like '%2002%'
Just note that any casting prevents the use of an index.
-
Once again.. thank you all for your support!!!!
select * from iaxChecksCurrent where cast(UploadDate as varchar(10)) like '%2012-07-18%'
This returned zero rows
SELECT * FROM iaxChecksCurrent
WHERE
CONVERT(varchar(10),UploadDate,120) = '2012-07-18'
This worked like a charm!
-
Jpotucek (7/27/2012)
Once again.. thank you all for your support!!!!select * from iaxChecksCurrent where cast(UploadDate as varchar(10)) like '%2012-07-18%'
This returned zero rows
SELECT * FROM iaxChecksCurrent
WHERE
CONVERT(varchar(10),UploadDate,120) = '2012-07-18'
This worked like a charm!
:exclamation: Keep in mind using this query (also mentioned by Lynn above), it won't use index if it is present on UploadDate.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Jpotucek (7/27/2012)
Once again.. thank you all for your support!!!!select * from iaxChecksCurrent where cast(UploadDate as varchar(10)) like '%2012-07-18%'
This returned zero rows
SELECT * FROM iaxChecksCurrent
WHERE
CONVERT(varchar(10),UploadDate,120) = '2012-07-18'
This worked like a charm!
Take head of the warnings the other folks have given. The method above will never do an index seek. Things might look hunky-dory right now because your table is small but it's a performance time bomb waiting to happen. The query should be...
SELECT * --<<< Actually, this has got to go, as well.FROM dbo.iaxChecksCurrent
WHERE UploadDate >= '2012-07-18' AND UploadDate < '2012-07-19'
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply