Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 t-sql - days to week and days Rate Topic Display Mode Topic Options
Author
 Message
 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 daysEx:350 days = 50 0/7 weeks 351 days = 50 1/7 weeks 352 days = 50 2/2 weeksI have a column with days in it.How do i represent it in weeks / days in the select statement output.
Post #1367316
 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
 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=350select 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
 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/7I 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/7288 will be 41 1/7All this has to be part of a select statement.select col1,col2 ,days,weekdays from table1;days column will have daysweekdays must have this information 40 0/7 , 40 0/7 , 40 0/7Output must be :Days , weekdays280 40 0/7281 40 1/7288 41 1/7How do i do this thanks
Post #1368083
 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 weekdaysFROM dbo.tablename----` SQL DBA,SQL Server MVP('07, '08, '09)One man with courage makes a majority. Andrew Jackson
Post #1368087
 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)How do I convert days into weeks and daysEx:350 days = 50 0/7 weeks 351 days = 50 1/7 weeks 352 days = 50 2/2 weeksI have a column with days in it.How do i represent it in weeks / days in the select statement output.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
 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)How do I convert days into weeks and daysEx:350 days = 50 0/7 weeks 351 days = 50 1/7 weeks 352 days = 50 2/2 weeksI have a column with days in it.How do i represent it in weeks / days in the select statement output.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.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
 Posted Thursday, October 04, 2012 11:13 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, February 01, 2013 12:42 PM Points: 139, Visits: 447
 Yes.Just as scott has coded.
Post #1368596
 Posted Thursday, October 04, 2012 11:32 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 1:24 PM Points: 32,913, Visits: 26,806
 ScottPletcher (10/4/2012)Jeff Moden (10/3/2012)sqlserver12345 (10/2/2012)How do I convert days into weeks and daysEx:350 days = 50 0/7 weeks 351 days = 50 1/7 weeks 352 days = 50 2/2 weeksI have a column with days in it.How do i represent it in weeks / days in the select statement output.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.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.Yep... that's exactly what I was talking about. --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 #1368603
 Posted Thursday, October 04, 2012 11:32 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 1:24 PM Points: 32,913, Visits: 26,806
 sqlserver12345 (10/4/2012)Yes.Just as scott has coded.Agreed. Just wanted to make sure. --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 #1368604

 Permissions