'Date' BETWEEN 'select Date'

Database
Enthusiast

'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 ??????

5 REPLIES

Re: 'Date' BETWEEN 'select Date'

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"

Enthusiast

Re: 'Date' BETWEEN 'select Date'

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)

Re: 'Date' BETWEEN 'select Date'

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

Re: 'Date' BETWEEN 'select Date'

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*********

Enthusiast

Re: 'Date' BETWEEN 'select Date'

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