Date Calculation

Question

Post reply

Date Calculation

anitha.cherukuri

SSCommitted

Points: 1514
More actions
January 21, 2010 at 1:43 pm

#214786

Hi Friends
I got a problem here.
I have a Reading Date Column like this.
Reading
18/11/09
14/05/09
20/11/08
23/05/08
22/11/07
Here I want to display another column like this
NoDays
188 --------------Diff between 18/11/09 And 14/05/09
175
181
183
Can anybody help me ?
Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

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

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 1

Need more info. Table schema perhaps w/ some scripts for loading data in them and perhaps the desired results.

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 2

also are you sure the dates are being stored that way? dd/mm/yy? That's odd.

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 3

Hi

The data has been stored as INT type for example 11/11/09 means 20091111

So i converted

to Reading_date using this statement.

CONVERT(DATETIME,CAST(ReadingDate_Key AS VARCHAR(12)),112) AS ReadingDate.

Plaese let me know if you need any more information

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 4

Here is the data in my table

Reading

20090909

20060909

20070909

My user asked no of days between readings.

Here is the sample ouput i want

Reading No Of days

09/09/2009 DateDIFF(09/09/2009 ,09/09/2007)

09/09/2007 DATEDIFF(09/09/2007,09/09/2006)

09/09/2007

Here i used convert function to display 20090909 as 09/09/2007.

But i dont know how to caluculate noofdays.

I think you can understand my situation.

Thanks,

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 5

Anitha,

You're probably not getting the help you need just because of the way you posted the data. Take a look at the first link in my signature below to get better answers a whole lot quicker. People will jump through hoops for you if you post data in the manner identified in that article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 6

HI Jeff

Thanks for your time.

Actually what i am looking is row caculation between dates(Date type is DATETIME).

Any help would be Appreciated .

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 7

Yes, I know what you're looking for. If you take the time to post the data in a readily consumable format (like in the article I referred you to), people will be more willing to help. I'm at your service when you do. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 8

anitha.cherukuri

SSCommitted

Points: 1514

January 22, 2010 at 12:32 am

#1107129

HI Jeff

I really thankful to you.

I will send you now.

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 9

Table:

MeterReading---------- ReadingDate

0 ---------- '2003-06-13 00:00:00.000'

70 ---------- '2003-11-12 00:00:00.000'

162 ---------- '2004-05-25 00:00:00.000'

239 ---------- '2004-11-03 00:00:00.000'

365 ---------- '2005-05-24 00:00:00.000'

Output What i am looking for:

ReadingDate ------------------------- NoOfDays(Row Calculated Column)

'2003-06-13 00:00:00.000' --------- 0

'2003-11-12 00:00:00.000'-------- Diff('2003-11-12' -'2003-06-13')

'2004-05-25 00:00:00.000'-------- Diff('2004-05-25','2003-11-12')

'2004-11-03 00:00:00.000' -------- Diff('2004-11-03 ','2004-05-25

'2005-05-24 00:00:00.000' -------- Diff('2004-05-25','2004-11-03')

I think it makes sense.....

Please let me know if you need any thing more....

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 10

Sorry jeff

i typed correctly and checked with the preview but it came odd format.

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 11

I updated the code now it looks ok...

Table:

MeterReading---------- ReadingDate

0 ---------- '2003-06-13 00:00:00.000'

70 ---------- '2003-11-12 00:00:00.000'

162 ---------- '2004-05-25 00:00:00.000'

239 ---------- '2004-11-03 00:00:00.000'

365 ---------- '2005-05-24 00:00:00.000'

Output What i am looking for:

ReadingDate ------------------------- NoOfDays(Row Calculated Column)

'2003-06-13 00:00:00.000' --------- 0

'2003-11-12 00:00:00.000' -------- Diff('2003-11-12' -'2003-06-13')

'2004-05-25 00:00:00.000' -------- Diff('2004-05-25','2003-11-12')

'2004-11-03 00:00:00.000' -------- Diff('2004-11-03 ','2004-05-25

'2005-05-24 00:00:00.000' -------- Diff('2004-05-25','2004-11-03')

I think it makes sense.....

Please let me know if you need any thing more....

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 12

BaldingLoopMan

SSCertifiable

Points: 6378

January 22, 2010 at 7:42 am

#1107337

select DateDIFF(dd,'09/09/2009' ,'09/10/2007')

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

Please take a close look at the code I have provided. In it you will see that I have provided you with the CREATE TABLE statement and the INSERT INTO statements to load sample data. This is what you had been asked for in earlier posts. If you provide this, many people will quickly jump in and help. It demonstrates that you are commited to solving your problem.

create table dbo.Meter (

MeterID int,

ReadingDate datetime,

MeterReading int

);

insert into dbo.Meter

select 1, '2003-06-13 00:00:00.000', 0 union all

select 1, '2003-11-12 00:00:00.000', 70 union all

select 1, '2004-05-25 00:00:00.000', 162 union all

select 1, '2004-11-03 00:00:00.000', 239 union all

select 1, '2005-05-24 00:00:00.000', 365;

with MeterReadings as (

select

row_number() over (partition by MeterID order by ReadingDate asc) as RowNum,

MeterID,

ReadingDate,

MeterReading

from

dbo.Meter

)

select

r1.MeterID,

r1.ReadingDate,

datediff(dd, isnull(r2.ReadingDate, r1.ReadingDate), r1.ReadingDate) as DaysBetweenReadings

from

MeterReadings r1

left outer join MeterReadings r2

on (r1.MeterID = r2.MeterID

and r1.RowNum = r2.RowNum + 1)

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 14

It demonstrates that you are commited to solving your problem.

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

It also gives us all level ground to work from. When you don't provide this sort of info i think we all are hesitent to help because we realize that your not exactly sure what your asking which means we will have to come up w/ several solution all the while getting closer and closer to you finding out exactly wehat you really want. For example. Does this table w/ the dates have an identity column? It would help if you provided more detailed info. Perhaps all the tables columns instead of just the dates. Which by the way have changed from mm/dd/yy to yyyymmdd though out your postings. Those sorts of things matter.