Teradata SQL date functions


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Teradata SQL date functions

  1. #1
    R. Jacildo Guest

    Teradata SQL date functions


    My copany is converting from Oracle to Teradata. I'm trying to figure out
    how to pull the first and last day of the month in a query. In Oracle, its:

    SELECT LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE)-1,-1))+1 FROM DUAL;
    SELECT LAST_DAY(TRUNC(SYSDATE)-1) FROM DUAL;

    Any advise would be appreciated!


  2. #2
    Join Date
    Jul 2007
    Posts
    1

    Teradata SQL Date Functions

    create table tab1(col1 DATE);

    ins tab1(DATE'1999-03-04');
    ins tab1(DATE'1999-04-05');
    ins tab1(DATE'1980-02-12');
    ins tab1(DATE'1979-02-10');

    You can do this is 2 ways.

    1st Method.


    select extract(month from col1) as monthofyear,
    CASE extract(year from col1) MOD 5
    WHEN 0 THEN
    CASE monthofyear
    WHEN 1 THEN 31
    WHEN 2 THEN 29
    WHEN 3 THEN 31
    WHEN 4 THEN 30
    WHEN 5 THEN 31
    WHEN 6 THEN 30
    WHEN 7 THEN 31
    WHEN 8 THEN 31
    WHEN 9 THEN 30
    WHEN 10 THEN 31
    WHEN 11 THEN 30
    WHEN 12 THEN 31
    END
    ELSE
    CASE monthofyear
    WHEN 1 THEN 31
    WHEN 2 THEN 28
    WHEN 3 THEN 31
    WHEN 4 THEN 30
    WHEN 5 THEN 31
    WHEN 6 THEN 30
    WHEN 7 THEN 31
    WHEN 8 THEN 31
    WHEN 9 THEN 30
    WHEN 10 THEN 31
    WHEN 11 THEN 30
    WHEN 12 THEN 31
    END
    END

    as lastday from tab1;

    2nd method (simple method):

    select extract(month from col1) as monthofyear,
    CASE monthofyear
    WHEN 1 THEN 31
    WHEN 2 THEN
    CASE extract(year from col1) MOD 5
    WHEN 0 THEN 29
    ELSE 28
    END

    WHEN 3 THEN 31
    WHEN 4 THEN 30
    WHEN 5 THEN 31
    WHEN 6 THEN 30
    WHEN 7 THEN 31
    WHEN 8 THEN 31
    WHEN 9 THEN 30
    WHEN 10 THEN 31
    WHEN 11 THEN 30
    WHEN 12 THEN 31
    END


    as lastday from tab1;



    regards,
    Nagaraju.V

  3. #3
    Join Date
    Feb 2008
    Posts
    162
    For this kind of thing I always recommend creating a date dimension table. I have a table like this in my database and I can't tell you how much time it has saved me.

    See my comments in post #3 of this thread:
    http://forums.devx.com/showthread.php?t=169169

    I did have to add CalendarMonthStartDate and CalendarMonthEndDate columns as we didn't have them in our table because we work mostly in Broadcast Months. But I can now get what you want from this table by doing:

    Code:
    SELECT 
        MIN(CalendarDate) FirstDay,
        MAX(CalendarDate) LastDay
    FROM dwCalendars
    WHERE GETDATE() BETWEEN CalendarMonthStartDate AND CalendarMonthEndDate
    The point of this table is that it can solve not only this problem but numerous other problems and give you the answer without having to do any complex date calculations. When you need something like above, you can just whip up a simple query and have the answer in a couple of minutes.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center