Solved

SQL year and month e.g. 8:4

Posted on 2012-03-29
5
439 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now