DateDiff and Concatentaion? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DateDiff and Concatentaion?

Author

Topic

cardgunner

326 Posts

Posted - 2007-04-23 : 13:54:14

Second Post with very little SQL knowlewdge. I have a good knowledge of MS Access however the SQL from Access doesn't always work in SQL 2000.

I have a date field and want to show how many years months and days has gone by since that date in one line. I have found some code in this forum that seemed to work to get years and months however the days seem to be wrong. And I have no idea how to convert these to characters. You will see that my text string is taking the values as numbers and won't concate??.Nor sure if I'm using the right words. In any case here is what I got after 2 days searching with trial and error.

USE cardgunnerdb
begin
Select
t_ldat,
DateDiff(m, t_ldat, getdate())/12 as Years,
DateDiff(m, t_ldat, getdate())%12 as Months,
DateDiff(d, DateAdd(m, DateDiff(m, t_ldat, getdate()), t_ldat), getdate()) as Days,
DateDiff(m, t_ldat, getdate())/12 + '-'+
DateDiff(m, t_ldat, getdate())%12 + '-'+
DateDiff(d, DateAdd(m, DateDiff(m, t_ldat, getdate()), t_ldat), getdate())as Combined
from
ttfext200100
where
t_fpln='351'
end
go

results

t_ldat Years Months Days Combined
2004-05-13 00:00:00.000 2 11 10 23
2004-12-13 00:00:00.000 2 4 10 16
2005-04-14 00:00:00.000 2 0 9 11
2005-04-30 00:00:00.000 2 0 -7 -5
2005-05-31 00:00:00.000 1 11 -7 5
2005-07-01 00:00:00.000 1 9 22 32
2005-06-30 00:00:00.000 1 10 -7 4

Any help on the neg numbers for days? or how I can get the Combined field to read 2-11-10?

From pressure comes diamonds,
Card Gunner

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-23 : 13:58:42

Throw in an ABS().


...
DateDiff(m, @t_ldat, getdate())/12 as Years, 
DateDiff(m, @t_ldat, getdate())%12 as Months, 
abs(DateDiff(d, DateAdd(m, DateDiff(m, @t_ldat, getdate()), @t_ldat), getdate())) as Days,
DateDiff(m, @t_ldat, getdate())/12 + '-'+
DateDiff(m, @t_ldat, getdate())%12 + '-'+ 
abs(DateDiff(d, DateAdd(m, DateDiff(m, @t_ldat, getdate()), @t_ldat), getdate()))as Combined
...

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cardgunner

326 Posts

Posted - 2007-04-23 : 14:07:33

That worked the the negative amounts. Altough I think I'm missing a day.

2005-04-30 00:00:00.000 2 0 7 -5
2005-05-31 00:00:00.000 1 11 7 5

I will have to do some math but should both of these be 7 days? It LOOKS like I'm a day short or a day long. Hmmm.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-23 : 14:13:40

I did not change any of your existing logic except to add an ABS around the expression.
Consider an example:

select abs(DateDiff(d, DateAdd(m, DateDiff(m, '2005-04-22 00:00:00.000', getdate()), '2005-04-22 00:00:00.000'), getdate())) as Days

will return 1 day. Now do you consider yesterday and today as 2 days accodring to your business logic or just the absolute difference which is 1 day? You need to change your logic accordingly.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cardgunner

326 Posts

Posted - 2007-04-23 : 14:43:39

The logic is right, 1 day. I'm good.

Now if I can only get this to read as 2-11-7. Care to take a stab? I'm been all over the forum searching. I may not be using the right words. In Access I'd call it a text string.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-23 : 16:18:39

Change your Years part to :


DateDiff(year, @t_ldat, getdate()) as Years,

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cardgunner

326 Posts

Posted - 2007-04-23 : 17:01:23

I'm sorry dinakar. But what then would I have to declare. I have not taught myself this yet. From here I think it may be
DECLARE @t_ldat VARCHAR(20)
I tried and it gave me NULL value in the fields.

I feel I'm on the cusp of this please stay with me.

cardgunner

326 Posts

Posted - 2007-04-23 : 17:12:16

Why can't I use this to get my desired 2-11-7

