fetching date from 1 sp to another

  • I give up. You need to figure out how to do basic trouble shooting.

  • hbtkp (4/14/2012)


    they both are datetime.

    how to compare two dates with <=

    Oddly enough, I showed you in an earlier piece of code...

    IF (@Date1 <= @Date2)

    Simple as that.

    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
  • ok the condition is working,

    but i have problem with if(stat)

    whats wrong in that

  • To hbtkp,

    You probably don't want to here this but how about learning some SQL before asking increasingly frustrating questions.

    Try this...

    Introduction to SQL 2008

    http://www.microsoft.com/learning/en/us/course.aspx?id=2778a

    If your boss thinks you know SQL already then he is wrong.

    Reading the forum posts...you don't seem to understand ddl, tables, functions, date datatype, how to ask for help correctly.

    Given create statements for tables, some sample (made up non confidential) data, and expected results then you may get help.

    Fitz

    [/left]

  • hbtkp (4/15/2012)


    ok the condition is working,

    but i have problem with if(stat)

    whats wrong in that

    Without a lot more details and sample data, no idea. Start by looking at the values of the variables and what's returned from the function and you might be able to see what's wrong.

    That said, I think you need to sit down with a colleague who know T-SQL and get some help from someone who can see what's going on. That or ask your boss for a basic 'introduction to T-SQL' course.

    We could spend weeks on this and it's getting pretty clear that you need some basic knowledge that at this point you don't have.

    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 know basic,

    i am just trying to get idea.

    the prob is

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

    so if i have date @row1 = 01/31/2010

    next @row1 would be 02/28/2010

    and next would be 03/28/2010(which is wrong)

    how add exact month

    so that i can get 03/31/2010

    next 04/30/2010

  • Easier to dateadd the 1st of the month, then subtract a day.

    So, rather set @row1 = 2010/02/01 (btw, use the yyyymmdd format as the others are ambiguous)

    then dateadd will always produce the next 1st day of the month. You can subtract a day to get the last day of the previous month when you check the functions

    So, roughly

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

    while(@row1 <= @Todate)

    begin

    if ( DATEADD(dd,-1,@row1) not in(select fromdate from item2(@ReportData1)))

    insert into #temp4

    select I1 from item3(@Reportdata3) where fromdate = DATEADD(dd,-1,@row1)

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

    end

    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
  • this is not working

    i am getting date liek this

    2010-10-31 00:00:00.000

    2010-11-30 00:00:00.000

    2010-12-30 00:00:00.000

    2011-01-30 00:00:00.000

    2011-02-28 00:00:00.000

    2011-03-28 00:00:00.000

    its just adding month, not the exact day ,i tried your code,still not getting

  • Then you have changed the code I gave you and broke it somehow.

    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

    I will suggest again, sit with a colleague who knows SQL or ask your boss for a SQL course.

    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
  • nope ,i have chnage anything

    the prob is month of feb,is you try this

    DECLARE @row1 datetime, @Todate DATETIME

    set @row1 = '2010/02/28' -- 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

    you will know after feb its not incremeting

  • hbtkp (4/15/2012)


    nope ,i have chnage anything

    Really?????? You didn't change anything?

    Your code:

    the prob is month of feb,is you try this

    DECLARE @row1 datetime, @Todate DATETIME

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

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

    My code:

    DECLARE @row1 datetime, @Todate DATETIME

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

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

    In bold is a difference. Hence, you changed something and it broke as a result.

    I said to set the date to the FIRST day of the month. You went and set it to the last and claim that my code (which you changed) doesn't work. My code does, your modification does not. Set the date to the FIRST DAY OF THE MONTH and use DATEADD to get the last day, then the month increment will work correctly, as I showed with the results I posted from my code.

    If you're not willing to read and follow simple advice, I'm done. Sit with a colleague who knows T-SQL or go to your boss and ask for help.

    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 knwo tits working for first day of month.

    but my req is if i enter any date in a month, i should get next month first day

  • usually i need t put month last day ,not first day

    for example.

    9/30/2010

    can be my row1,so after coming to feb it just adding month ,not days

  • Right now I am in a car heading east on I40 heading toward New Mexico. I am not even going to try giving you any code. I'd tell you to follow the link to my blog site and look for the post regarding common date functions, but you probably wouldn't be able to figure out how to even use them.

    Sit with a coworker or go tell your boss that you need more training in T-SQL and Microsoft SQL Server.

  • Lynn,

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

Viewing 15 posts - 61 through 75 (of 87 total)

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