system
July 1, 2011, 11:13am
#1
please help
I have to take the date from user…and extract the last 12 onth data from this date using month…for example if user enters 2nd june 1011, i need to publish data from 2nd june 2010…how to do this…??
newbouser23 (BOB member since 2011-07-01)
system
July 1, 2011, 11:17am
#2
Hi,
What database do you use? The syntax depends on it.
Check also relative dates for SQL Server and Oracle here:
As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server.
Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8.
Yesterday
cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime)
Today
cast(convert(char(10),getdate(),23) as datetime)
The following also works for today:
dateadd(dd, datediff(dd,0,getdate()), 0)
Start of Current Month
cast(convert(char(7),getdate(),23)+'-01' as datetime)
End of Current Month
dateadd(d,-1…
Marek Chladny (BOB member since 2003-11-27)
system
July 1, 2011, 11:22am
#3
Try this (i’ve used sysdate), you can replace sysdate with your date prompt
trunc(sysdate) - 365
Note: Oracle Syntax
jprasanthram (BOB member since 2010-12-10)
system
July 1, 2011, 11:25am
#4
tera data
newbouser23 (BOB member since 2011-07-01)
system
July 1, 2011, 12:11pm
#5
extract(year from your_date) - 365.
I dont have teradata to test this out, so please try and post if this works for you.
jprasanthram (BOB member since 2010-12-10)
MarkP
July 4, 2011, 8:37am
#6
Here’s the thread for Teradata relative dates:
To add to the existing Oracle and SQL Server lists, here are my versions of the Teradata equivalents. Perhaps after any discussion and validation, they can be added to the same ‘Sticky’:
Yesterday:
DATE-1
Today:
DATE
Start of Current Month:
(DATE - DAYOFMONTH(DATE))+1
End of Current Month:
ADD_MONTHS((DATE - DAYOFMONTH(DATE))+1,1)-1
Start of Previous Month:
ADD_MONTHS((DATE - DAYOFMONTH(DATE))+1,-1)
End of Previous Month:
(DATE - DAYOFMONTH(DATE))
Start of Current Quarter:
CASE WHEN MONT…