Date Translation Help

Question

Date Translation Help

CRC

Old Hand

Points: 399
More actions
February 1, 2006 at 7:44 am

#170321

Given a specific date, I am attempting to determine what the date is for the Sunday of that week. Meaning, if I have a date of 2/1/2006, I want the query to translate 2/1/2006 to a date of 1/29/2006.
Kindest Regards,
CRC

Viewing 6 posts - 1 through 5 (of 5 total)

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Assuming U.S date format you could try something like this...substituting the getdate() with your date...

set datefirst 7
select convert(varchar, (getdate() - DATEDIFF(dd, datepart(dw, getdate()), @@datefirst)), 101)

**ASCII stupid question, get a stupid ANSI !!!**

Kiat Tang SSChasing Mays Points: 629 More actions · Answer 2

There are two parts for problem.

First you should use DATEPART(dw, @YourDate) to determine the day of the week of the specific date, in this case it's 02-01-2006 which is a Wednesday. the result will be a 4.(Sunday is 1, Saturday is 7). So the first part is:

SET @DatePart = DATEPART(dw, @YourDate)

The second part is to determine how many days to be deducted to get the Sunday. Wednesday is 4, and Sunday is 1. And Wednesday is three days after sunday, you can generally use @DatePartResult - 1 to get the days needed to be subtracted to get the last Sunday.

Of course if you have the specific date as a Sunday, e.g. 01-29-2006, I'd assume you want that to be the Sunday you need? So the second part of the query should be:

IF @DatePart > 1

SET @LastSundayDate = DATEADD(dd, -(@DatePart-1), @YourDate)

and @LastSundayDate is the date you're looking for.

Hope this help.

Steve Jones - SSC Editor SSC Guru Points: 735656 More actions · Answer 3

You can use DATEPART to find out which day of the week it is. Then depending on what you have DATEFIRST set to, you can add or subtract days.

For me, this

select datepart( dw, getdate())

gives me 4. I have Sun set to 1 on my server, so I know that I need to subtract 4 - 1, or 3 days. So

select dateadd(d, 1 - datepart( dw, getdate()), getdate())

gives me Sunday of each week.

CRC Old Hand Points: 399 More actions · Answer 4

I got it. Thanks for the help.

Kindest Regards,

CRC

David Korb SSCrazy Points: 2505 More actions · Answer 5

I've got it all wrapped up in a function.

CREATE FUNCTION UDF_WEEK_OF( @Date VARCHAR(23) )

RETURNS VARCHAR(23)

AS

BEGIN

DECLARE @Week_Ending AS VARCHAR(23)

SELECT @Week_Ending =

CONVERT(

VARCHAR

,DATEADD( WEEK, DATEPART( WEEK, @Date ) - 1, '1/1/' + CAST( DATEPART( yy, @Date ) AS VARCHAR ) )

,101

&nbsp

RETURN @Week_Ending

END