Dateadd question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dateadd question

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-09-30 : 17:06:53

DATEADD(WK, DATEDIFF(WK, 0, SOP10100.DOCDATE), 0) AS ID,

This expression in the REport is not doing what I want. I need to have the Column show the week period as most reflected by Startdate and EndDate.
Or I would take just the startdate.

But this expression is returning the date After the startdate which is not what we want.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-30 : 17:10:41

The formula you've shown seems to work. But, since there is not any sample data or expected ouput, it's hard to tell.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 02:02:26

what will value passed for startdate? what according to you will be start of week?

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-01 : 09:32:00

SELECT SOP10200.XTNDPRCE AS Price, SOP10200.QUANTITY AS quantity, DATEADD(WK, DATEDIFF(WK, 0, SOP10100.DOCDATE), 0) AS ID,

the report refers to Price, Quantity, ID, etc.

I can't get the SQL to add a field such as select @startdate as SD,

The report is working correctly on a start date of Sunday and a end date of the following Monday. This is oK but it really shouldn't matter whenever they want to see a week it should do it.

When the startdate is not a Sunday, the report makes 2 columns.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-01 : 09:35:31

DATEDIFF(WEEK...

is dependant on your language settings.

Give us some example for some dates, and the appropriate/corresponding id value.

N 56°04'39.26"
E 12°55'05.63"

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-01 : 09:41:16

Peso, our date fields are like this:

3/20/2007 12:00:00 AM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-01 : 09:48:41

And that date should have a start date of ..?

N 56°04'39.26"
E 12°55'05.63"

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-01 : 10:02:28

The start date is passed by the parameter to the report.

Say for 4/5/2009 to 4/11/2009 this presents only one column but it also displays 'week 4/6/2009' this is one issue. The expression for this display is: = "Week:" +Cstr(Fields!ID.Value)
ID comes from the SQL above.

The other thing I would want to know, is why it sets up 2 columns when the start date is not a Sunday.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-01 : 12:16:29

I'll try one last time: Sample data and Expected output?

You are a Master on this site with almost 200 posts. What are you finding difficult about posting a proper question with sample data and expected output?

[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-01 : 15:36:54

Lamprey, this is the output currently in XML. AS you see, it is showing 4/6/09. The Start date is 4/5/09 and this would be preferable.

<?xml version="1.0" encoding="utf-8" ?>
- <Report xsi:schemaLocation="Report1 http://reportserver/?%2fReport1&rs%3aFormat=XML&rc%3aSchema=True" Name="Report1" textbox6="northern center" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="Report1">
- <matrix1>
- <matrix1_Column_5_Collection>
- <matrix1_Column_5>
- <matrix1_Column_4_Collection>
- <matrix1_Column_4 Column_4="GPFVLC Gloves P.F. Vinyl Lg. :">
- <textbox1>
- <matrix1_Column_2_Collection>
- <matrix1_Column_2 Column_2="Week:4/6/2009">
<Cell Price="$75.00" />
</matrix1_Column_2>
</matrix1_Column_2_Collection>
</textbox1>
- <textbox4>
- <matrix1_Column_2_Collection>
- <matrix1_Column_2 Column_2="Week:4/6/2009">
<Cell quantity_1="3.00" />
</matrix1_Column_2>
</matrix1_Column_2_Collection>
</textbox4>
</matrix1_Column_4>
- <matrix1_Column_4 Column_4="PFVGLC Gloves Pwdr Free Vinyl Med. :">
- <textbox1>
- <matrix1_Column_2_Collection>
- <matrix1_Column_2 Column_2="Week:4/6/2009">
<Cell Price="$375.00" />
</matrix1_Column_2>
</matrix1_Column_2_Collection>
</textbox1>
- <textbox4>
- <matrix1_Column_2_Collection>
- <matrix1_Column_2 Column_2="Week:4/6/2009">
<Cell quantity_1="15.00" />
</matrix1_Column_2>
</matrix1_Column_2_Collection>
</textbox4>
</matrix1_Column_4>
</matrix1_Column_4_Collection>
</matrix1_Column_5>
- <matrix1_Column_5>
- <matrix1_Column_4_Collection>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 15:44:16

which field has the value 4/5/2009?

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-01 : 16:00:12

The passed parameter, the start date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 16:03:34

then shouldnt expression be:-
= "Week:" +Cstr(Parameters!StartDate.Value)

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-01 : 16:13:50

Yes that is very spot on Visakh.

The other issue I had here was that if you enter a start date that is not a Sunday, it display 2 columns in the Report.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 16:20:45

what two columns?

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-01 : 16:51:52

It just makes a second column. It starts filling the first col. with results, then puts results in the second col. (not both at same time).

But if the start-date is a Sunday, it will only have one column. I can tell the user you must do a Sunday to Monday request but I wish to understand why this occurs. and the problem here is that we are closed certain days so they may want to look at a week not just Sunday to Monday. But this is not the end of the world if I don't understand why this is happening.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-01 : 17:08:57

quote:
Originally posted by Adam West

DATEADD(WK, DATEDIFF(WK, 0, SOP10100.DOCDATE), 0) AS ID,

This expression in the REport is not doing what I want. I need to have the Column show the week period as most reflected by Startdate and EndDate.
Or I would take just the startdate.

But this expression is returning the date After the startdate which is not what we want.

OK, so is this what you want???

SELECT DATEADD(WK, DATEDIFF(WK, 6, SOP10100.DOCDATE), 6) AS ID

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-01 : 17:14:00

quote:
Originally posted by Adam West
I don't understand why this is happening.

because 1900/01/01 is a Monday

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-01 : 17:21:17

No this is still doing 2 columns.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-01 : 17:24:08

how about the Start date? Is it still 04/06 or did it change to 04/05?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 06:24:27

quote:
Originally posted by Adam West

It just makes a second column. It starts filling the first col. with results, then puts results in the second col. (not both at same time).

But if the start-date is a Sunday, it will only have one column. I can tell the user you must do a Sunday to Monday request but I wish to understand why this occurs. and the problem here is that we are closed certain days so they may want to look at a week not just Sunday to Monday. But this is not the end of the world if I don't understand why this is happening.

what are you giving as column group for matrix?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources