Date fn:

[pearl.kumar1]

Hi to all,

I like to start a new discussion after a long gap..Now i have a Question in Sql,I need to Take a difference in Current Month and Previous month,from get the diference i use the trigger to send a sms..
SELECT datepart(mm, dateadd(mm,-1,'2011/1/1') )
or
SELECT DATEADD(MONTH,-1,GETDATE()).i need to filter a exact date from a one month gap.
Ex: If a person buy a product in last month,again he come into the same store and buy a same product (Condition Apply:within one month ).I like to Trace the exact date and send a sms from that date..Kindly refer some ideas..

I think, you have already answered your question, for DATEADD(MONTH,-1,GETDATE()) exactly computes the starting date backwards beginning with current date getdate().

Possibly you want to get something like: select person from yourTable where date_of_purchase between starting_date AND current_date;

[pearl.kumar1]

Hi Dan,
Thanks for your reply,Actually i have still confusing about this date function.because i'm using this function in trigger for sms application,My doubt is similar to Y2k Problem.This date executes correctly..

WHERE DATETIME BETWEEN DATEADD(MONTH,-1,GETDATE()) AND GETDATE() ORDER BY DATETIME DESC

My doubt is If i'm using this application(Query) continuously ,is there any problem occur in jan 2014.Would you capture my Question..

Hi,
I don't think that turn of a year will raise problems when using MS Sql Server's date functions. Ok, before Sql Server 2008 there were really weak date datatypes and functions (I never understood why, for the mother of MS Sql Server is Sybase Database. And even the old obsolete Sybase transact SQL has absolutely good date datatypes and functions.)

If there are any doubt, you should simply test what happens if year turns: Within a stored procedure or simple Java using JDBC you can simulate it by setting date of purchase to something of December 2013 and current date to something in January 2014.

Btw, I have my own set of date functions for I am engaged in old historic dates usually not supported by databases (They usually end in something around 1582 or even just around 1900. My date functions are able to handle dates back to -4713 years (Joseph Scaliger (15x19x28):)

1dan