t-sql - days to week and days

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » t-sql - days to week and days

t-sql - days to week and days

Rate Topic

Display Mode

Topic Options

Author

Message

sqlserver12345

Posted Tuesday, October 02, 2012 3:57 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 01, 2013 12:42 PM
Points: 139, Visits: 447

How do I convert days into weeks and days
Ex:350 days = 50 0/7 weeks
351 days = 50 1/7 weeks
352 days = 50 2/2 weeks

I have a column with days in it.How do i represent it in weeks / days in the select statement output.

Post #1367316

ScottPletcher

Posted Tuesday, October 02, 2012 4:04 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778

#days / 7 = #wks
#days % 7 = #days

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1367319

subbareddy542

Posted Wednesday, October 03, 2012 1:11 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 4:17 AM
Points: 832, Visits: 613

pls try below code:
declare @days int=350
select convert(varchar(10),(@days/7))+' '+case when (@days-((@days/7)*7))=0 then '' else convert(varchar(10),(@days-((@days/7)*7)))+'/7' end

Post #1367424

sqlserver12345

Posted Wednesday, October 03, 2012 4:16 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 01, 2013 12:42 PM
Points: 139, Visits: 447

I got this part.How do i concatenate both weeks and days together.
Ex: 37 5/7 or 31 /2/7 or 41 6/7
I have a days column
Ex:280 days
I would like to represent it as 40 0/7 in another column
281 will be 40 1/7
288 will be 41 1/7
All this has to be part of a select statement.
select col1,col2 ,days,weekdays from table1;

days column will have days
weekdays must have this information 40 0/7 , 40 0/7 , 40 0/7

Output must be :
Days , weekdays
280 40 0/7
281 40 1/7
288 41 1/7

How do i do this

thanks

Post #1368083

ScottPletcher

Posted Wednesday, October 03, 2012 4:22 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778

--
--
SELECT 
    col1, col2, days, 
    CAST(days / 7 AS varchar(5)) + ' ' + CAST(days % 7 AS varchar(1)) + '/7' AS weekdays
FROM dbo.tablename
--
--

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1368087

Jeff Moden

Posted Wednesday, October 03, 2012 6:39 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 32,913, Visits: 26,806

sqlserver12345 (10/2/2012)

Just double checking... you want "0 1/7" for 1 day and not "1 1/7" or "0 0/7", correct? If so, Scott's code works a treat.

I ask mostly because the first "week" will only have 6 days. The 7th day will be "1 0/7" as currently defined.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1368126

ScottPletcher

Posted Thursday, October 04, 2012 8:40 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778

Jeff Moden (10/3/2012)

sqlserver12345 (10/2/2012)

I don't see that as the first "week" having only 6 days. The "1" full week indicates 7 days.

6 days would result in "0 6/7", which mean 6 days of the first week, but no full week yet, i.e. less than 7 days.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1368469