[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


SQL year and month e.g. 8:4

Posted on 2012-03-29
Medium Priority
Last Modified: 2012-03-29

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?

Question by:IssacJones
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
  • Learn & ask questions
LVL 15

Assisted Solution

Anuj earned 300 total points
ID: 37781327
FROM TableName
WHERE CONVERT(INT,replace(YearMonth,':','')) BETWEEN CONVERT(INT,replace('8:1',':','')) AND
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 300 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(
    StartAge VARCHAR(6),
    EndAge VARCHAR(6),

INSERT INTO @your_table(StartAge, EndAge)

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

SELECT T.ID, T.StartAge, T.EndAge
    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
    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


Accepted Solution

rajeevnandanmishra earned 300 total points
ID: 37781494
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
declare @retChar varchar(6)
set @retChar = LEFT(@CharAge,charindex(':', @CharAge)-1) +  RIGHT('00'+SUBSTRING(@CharAge,charindex(':', @CharAge)+1,2),2)
return @retChar

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)
LVL 18

Assisted Solution

deighton earned 300 total points
ID: 37781823
FROM TableName
WHERE cast(YearMonth as datetime) BETWEEN cast('8:1' as datetime) AND
cast('9:11' as datetime)
LVL 60

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!

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

650 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