Showing posts with label adding. Show all posts
Showing posts with label adding. Show all posts

Monday, March 19, 2012

Incorrect date in column for 25,000 rows

I have a table with a column in it called Date, which is of the type DateTime, and for the last two years I have been adding data which I found out was incorrect.

My dates are all a day in the future, so I need to reduce each date by one day.

I can easily use a select script to reveal the 25,000 rows which are all incorrect dates. But I can't figure out how to update each and every row to subtract one day from each date.

So where I have:

26/01/2005

I would like to have:

25/01/2005

and of course for every record. Obviously way too many to do manually :-(

Can anyone show me a script that will get what I'm after.

Tia

Tailwag

After one your 25000 rows with invalid data, that is not nice to find out. But it is easy to solve, just run this query:


UPDATE [MyTable] SET [MyDate] = DATEADD(year, -1, [MyDate])


Don't forget to replace MyTable with your table name and MyDate with your datetime field name.
I have tested it before posting this solution, because i don't want to be responsible for lozing 25000 rows on friday.

|||

Also, replace year with day!

So the update query must be:

UPDATE [MyTable] SET [MyDate] = DATEADD(day, -1, [MyDate])

- Jeroen Boiten

|||Indeed! Thanks Jeroen, i overlooked that one. Thought only the years where incorrect.|||

Thank you PJ, and also Jeroen. I ran the query and 'Viola' it worked flawlessly, you guys are now extremely high on my best friends of all time list

Tia.

Tailwag

P.S. The B_ _ _ _ Friday curse has finally been thwarted!!!

Wednesday, March 7, 2012

Include User's Parameters in Report Header?

Hi, I'm getting stuck on what is probably a very simple/common task: Adding a textbox that displays the parameters that a user has selected for the current report.

For example, if the user chooses a @.startdate and @.enddate using date-pickers, a textbox in the header will display/print those parameters:

="Report by Department from " + @.startdate+ " to " + @.enddate

Seems simple, but I keep getting errors:

"Return statement in a Function, Get or Operator must return a value".

Can anyone help out with the proper syntax for repeating a user's parameters in the header or body of a report?

Thanks!

try

="Report by Department from " & Parameters!startdate.value " to " & Parameters!enddate.value

you can also right click at your textbox in the header and choose Expression to edit expression..

in there , choose Parameters in the left pane and double click the needed parameters in the right pane...

the expression will show at the top pane...

HTH

|||Ah, of course - Thankl you!|||What about something more general like looping through the parameters and writing them out by name and value? Any ideas?|||

do you mean multivalue parameters ?

if it is, u can try =Join(Parameters!status.label, ",")

.label for name; .value for value