use cardgunnerdb
select
varchar ((DateDiff(m, t_ldat, getdate())/12 + '-'+ DateDiff(m, t_ldat, getdate())%12 + '-'+ DateDiff(d, DateAdd(m, DateDiff(year, t_ldat, getdate()), t_ldat), getdate()))as Combined
from
ttfext200100
where
t_fpln='351'
go

It lloks k=like it almost will work except I get
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'from'.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-23 : 17:41:12

What I meant was:


select 
DateDiff(year, @t_ldat, getdate()) as Years,
DateDiff(m, @t_ldat, getdate())%12 as Months, 
abs(DateDiff(d, DateAdd(m, DateDiff(m, @t_ldat, getdate()), @t_ldat), getdate())) as Days,
DateDiff(m, @t_ldat, getdate())/12 + '-'+
DateDiff(m, @t_ldat, getdate())%12 + '-'+ 
abs(DateDiff(d, DateAdd(m, DateDiff(m, @t_ldat, getdate()), @t_ldat), getdate()))as Combined

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cardgunner

326 Posts

Posted - 2007-04-24 : 08:02:42

I did try that and I got the error message:

Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@t_ldat'

Cardgunner

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 08:07:34

quote:
Originally posted by cardgunner

I did try that and I got the error message:

Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@t_ldat'

Cardgunner

You have to declare that variable.

cardgunner

326 Posts

Posted - 2007-04-24 : 09:22:17

That is what I thought. However I don't have any idea what a declare is other then I see them in the forums and such. I bought a book but haven't figured it out.

Declare @t_ldat ????

I'll look harder online and in the forums to find what I'm looking for.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 09:29:26

@t_ldat is a variable. You can declare it as follows.


declare @t_ldat datetime

dinakar uses it to test the code.

For you case, you just replace the @t_ldat with your column name


select 
DateDiff(year, t_ldat, getdate()) as Years,
DateDiff(m, t_ldat, getdate())%12 as Months, 
abs(DateDiff(d, DateAdd(m, DateDiff(m, t_ldat, getdate()), t_ldat), getdate())) as Days,
DateDiff(m, t_ldat, getdate())/12 + '-'+
DateDiff(m, t_ldat, getdate())%12 + '-'+ 
abs(DateDiff(d, DateAdd(m, DateDiff(m, t_ldat, getdate()), t_ldat), getdate()))as Combined
from
ttfext200100
where
t_fpln='351'

cardgunner

326 Posts

Posted - 2007-04-24 : 10:42:42

Khtan,

I appreciate your help. I'm getting nowhere though on being able to take the results of the three columns(Year-Month-Day) and put them in one column as text such as 2-11-7. This is to represent that 2Y-11M-7D have passed since a certain date(t_ldat). I may not be explaining it right or it might be harder then I thought.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 10:48:59

quote:
Originally posted by cardgunner

Khtan,

I appreciate your help. I'm getting nowhere though on being able to take the results of the three columns(Year-Month-Day) and put them in one column as text such as 2-11-7. This is to represent that 2Y-11M-7D have passed since a certain date(t_ldat). I may not be explaining it right or it might be harder then I thought.

Just re-read the posts again. Basically what you want is to find out the different of certain date (t_ldat) compare with current date and express in Y-M-D ?

cardgunner

326 Posts

Posted - 2007-04-24 : 11:07:46

I did. I'm not sure what I'm doing wrong then
Because if I do it this I get the actual null values in all the records
declare @t_ldat datetime
select
DateDiff(year, @t_ldat, getdate()) as Years,
DateDiff(m, @t_ldat, getdate())%12 as Months,
abs(DateDiff(d, DateAdd(m, DateDiff(m, @t_ldat, getdate()), @t_ldat), getdate())) as Days,
DateDiff(m, @t_ldat, getdate())/12 + '-'+
DateDiff(m, @t_ldat, getdate())%12 + '-'+
abs(DateDiff(d, DateAdd(m, DateDiff(m, @t_ldat, getdate()), @t_ldat), getdate()))as Combined
from
ttfext200100
where t_fpln='351'
go
results
Years Months Days Combined
----------- ----------- ----------- -----------
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL NULL NULL

If I take out the declare
select
DateDiff(year, t_ldat, getdate()) as Years,
DateDiff(m, t_ldat, getdate())%12 as Months,
abs(DateDiff(d, DateAdd(m, DateDiff(m, t_ldat, getdate()), t_ldat), getdate())) as Days,
DateDiff(m, t_ldat, getdate())/12 + '-'+
DateDiff(m, t_ldat, getdate())%12 + '-'+
abs(DateDiff(d, DateAdd(m, DateDiff(m, t_ldat, getdate()), t_ldat), getdate()))as "Combined Y-M-D"
from
ttfext200100
where t_fpln='351'
go

results
Years Months Days Combined Y-M-D
----------- ----------- ----------- --------------
3 11 11 24
3 4 11 17
2 0 10 12
2 10 6 17

And the years are off by 1.

What I started with after the abs correction
select
DateDiff(m, t_ldat, getdate())/12 as Years,
DateDiff(m, t_ldat, getdate())%12 as Months,
abs(DateDiff(d, DateAdd(m, DateDiff(m, t_ldat, getdate()), t_ldat), getdate())) as Days,
DateDiff(m, t_ldat, getdate())/12 + '-'+
DateDiff(m, t_ldat, getdate())%12 + '-'+
abs(DateDiff(d, DateAdd(m, DateDiff(m, t_ldat, getdate()), t_ldat), getdate()))as "Combined Y-M-D"
from
ttfext200100
where t_fpln='351'
go
results
Years Months Days Combined Y-M-D
----------- ----------- ----------- --------------
2 11 11 24
2 4 11 17
2 0 10 12
2 0 6 8
1 11 6 18
1 9 23 33
1 10 6 17

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 11:33:41

Borrowing MVJ's F_AGE_YYYY_MM_DD function from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729
with slight modification to your requirement.
change the last section select @AGE to


select @AGE = 	convert(varchar(4),@AGE_IN_YEARS)	+ '-' + 
		convert(varchar(4),@AGE_IN_MONTHS)	+ '-' +
		convert(varchar(4),@AGE_IN_DAYS)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-24 : 11:40:50

Post some data you have in your table and the result you are expecting.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

cardgunner

326 Posts

Posted - 2007-04-24 : 13:04:28

Ok Let's start over. I have a table that has the follwing field name on the records in that field.(This is a sample of the 1000's of records)

