October 27, 2015 at 2:08 pm
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?
October 27, 2015 at 2:49 pm
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/
October 27, 2015 at 2:50 pm
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'
October 27, 2015 at 2:51 pm
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);
October 27, 2015 at 3:02 pm
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?
October 27, 2015 at 3:06 pm
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());
October 28, 2015 at 6:39 am
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.
October 28, 2015 at 7:26 am
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'
October 28, 2015 at 7:44 am
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
October 29, 2015 at 9:04 am
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