Use Like and DateDiff in where Clause

  • So, I should know this, but been a long while since I've written any SQL's...

    Lets say I have the value AB120315111111 (where 0315 represents MM/YY) in the Name Column. What I'd like to do is us a DateDiff and pull out every value that is older than 0312. Each time the MMYY in my value will be in the same place...

    Whats the best way to accomplish this?

  • SELECT SUBSTRING('AB120315111111', 5, 4)

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • you didn't give us alot to work with (read article in my signature) but this should get you the results you want.

    select *

    from tablename

    where datefromparts('20'+substring(name,7,2),substring(name,5,2),01) < '2012-03-01'

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Best way? Honestly, I would split off the date to a separate column, and then maybe index it. Then you can just use regular date math. The problem with "smart Keys" is that although they're sort of human readable, they're a nightmare to query. Because you're not dealing with the "leading edge" of the value, indexing it won't help, and you'll end up causing table scans. Not a big deal if you don't have a lot of data, but performance will always be brutal.

    If you're stuck with it, you can use SUBSTRING...

    DECLARE @textDate VARCHAR(15)= 'AB120315111111';

    SELECT SUBSTRING(@textDate,5,2);

    SELECT SUBSTRING(@textDate,7,2);

  • Ok, Here's more info..

    This value is our PC Domain Name and in the name we indicate the MMYY in which it was built. We are on a 48 Month Rotation and don't have a "Good Way" besides looking down a spreadsheet to determine which need replaced. So, I decided to dig around in our Microsoft SCCM Database and found a couple Tables that have all of the info we are looking for...Therefore, we're stuck with it as it is in the Column.

    select Name00, IP_Addresses0, Model00 from System_IP_Address_ARR, Computer_System_DATA

    where System_IP_Address_ARR.ItemKey=Computer_System_DATA.MachineID

    Now, I know that's a legacy join, but it works and we're only going to run this query once a month...

    The Name00 is the field I am looking to work with.

    If we have W7110315876abc (Order is: OS/Branch/MMYY/Serial #).

    As you can see, this PC was deployed in 0315. What I need is a substring('name00',5,2)=datediff(mm,getdate()) as well one for the YY.

    Am I close?

  • this should work

    SELECT [Name00],

    [IP_Addresses0],

    [Model00],

    datefromparts('20'+substring(Name00,7,2),substring(Name00,5,2),01) as DateDeployed

    FROM [System_IP_Address_ARR]

    inner join [Computer_System_DATA]

    ON [System_IP_Address_ARR].[ItemKey] = [Computer_System_DATA].[MachineID]

    WHERE datefromparts('20'+substring(Name00,7,2),substring(Name00,5,2),01) < dateadd(month,-48,getdate());

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • When I Run that query, I get the below error...

    Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the nvarchar value '20VM' to data type int.

  • This is how I would do it if there's no option to correct the data to save the date as a real date and not part of the string.

    CREATE TABLE #SampleData(

    myColumn varchar(20)

    )

    INSERT INTO #SampleData

    VALUES

    ('AB120315111111'),

    ('AB120212111111'),

    ('AB120312111111'),

    ('AB120412111111'),

    ('AB120111111111'),

    ('AB120811111111'),

    ('AB120311111111')

    SELECT *, SUBSTRING( myColumn, 5,4) ImportantDigits

    FROM #SampleData

    WHERE SUBSTRING( myColumn, 7,2) + SUBSTRING( myColumn, 5,2) < '1203'

    GO

    DROP TABLE #SampleData

    You could create a persisted column and index it to have better performance when querying the column.

    SELECT myColumn,

    SUBSTRING( myColumn, 5,4) AS ImportantDigits,

    CONVERT( date, SUBSTRING( myColumn, 7,2) + SUBSTRING( myColumn, 5,2) + '01', 12) AS RealDate

    FROM #SampleData

    WHERE CONVERT( date, SUBSTRING( myColumn, 7,2) + SUBSTRING( myColumn, 5,2) + '01', 12) < '20120301'

    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
  • bherbert (10/28/2015)


    When I Run that query, I get the below error...

    Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the nvarchar value '20VM' to data type int.

    I would guess that you have some computer names that do not follow the naming convention you expect. In the case that cause this error the name would have been something like AB1203VM111111 because in the datefromparts function I added 20 to the 7th and 8th characters which results in 20VM

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • If you are looking for a quick and dirty way and the data is in Excel I would just use text to columns and parse it out to an individual column.

Viewing 10 posts - 1 through 9 (of 9 total)

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