All Forums Database
DaZzL3R 20 posts Joined 12/06
02 Mar 2010
'Date' BETWEEN 'select Date'

I want to get month end date of 2 months prior. For instance, if i run the SQL today, i should get 2010-01-31 where the question marks are in the SQL below. Mind you, I cannot use volatile table.

"sel (current_date - extract(day from current_date)) -
extract(day from (current_date - extract(day from current_date))) " gets me 2010-01-31 but I dont know how to use it in the following query to get 2010-01-31. One more thing, I am getting prevoius month's date (2010-02-28) as parameter for other stuff. I tried ADD_MONTHS function but it did not work as i wanted it to be.
any help is appreciated.

Select DateColum, COL2 FROM DB.TableName
Where DateColum between '2008-04-01' and ??????

Rajesh.Kumar 1 post Joined 07/09
02 Mar 2010

Hi,
There is a way you can use this but I'm not sure it suits ur requirement or not.

Select
DateColum
, COL2
, (current_date - extract(day From current_date)) - extract(day From (current_date - extract(day From current_date))) AS "Day"
FROM DB.TableName
WHERE DateColum BETWEEN '2008-04-01' AND "Day"

DaZzL3R 20 posts Joined 12/06
03 Mar 2010

Thanks Rajesh for the solution. Below are two ways to accomplish it without refrencing "Day" since it wasn't working outside of subquery. Following two worked perfectly, so I am sharing with the Forum:

1)
Select DateColum, COL2 FROM DB.TableName
Where DateColum between '2008-04-01' and (current_date - extract(day from current_date)) - extract(day from (current_date - extract(day from current_date)))

2)
Select DateColum, COL2 FROM DB.TableName
Where DateColum between '2008-04-01' and add_months((((substr('2010-02-28',1,8)||'01')(DATE, FORMAT 'YYYY-MM-DD')) -1), -0)

PeriphVision 1 post Joined 03/14
27 Mar 2014

Similar to the above question I would like to find a date range based on current columns.
In my table I have a column called Mthend which is a fixed date eg 2014-02-28
I would like a piece of code that I can use without having to put any fixed dates in a where statement so it works out what the start of that month is and returns a between eg
If Mthend = 2014-02-28 then range would be between MnthStart and Mthend and the result would check from 2014-02-01 and 2014-02-28.
I am very new to Teradata SQL so any help is very much appreciated.
Thanks
Lee

22 Nov 2015

Hello every one ...
I need a help 
I have two tables and there is common field between those two table that is 'DP_INSERT_DATE' and the format of the date is "YYYY-MM-DD.T.MM:SS:(time zone also)" 
I need to join the tables using DP_INSERT_DATE and get the information....
Can you please help in the query thanks alot.
You can mail me at vinit.tippu@gmail.com
 
 
******Urgent*********

dins2k2 51 posts Joined 05/13
23 Nov 2015

Hi Lee,
Here is the sql which gives you first and last day of the given date(2014-03-28) of the month.
 
select (ADD_MONTHS(cast('2014-03-28' as date) - EXTRACT(day FROM cast('2014-03-28' as date)) + 1,0)) as MnthStart 
,(ADD_MONTHS(cast('2014-03-28' as date) - EXTRACT(day FROM cast('2014-03-28' as date)) + 1,1) -1) as MnthEnd
 
Thanks,
Dinesh

You must sign in to leave a comment.