Updating Date value automatically ?

Question

Post reply

Updating Date value automatically ?

Mitch2007

SSC Eights!

Points: 830
More actions
February 13, 2009 at 10:48 am

#197152

Good day all.
I am currently rewriting some tasks that I created in Access over to SQL Server 2000 and so far so good.
I have a procedure that takes the last day of the month using DateSerial :- DateSerial(Year(Date), Month(Date) - 1, 0) which is then used in a query to define criteria for records to pull from a table. In Access all the user need do is clikc the button as Access calculates which Date to use based on that days date (For example if the month is February, it will use the last day of December as the criteria) and pull the required Data for them.
Is there a way this can be automated in a Stored Procedure so the user does not have to edit the code each month ?
Any advice gratefully received.
Thanks,
Mitch....

Viewing 13 posts - 1 through 12 (of 12 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The following will return the last day of the previous month:

dateadd(mm, datediff(mm, 0, getdate()), -1)

Personally, the queries, however, should be coded as a < and use the following:

dateadd(mm, datediff(mm, 0, getdate()), 0)

Mitch2007 SSC Eights! Points: 830 More actions · Answer 2

Thanks for the prompt reply Lynn.

Unfortuantly i'm not getting any results and this may be due to the DataType for the field I am using.

I have the Datatype as SmallDateTime and the value I am trying to query is stored as 12/31/2008.

My query is written like so:-

SELECT * from TIPTest02

WHERE Cycle_Cutoff_DT = dateadd(mm, datediff(mm, 0, getdate()), -2)

GO

Should I change the Data type ?

Thanks again,

Mitch...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Mitch2007 (2/13/2009)
Thanks for the prompt reply Lynn.
Unfortuantly i'm not getting any results and this may be due to the DataType for the field I am using.
I have the Datatype as SmallDateTime and the value I am trying to query is stored as 12/31/2008.
My query is written like so:-
SELECT * from TIPTest02
WHERE Cycle_Cutoff_DT = dateadd(mm, datediff(mm, 0, getdate()), -2)
GO
Should I change the Data type ?
Thanks again,
Mitch...

Do this: select dateadd(mm, datediff(mm, 0, getdate()), -2)

Note the return value, then do this: select Cycle_Cutoff_DT from TIPTest02

Compare the values from the second query to the first. You'll find your problem is the equality condition in your where clause.

Mitch2007 SSC Eights! Points: 830 More actions · Answer 4

Ack, this is embarrassing because I can't figure it out.

I ran thsi statement on qry analyzer, select dateadd(mm, datediff(mm, -2, getdate()), -1)

GO

and got the date of 2009-02-28. I then changed the -2 to -4 but get the same result back. I did play with the last digits too but its not playing ball. I dare say there is soemthing blindingly obvious i'm missing and i'm turning red as I type !!

Mitch....

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Did you do this for me yet?

Do this: select dateadd(mm, datediff(mm, 0, getdate()), -2)
Note the return value, then do this: select Cycle_Cutoff_DT from TIPTest02

What was the results?

Mitch2007 SSC Eights! Points: 830 More actions · Answer 6

OK, for the first statement the result is 2009-01-31 00:00:00.000,

so by changing the value of the last digit the date changes.

The second statement displayed the result of 2008-12-31 00:00:00, which is what I expected to see.

I thought by changing the value of the 0 to -2 it would return with the last day of December, but instead it returns 2009-02-28 00:00:00.000.

I did try using SELECT dateadd(mm, -2, getdate()) but can't get it to do the last day of that month.

I appreciate you hanging in there for me 🙂

Mitch.....

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Okay, using the original query, change your equality statement to a less than statement, then run the query again.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Here is some date manipulation code for you to play with as well:

select

getdate(),

dateadd(mm, datediff(mm, 0, getdate()), 0),

dateadd(mm, datediff(mm, 0, getdate()), -1),

dateadd(mm, datediff(mm, 0, getdate()) - 1, 0),

dateadd(mm, datediff(mm, 0, getdate()) - 1, -1)

Mitch2007 SSC Eights! Points: 830 More actions · Answer 9

Thanks for your patience and input Lynn, I really appreciate it.

Using

SELECT Cycle_Cutoff_DT

FROM TIPTest02

WHERE Cycle_Cutoff_DT < dateadd(mm, datediff(mm, 0, getdate()), 0)

GO

brings in the same Dataset as we tested earlier, but the query will be pulling from a large table which dates back many years. The criteria for the Dataset is the last day of the Cutoff month which is two months back, so for the whole of the month of February the Data which needs to be pulled with have the Cutoff month of 12/31/2008. What I want to do is have a stored procedure that runs after the first day of the month and pulls in the desired Datatset.

Sorry if i'm being a pain.

Mitch......

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

I am not sure what your criteria really is here. What are you trying to return with the query? If February, you what all data for the past two months, December and January?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Try this in your WHERE clause in place of what you currently have:

Cycle_Cutoff_DT > dateadd(mm, datediff(mm, 0, getdate()) - 1, -1) and

Cycle_Cutoff_DT < dateadd(mm, datediff(mm, 0, getdate()), 0)

Mitch2007 SSC Eights! Points: 830 More actions · Answer 12

Lynn thank you so much for your time and help on this.

You've pratically saved my bacon !!

Mitch.......