Post reply

Like statement

  • 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

  • November 26, 2013 at 5:14 pm

    #1669288

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

  • November 26, 2013 at 5:16 pm

    #1669289

    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: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')

    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

  • November 26, 2013 at 5:17 pm

    #1669290

    Hi Piet

    Yes and thanks in advance

  • November 26, 2013 at 5:18 pm

    #1669291

    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'

  • November 26, 2013 at 5:20 pm

    #1669293

    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

  • November 26, 2013 at 5:33 pm

    #1669294

    Hi Luis

    PERFECT...right on the money.

    Much appreciated!

    Everyone else, Thank you for you contributions too!

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

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