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.
| Author |
Topic |
|
kemi2299
Starting Member
41 Posts |
 Posted - 2008-04-23 : 06:55:05
|
| hi,thanks for the other problem you solved for me.pls could you help me with this, particularly the interval function, i have tried this in so many ways but i am not getting the right date. your help is crucial and appreciateddays_Susp=INT2(INTERVAL('days',DATE_TRUNC('days',Suspension_End_Dt)- --DATE_TRUNC('days',Suspension_St_Dt))); |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-23 : 06:57:33
|
Days_Susp = DATEDIFF(DAY, Suspension_St_Dt, Suspension_End_Dt)
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
kemi2299
Starting Member
41 Posts |
Posted - 2008-04-24 : 07:11:52
|
| hi,could you pls have a look at this query again, as i am getting wrong date, I am getting 1900-02-19the problem is:days_Susp=INT2(INTERVAL('days',DATE_TRUNC('days',Suspension_End_Dt)-DATE_TRUNC('days',Suspension_St_Dt)));suggested solution : Days_Susp = DATEDIFF(DAY, Suspension_St_Dt, Suspension_End_Dt)thanks |
|
|
|
kemi2299
Starting Member
41 Posts |
Posted - 2008-04-24 : 07:36:54
|
| hi,could you pls have a look at this query again, as i am getting wrong date, I am getting 1900-02-19the problem is:days_Susp=INT2(INTERVAL('days',DATE_TRUNC('days',Suspension_End_Dt)-DATE_TRUNC('days',Suspension_St_Dt)));suggested solution :Days_Susp = DATEDIFF(DAY, Suspension_St_Dt, Suspension_End_Dt)thanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-24 : 07:40:22
|
What do you expect to be returned?DATE_TRUNC truncates the datetime columns Suspension_End_Dt and Suspension_End_Dt to full day (truncating time part).The style "DAYS" should read "DAY" as standard.Now you have two truncated dates and you subtract them.Then you take the INTERVAL function with DAYS (again DAY is correct) as argument.That returns number of days between the two truncated dates.And at last you convert the difference in days with INT2All this is exactly what DATEDIFF does.Your problem is that you then assign the difference in days (49 days) to a datetime variable which converts the 49 days to a date, which eventually is 1900-02-19.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
kemi2299
Starting Member
41 Posts |
Posted - 2008-04-24 : 07:54:27
|
| but this date is in century date, it should be in 2008 as that is the date entered for the suspension date |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-24 : 08:00:59
|
What dodays_Susp = INT2(INTERVAL('days',DATE_TRUNC('days', Suspension_End_Dt) - DATE_TRUNC('days', Suspension_St_Dt)));return in your PostGreSQL installation?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
|
|
INTERVAL in tsql