t-sql - days to week and days

Question

Post reply

t-sql - days to week and days

mtz676

SSCrazy Eights

Points: 9212
More actions
October 2, 2012 at 3:57 pm

#282607

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.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

#days / 7 = #wks

#days % 7 = #days

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

subbu1 Hall of Fame Points: 3281 More actions · Answer 2

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

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 3

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

--

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

sqlserver12345 (10/2/2012)
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.

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

Jeff Moden (10/3/2012)
sqlserver12345 (10/2/2012)
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.
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 7

mtz676

SSCrazy Eights

Points: 9212

October 4, 2012 at 11:13 am

#1545699

Yes.

Just as scott has coded.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

ScottPletcher (10/4/2012)
Jeff Moden (10/3/2012)
sqlserver12345 (10/2/2012)
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.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)