January 9, 2018 at 9:25 am
Hi All,
Can anyone please advise on how to go about returning records where an int datatype column contains a 0 in specific positions i.e.1st; or 5th and 6th; or 7th and 8th positions? I came across the CharIndex and PATIndex functions, but I don't think these apply to integers. I'm looking to do this for testing purposes, as I need to verify that an int column which stores yyyymmdd formatted dates, doesn't contain 0s in the 1st y(ear) position, or both the m(onth) positions, or in both d(ay) positions. Please let me know if I need to further clarify. I appreciate your feedback 🙂
January 9, 2018 at 9:41 am
daniness - Tuesday, January 9, 2018 9:25 AMHi All,Can anyone please advise on how to go about returning records where an int datatype column contains a 0 in specific positions i.e.1st; or 5th and 6th; or 7th and 8th positions? I came across the CharIndex and PATIndex functions, but I don't think these apply to integers. I'm looking to do this for testing purposes, as I need to verify that an int column which stores yyyymmdd formatted dates, doesn't contain 0s in the 1st y(ear) position, or both the m(onth) positions, or in both d(ay) positions. Please let me know if I need to further clarify. I appreciate your feedback 🙂
SELECT your_date_field
FROM your_table
WHERE your_date_field > 9999999 -- year > 1000
OR your_date_field / 100 % 100 = 0 -- both month fields 0
OR your_date_field % 100 = 0 -- both days 0
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 9, 2018 at 9:41 am
That should just be a case of doing some integer math.
DECLARE @test_number int
SET @test_number = 99990099
SELECT FLOOR(@test_number / 10000) AS YEAR, FLOOR(@test_number / 100) % 100 AS MONTHS, @test_number % 100 AS DAYS
, CASE WHEN FLOOR(@test_number / 10000) < 1000 OR FLOOR(@test_number / 100) % 100 = 0 OR @test_number % 100 = 0 THEN 'INVALID' ELSE 'VALID' END
January 9, 2018 at 9:43 am
Well, you don't have to worry about a leading 0; INTs don't have such things 🙂
.For the month, should be sufficient to check that the column value modulo 10000 is less than 100, and for the day that the column value modulo 100 is equal to 0.
Cheers!
EDIT: Here's an example:
IF OBJECT_ID('tempdb.dbo.#somedates') IS NOT NULL DROP TABLE #somedates;
CREATE TABLE #somedates (somedate INT);
INSERT INTO #somedates VALUES
(20170915),
(20170013),
(20170500);
--Return problematic rows
SELECT somedate
FROM #somedates
WHERE somedate%10000<100 OR somedate%100=0
;
January 9, 2018 at 9:51 am
If you're looking for valid dates and you're using SQL Server 2014, you could simply try to convert them.
SELECT TRY_CONVERT( date, CONVERT( char(8), SomeDateInteger), 112)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply