Sybase stored procedure -- getting last month's date


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Sybase stored procedure -- getting last month's date

  1. #1
    Join Date
    Oct 2005
    Location
    New Jersey
    Posts
    255

    Question Sybase stored procedure -- getting last month's date

    This is my first question ever on any forum.

    I am working on a Sybase procedure where I need to get last month's date {as MMddyyyy} to filter records against a date field. The way I am currently doing it seems to be rather patchy though it is working.

    Code:
    CREATE PROC sy_previous_month_report AS
      BEGIN
       DECLARE @month       CHAR(3) ,
               @year        CHAR(4) ,
               @date        CHAR(12)
    
        SELECT @month = substring(convert(char(20), dateadd(mm, -1, getdate()), 106), 4, 3)
        SELECT @year  = substring(convert(char(20), getdate(), 106), 8, 4)
        IF @month = "Dec"
             SELECT @year  = substring(convert(char(20), dateadd(yy, -1, getdate()), 106), 8, 4)
    
        SELECT @date = @month+" "+@year+"%"
        ...
    Is there a better way of doing this?

    TIA!
    Happiness is good health and a bad memory.

  2. #2
    Join Date
    Oct 2005
    Location
    New Jersey
    Posts
    255
    I guess I should have done a little search first. I managed to reduce that part of the code down to this... eliminates a few checks.

    Code:
    CREATE PROC test_proc AS
      BEGIN
        DECLARE @dtstr  VARCHAR(20),
                @month  CHAR(3),
                @year   CHAR(4)
    
        SELECT @dtstr = convert(char(20), dateadd(mm, -2, getdate()))
        SELECT @month = substring(@dtstr, 1, 3)
        SELECT @year  = substring(@dtstr, 8, 4)
    
        SELECT @dtstr = @month +"%" +@year +"%"
        SELECT COUNT(*) FROM UserTable WHERE CreatedDate LIKE @dtstr
    
      END
    If anyone could suggest something simpler...
    Happiness is good health and a bad memory.

Similar Threads

  1. Stored Procedure SQL Server 2000
    By Michael in forum Database
    Replies: 2
    Last Post: 03-17-2005, 12:07 PM
  2. Stored procedure
    By Geetha in forum Database
    Replies: 3
    Last Post: 09-12-2001, 05:28 PM
  3. Replies: 4
    Last Post: 04-22-2001, 11:35 PM
  4. Replies: 0
    Last Post: 10-02-2000, 01:25 PM
  5. date parameter in Oracle Stored Procedure
    By Naveen in forum VB Classic
    Replies: 1
    Last Post: 06-13-2000, 07:05 PM

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