Solved

# SQL year and month e.g. 8:4

Posted on 2012-03-29
446 Views
Hi

I have a table that stores a year and month age e.g. 8:4 (8 years and 4 months old) in two columns e.g.

8:4        9:11

I would like to be able to select on this table so I can select rows that fall into a range which I supply e.g. find rows in the table where the two columns have values that fall into

8:7         9:02

That is, the row I gave first would contain this range I've supplied.

Can anybody help?

John
0
Question by:IssacJones
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 15

Assisted Solution

Anuj earned 75 total points
ID: 37781327
SELECT *
FROM TableName
WHERE CONVERT(INT,replace(YearMonth,':','')) BETWEEN CONVERT(INT,replace('8:1',':','')) AND
CONVERT(INT,replace('9:11',':',''))
0

LVL 60

Assisted Solution

Kevin Cross earned 75 total points
ID: 37781370
My initial thought is to CONVERT() to INT as well, but I did a little differently as it appeared that the number of digits (zero fill) is not consistent, plus 811 is not a valid comparison to 84.

``````DECLARE @your_table TABLE(
ID BIGINT IDENTITY(1,1),
StartAge VARCHAR(6),
EndAge VARCHAR(6),
PRIMARY KEY(ID)
);

INSERT INTO @your_table(StartAge, EndAge)
VALUES('8:4','9:11'),('7:11','9:02'),('8:8','9:01');

DECLARE @startAge VARCHAR(6), @endAge VARCHAR(6);
SET @startAge = '8:7';
SET @endAge = '9:02';

SELECT T.ID, T.StartAge, T.EndAge
FROM (
SELECT ID, StartAge, EndAge
, StartAgeMonths=CONVERT(INT, LEFT(StartAge, PATINDEX('%[^0-9]%', StartAge)-1))*12
+CONVERT(INT, RIGHT(StartAge, PATINDEX('%[^0-9]%', REVERSE(StartAge))-1))
, EndAgeMonths=CONVERT(INT, LEFT(EndAge, PATINDEX('%[^0-9]%', EndAge)-1))*12
+CONVERT(INT, RIGHT(EndAge, PATINDEX('%[^0-9]%', REVERSE(EndAge))-1))
FROM @your_table
) T
JOIN (
SELECT StartAgeMonths=CONVERT(INT, LEFT(@StartAge, PATINDEX('%[^0-9]%', @StartAge)-1))*12
+CONVERT(INT, RIGHT(@StartAge, PATINDEX('%[^0-9]%', REVERSE(@StartAge))-1))
, EndAgeMonths=CONVERT(INT, LEFT(@EndAge, PATINDEX('%[^0-9]%', @EndAge)-1))*12
+CONVERT(INT, RIGHT(@EndAge, PATINDEX('%[^0-9]%', REVERSE(@EndAge))-1))
) R ON R.StartAgeMonths >= T.StartAgeMonths AND R.EndAgeMonths <= T.EndAgeMonths
;
``````
0

LVL 9

Accepted Solution

rajeevnandanmishra earned 75 total points
ID: 37781494
Hi,
If you want to use a query, you can write like :
declare @StartAge varchar(5), @EndAge varchar(5)
select @StartAge = '8:07', @EndAge = '9:02'

select  * from myTable
where LEFT(@StartAge,charindex(':', @StartAge)-1) +  RIGHT('00'+SUBSTRING(@StartAge,charindex(':', @StartAge)+1,2),2)
>=  LEFT(StartAge,charindex(':', StartAge)-1) +  RIGHT('00'+SUBSTRING(StartAge,charindex(':', StartAge)+1,2),2)
and LEFT(@StartAge,charindex(':', @StartAge)-1) +  RIGHT('00'+SUBSTRING(@StartAge,charindex(':', @StartAge)+1,2),2)
<= LEFT(EndAge,charindex(':', EndAge)-1) +  RIGHT('00'+SUBSTRING(EndAge,charindex(':', EndAge)+1,2),2)
and LEFT(@EndAge,charindex(':', @EndAge)-1) +  RIGHT('00'+SUBSTRING(@EndAge,charindex(':', @EndAge)+1,2),2)
>=  LEFT(StartAge,charindex(':', StartAge)-1) +  RIGHT('00'+SUBSTRING(StartAge,charindex(':', StartAge)+1,2),2)
and LEFT(@EndAge,charindex(':', @EndAge)-1) +  RIGHT('00'+SUBSTRING(@EndAge,charindex(':', @EndAge)+1,2),2)
<= LEFT(EndAge,charindex(':', EndAge)-1) +  RIGHT('00'+SUBSTRING(EndAge,charindex(':', EndAge)+1,2),2)

or you can create a function like:

create function dbo.calc_age (@CharAge varchar(5)) returns varchar(6) as
begin
declare @retChar varchar(6)
set @retChar = LEFT(@CharAge,charindex(':', @CharAge)-1) +  RIGHT('00'+SUBSTRING(@CharAge,charindex(':', @CharAge)+1,2),2)
return @retChar
end
go

And then call the query as:

declare @StartAge varchar(5), @EndAge varchar(5)
select @StartAge = '8:07', @EndAge = '9:02'

select  * from myTable
where dbo.calc_age(@StartAge) >=  dbo.calc_age(StartAge) and dbo.calc_age(@StartAge) <= dbo.calc_age(EndAge)
and dbo.calc_age(@EndAge) >=  dbo.calc_age(StartAge) and dbo.calc_age(@EndAge) <= dbo.calc_age(EndAge)

But, I would suggest to change your logic of storing the "Year:Month". And have some standard for data entering (like we have data with #:# and #:0# format)
0

LVL 18

Assisted Solution

deighton earned 75 total points
ID: 37781823
SELECT *
FROM TableName
WHERE cast(YearMonth as datetime) BETWEEN cast('8:1' as datetime) AND
cast('9:11' as datetime)
0

LVL 60

Expert Comment

ID: 37781965
*Ha* Nice trick. Since the months are never over 12, there is no worry of the minutes spilling into hours. Seems like this would be viable if your Years is <24.

e.g., Try: SELECT CAST('24:1' AS DATETIME);
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Nice suggestion, though, @deighton!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month6 days, 2 hours left to enroll