fetching date from 1 sp to another

  • hbtkp (4/15/2012)


    i am already have training in sql, this is somehow tricky ,that dates is not working after feb

    No, it's not at all tricky. You take the code I posted and you use it. It shows you how to increment by month and get the last day of the month. All there, copy it into management studio and run it unchanged and you should see that it does work. Between the last two pieces of code I posted you should also be able to figure out how to use it in your 'match function' loop.

    As for finding the first day of the month:

    http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/

    p.s. You need training. This stuff we're talking about is very basic T-SQL and you're struggling with it. Hence you need training or help. Speak to your colleagues, speak to your boss.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • In case you missed it. This is a loop that produces (see the PRINT) the last day of each month, it does so correctly for all months, including after February.

    GilaMonster (4/15/2012)


    Because this:

    DECLARE @row1 datetime, @Todate DATETIME

    set @row1 = '2010/02/01' -- however it gets assigned,

    set @Todate = '2011/02/01' -- however it gets assigned,

    while(@row1 <= @Todate)

    BEGIN

    PRINT CONVERT(VARCHAR(28), DATEADD(dd,-1,@row1), 121)

    set @row1 = DATEADD(MONTH , 1 , @row1)

    end

    gives this as the DATEADD(dd,-1,@row1) values

    2010-01-31 00:00:00.000

    2010-02-28 00:00:00.000

    2010-03-31 00:00:00.000

    2010-04-30 00:00:00.000

    2010-05-31 00:00:00.000

    2010-06-30 00:00:00.000

    2010-07-31 00:00:00.000

    2010-08-31 00:00:00.000

    2010-09-30 00:00:00.000

    2010-10-31 00:00:00.000

    2010-11-30 00:00:00.000

    2010-12-31 00:00:00.000

    2011-01-31 00:00:00.000

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • i havent missed anything.

    can you please rutn this code,you will know

    DECLARE @row1 datetime, @Todate DATETIME

    set @row1 = '2010/09/30' -- however it gets assigned,

    set @Todate = '2011/12/31' -- however it gets assigned,

    while(@row1 <= @Todate)

    BEGIN

    PRINT CONVERT(VARCHAR(28), DATEADD(dd,-1,@row1), 121)

    set @row1 = DATEADD(MONTH , 1 , @row1)

    end

    my @row1 will be like this date ,if you run this one ,it wont work

    run exactly what i have posted

  • hbtkp (4/15/2012)


    i havent missed anything.

    Yes, you have. You've missed the fact that the @row needs to be set to the FIRST DAY OF THE MONTH.

    I've said that three times now. FIRST DAY OF THE MONTH, not the last day, as it is in the code that I have posted more than once. If you're not going to read what I write, I'm outa here.

    For the 4th time...

    DECLARE @row1 datetime, @Todate DATETIME

    set @row1 = '2010/02/01' -- FIRST DAY OF THE MONTH!!!!!!!!!!!!!!!!!

    set @Todate = '2011/02/01' -- however it gets assigned,

    while(@row1 <= @Todate)

    BEGIN

    PRINT CONVERT(VARCHAR(28), DATEADD(dd,-1,@row1), 121)

    set @row1 = DATEADD(MONTH , 1 , @row1)

    end

    run exactly what i have posted

    How about you run exactly what I've posted and see that it really does work. As in exactly what I posted, without any modifications including to the start date. And the link I posted shows you how to get the first day of the month if any date is given.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • i cant set @row1 first day of the month,it can be any date. its not hard coded,user will enter it and it could be any day

  • GilaMonster (4/15/2012)


    And the link I posted shows you how to get the first day of the month if any date is given.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • finally got it ,with this code

    DECLARE @row1 datetime, @Todate DATETIME

    set @row1 = '2010/09/30' -- however it gets assigned,

    set @Todate = '2011/12/31' -- however it gets assigned,

    while(@row1 <= @Todate)

    BEGIN

    PRINT DATEADD(dd,-DAY(DATEADD(m,1,@row1)),DATEADD(m,1,@row1))

    set @row1 = DATEADD(MONTH , 1 , @row1)

    end

  • hbtkp (4/15/2012)


    Lynn,

    i am already have training in sql, this is somehow tricky ,that dates is not working after feb

    Nothing you have been given is tricky. It is all basic t-sql. You need more training. You can't. Even debug basic code or code logic.

  • ok. 1 more prob.

    my function from which i need to fetch data @Reportdata3 , has fromdate and todate

    so when i execute my sp exec dashboard

    i am supplying fromdate and todate

    so item3 is taking that date and generate data.

    now i need to update search for @row1 date is there into fromdate, but it just gives me whatever i am supplying.

    i need to update fromdate = @row1 in loop.if i put in where condition,it doesnt give me date

    how to update in udf

  • i've nothing to add to this post but had to respond...

    Gila and others, you have amazing patience and your work with the community on this website deserves much thanks.

    For people to come here and ask questions (good or bad) and get the level of help you give is nothing short of outstanding.

    I'm not sure i saw a single thank you from the op, so i figured i'd give it 🙂

  • +1 on dat Jeff.

    My Sincere thanks to everyone.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • i hv this formula for cumulative

    (exp(sum(LOG(CASE WHEN I1 = 0.0 THEN 1 WHEN I1 IS NULL THEN 1 WHEN I1= -100 THEN 1 ELSE 1+I1/100 END)))/-1)* 100,

    i am getting error msg like

    Msg 8115, Level 16, State 2,

    Arithmetic overflow error converting expression to data type float.

  • hbtkp (4/17/2012)


    Arithmetic overflow error converting expression to data type float.

    Use Google and search for that exact message. You will get lots of articles that will help you troubleshoot the issue.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 13 posts - 76 through 87 (of 87 total)

You must be logged in to reply to this topic. Login to reply