LIKE operator
select * from table where timefield = '2008-06-11 09:00:24.000'
gets me back 6 rows
select distinct timefield from table where timefield like '%.000'
gives me nothing
select distinct timefield from table where timefield like '2008-06-11%'
gives me nothing
select distinct timefield from table where timefield between '6/11/08' and '6/12/08'
gives me back 44 records, all of which are suffixed with ' .000 '
what am i doing wrong? can i not use LIKE on a datetime?
gets me back 6 rows
select distinct timefield from table where timefield like '%.000'
gives me nothing
select distinct timefield from table where timefield like '2008-06-11%'
gives me nothing
select distinct timefield from table where timefield between '6/11/08' and '6/12/08'
gives me back 44 records, all of which are suffixed with ' .000 '
what am i doing wrong? can i not use LIKE on a datetime?
Last Comment
chapmandew
to be honest, I've never used LIKE on a datetime..why are you trying to use it? Between or >= and < are the operators to use for datetime (like you used above)
ASKER
one of my procedures is returning the data incorrectly -- so i'm going into the dataset to first find if it's actually stored that way (no miliseconds on the timefield)
is it datetime data type?
if so - use convert\cast to char for like
and datediff for datetime
if so - use convert\cast to char for like
and datediff for datetime
I am not sure you can use like with datetyme format . you may want to considering converting it into varchar or so
from what i understand of datetime in databases and spreadsheets, they are not stored as we see them they are stored as days from 1 jan 1970 (i think)
try
select distinct timefield from table where cast(timefield as varchar(25)) like '2008-06-11%'
i haven't tried it but from my understanding of sql it should work
try
select distinct timefield from table where cast(timefield as varchar(25)) like '2008-06-11%'
i haven't tried it but from my understanding of sql it should work
ASKER
it is datetime, but it didn't begin that way. it came in as a varchar, i had to strip out the non-date stuff, and then i converted it to datetime
maybe that's causing the problem
maybe that's causing the problem
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
wow. i did not know that, zberteoc. i assumed the convert was my fix, but no, i didn't know i couldn't LIKE a datetime.
thank you very much
thank you very much
ASKER
....learn something new every day
dbaSQL,
O man, looks like you did not see my post above:
<is it datetime data type?
<if so - use convert\cast to char for like
<and datediff for datetime
O man, looks like you did not see my post above:
<is it datetime data type?
<if so - use convert\cast to char for like
<and datediff for datetime
ASKER
no, no, i saw it. i just wasn't thinking thru it all the way, nor did I award properly
zber's 'you cannot use LIKE' got me there faster
i apologize, eugene. that was a mistake.
i will try to re-open so i can split
zber's 'you cannot use LIKE' got me there faster
i apologize, eugene. that was a mistake.
i will try to re-open so i can split
no problems :)
ASKER
the site has changed a bit, do you know, do i just post an inquiry to customer support, they'll open it back up, yeah?
do not worry about it -
have a good day!
have a good day!
ASKER
ok, eugene. but only if you are certain. again, i do apologize. it was not intentional, not at all.
you have a very good day, too
you have a very good day, too
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