t_ldat
------------------------------------------------------
2004-05-13 00:00:00.000
2004-12-13 00:00:00.000
2005-04-14 00:00:00.000
2005-04-30 00:00:00.000
2005-05-31 00:00:00.000
2005-07-01 00:00:00.000
2005-06-30 00:00:00.000

I want to subtract t_ldat from today and get a reult in AY-BM-CD in one field with many records. The end result would look like the following

t_ldat Combined
------------------------------------------------------
2004-05-13 00:00:00.000 2Y-11M-11D
2004-12-13 00:00:00.000 2Y-4M-11D
2005-04-14 00:00:00.000 2Y-0M-10D
2005-04-30 00:00:00.000 2Y-0M-6D
2005-05-31 00:00:00.000 1Y-11M-6D
2005-07-01 00:00:00.000 1Y-9M-12D
2005-06-30 00:00:00.000 1Y-10M-6D

With the addition of the Combined column I can tell how old this is and the people I report to won't have to do much math. It will look good too.

I think the you are great for sticking with me, I'm sure you have better things to do then help an amateur. How did you all learn what you know?

Card Gunner

cardgunner

326 Posts

Posted - 2007-04-24 : 13:09:01

Sorry the spacing didn't come out t_ldat and combuned are 2 different columns(Fields). T_ldat records stop at the mllisecond and Combuined starts after that.

Card Gunner

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-24 : 13:13:13

[code]
select
convert(varchar,DateDiff(m, t_ldat, getdate())/12) + 'Y' + '-' +
convert(varchar,DateDiff(m, t_ldat, getdate())%12) + 'M' + '-' +
convert(varchar,abs(DateDiff(d, DateAdd(m, DateDiff(m, t_ldat, getdate()), t_ldat), getdate()))) + 'D' as combined
from
ttfext200100
where t_fpln='351'

[/code]

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources