Solved

SQL year and month e.g. 8:4

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 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