Like statement

Question

Post reply

Like statement

C-kweldude

Old Hand

Points: 312
More actions
November 26, 2013 at 5:03 pm

#279409

Hi Guys
Sorry, im a bit rusty..
Im trying to select records where the have a similar datestamp.
So im using the like expression with a wildcard
Example of the records..
2013-11-26 13:49:16.000
2013-11-26 13:49:16.000
2013-11-26 13:49:16.000
2013-11-26 13:49:17.000
2013-11-26 13:49:17.000
2013-11-26 13:49:17.000
2013-11-26 13:49:17.000
So, I need to include everything that falls within 2013-11-26 13:49:.......irrespective of the Seconds value
So I run
Select * from Table where DTStamp like '2013-11-26 13:49:%'
However , this returns no records...Any ideas where im going wrong?
Thanks in Advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

BETWEEN 2013-11-26 13:49:00 AND 2013-11-26 13:49:59?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

That's because the column DTStamp is being implicitly converted to char with a different format.

Here are two options but might not be the best ones.

CREATE TABLE #Test( myDatetime datetime)

INSERT #Test VALUES

('2013-11-26 13:49:16.000'),

('2013-11-26 13:49:17.000'),

('2013-11-26 13:49:17.000')

SELECT *

FROM #Test

WHERE CONVERT( char(17),myDatetime, 120) = '2013-11-26 13:49:'

SELECT *

FROM #Test

WHERE DATEADD( MINUTE, DATEDIFF( MINUTE, DATEADD( day, DATEDIFF( day, 0, myDatetime), 0), myDatetime), DATEADD( day, DATEDIFF( day, 0, myDatetime), 0)) = '2013-11-26 13:49'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

C-kweldude Old Hand Points: 312 More actions · Answer 3

C-kweldude

Old Hand

Points: 312

November 26, 2013 at 5:17 pm

#1669290

Hi Piet

Yes and thanks in advance

dogramone Hall of Fame Points: 3216 More actions · Answer 4

You are missing the DDL so I am assuming your datestamp column is of datetime data type.

You will need to use between as wild cards don't work well with dates.

So mydate between '2013-11-26 13:49' and '2013-11-26 13:50'

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

With piet's query you might loose rows if your dates have milliseconds after 13:49 and with dogramone's you might include dates on 13:50.

SELECT *

FROM #Test

WHERE myDatetime >= '2013-11-26 13:49'

AND myDatetime < DATEADD( minute, 1, '2013-11-26 13:49')

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

C-kweldude Old Hand Points: 312 More actions · Answer 6

Hi Luis

PERFECT...right on the money.

Much appreciated!

Everyone else, Thank you for you contributions too!