Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    14

    Question Unanswered: Number of days in a month w/ Sybase tSQL?

    I have an *interesting* date calcluation that I need to do for a client, but I don't know if it can even be done with Sybase tSQL.

    The calculation involves subtracting a number of days from the number of days in a specific month... i.e., If the month is December, I'd need to find the number of days in that month, which is 31, and subtract a value from that.

    I don't know if there is a way to get the number of days in a month with Sybase tSQL. I thought about doing a CASE statement to match the month with the number of days in the month (using DATEPART() or something like that), but then I have a problem with February on leap-years

    Do any of you have any thoughts on how I can find the number of days in a month?
    Sincerely,
    Todd M. Taylor
    http://www.toddtaylor.com/

  2. #2
    Join Date
    Oct 2003
    Posts
    14

    Re: Number of days in a month w/ Sybase tSQL?

    Wonder if this will work...

    Last Day of Current Month

    Now to get the last day of the current month I need to modify slightly the query that returns the last day of the prior month. The modification needs to add one to the number of intervals return by DATEDIFF when comparing the current date with "1900-01-01." By adding 1 month, I am calculating the first day of next month and then subtraction 3 milliseconds, which allows me to arrive at the last day of the current month. Here is the TSQL to calculate the last day of the current month.

    Code:
    select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))
    http://www.databasejournal.com/featu...le.php/3076421
    Sincerely,
    Todd M. Taylor
    http://www.toddtaylor.com/

  3. #3
    Join Date
    Oct 2003
    Posts
    14

    Re: Number of days in a month w/ Sybase tSQL?

    Thanks Me for solving my own problem

    Just in case some other poor slob has the same problem I do, here's my solution:

    Code:
    DATEPART(dd,(DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,'Jan 01, 1900 00:00:00',[YOUR_DATE_HERE])+1,'Jan 01, 1900 00:00:00')))
    If you exchange [YOUR_DATE_HERE] with a date like 08/18/2003, you should get 31 in return because there are 31 days in August.
    Sincerely,
    Todd M. Taylor
    http://www.toddtaylor.com/

  4. #4
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    G'day Todd,

    I have this proc that I use from long since. I'm not sure the source, probably from Ed Barlow or Rob Verschoor

    create proc sp_days @days tinyint OUTPUT, @month tinyint, @year smallint
    as
    declare @date datetime
    select @date=convert(char,@month)+"/01/"+convert(char, @year)
    select @days=datediff(dd,@date, dateadd(mm,1,@date))
    select @days
    go

    grant exec on sp_days to public
    go

    declare @days int
    -- For December 2003
    exec sp_days @days,12,03
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •