want to retrive the date based on the date for every three months

Question

Post reply

want to retrive the date based on the date for every three months

SAT_SQL

Default port

Points: 1405
More actions
February 17, 2010 at 5:12 am

#216829

Hi

i having the one table called oefforts , in that there is column called created date , in which the date will be update if any manager will update any request , now they want for evry three months report based on the created date.

I tried by using the between operators , but it is not showing the excat three months date it is showing all the date .

please let me know , if some can help me in this issue.

Waiting for the response .

Regards

sat

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

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

It sounds like you want to calculate reminder dates with an interval of three months, for lets say a year into the future?

you'll want to use a Calendar or Tally table to do that, and most likely insert the results into a reminder table of some sort.

here's some data: notice how i added the extra step to remove the TIME portion fromt he date; you might need that, so i included it for clarity:



StartDateTime              FuureDateTime              Midnight                   FuureDate

-----------------------    -----------------------    -----------------------    -----------------------

2010-02-17 08:25:41.443    2010-05-17 08:25:41.443    2010-02-17 00:00:00.000    2010-05-17 00:00:00.000

2010-02-17 08:25:41.443    2010-08-17 08:25:41.443    2010-02-17 00:00:00.000    2010-08-17 00:00:00.000

2010-02-17 08:25:41.443    2010-11-17 08:25:41.443    2010-02-17 00:00:00.000    2010-11-17 00:00:00.000

2010-02-17 08:25:41.443 2011-02-17 08:25:41.443 2010-02-17 00:00:00.000 2011-02-17 00:00:00.000

an important piece is the DATEADD function, where you can add months to a specific date.

here's an example; :



--ten years worth of days from todays date:

with Tally as (

                         SELECT TOP 3650

                           row_number() OVER (ORDER BY sc1.id) AS N

                         FROM Master.dbo.SysColumns sc1

                         CROSS JOIN Master.dbo.SysColumns sc2

                      )

                       SELECT GETDATE() as StartDateTime,

                       DATEADD(qq,Tally.N,GETDATE()) As FuureDateTime,

                       DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) AS Midnight,

                         DATEADD(qq,Tally.N,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As FuureDate

                      from Tally

                      where Tally.N < 5

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 2

Could you show us what you are talking about? The description of your problem makes it difficult to know what your are trying to accomplish. Table defs (CREATE TABLE statement(s)), sample data (series of INSERT INTO statements for the table(s)), and expected results based on the sample data would go a long way in helping us help you.

SAT_SQL Default port Points: 1405 More actions · Answer 3

Hi,

The below is my table look like .

Table Name:oeFFORTS

MY MANAGER WANT THE report depending upon the Created date column, can u please help now in this issue .

X1 X2 X3 CreatedDate CreatedTime

8/18/2009 1:40:04 PM

9/2/2009 12:53:32 PM

11/2/2009 11:31:29 AM

11/6/2009 11:29:39 AM

11/9/2009 12:59:07 PM

11/24/2009 3:07:06 PM

11/2/2009 8:56:19 AM

11/3/2009 10:04:04 AM

11/3/2009 10:07:18 AM

11/4/2009 9:36:57 AM

11/4/2009 9:29:02 AM

11/4/2009 9:30:46 AM

11/13/2009 9:06:23 AM

11/16/2009 12:41:07 PM

11/16/2009 12:46:59 PM

11/6/2009 11:29:39 AM

Waiting for the swift response .

Regards

sat

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 4

Hi If you want the output in the pivot format itself in the database, then its kinda difficult... but if you can create pivot report in the Excel then populating the data for it is easy.

-r ww; -n rmudugal;

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.