Solved

SQL year and month e.g. 8:4

Posted on 2012-03-29
5
440 Views
Last Modified: 2012-03-29
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
Comment
Question by:IssacJones
5 Comments
 
LVL 15

Assisted Solution

by:Anuj
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 59

Assisted Solution

by:Kevin Cross
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
;

Open in new window

0
 
LVL 9

Accepted Solution

by:
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

by:deighton
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 59

Expert Comment

by:Kevin Cross
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question