Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2010
    Posts
    3

    Unanswered: extract date time of the month

    i am trying get the date from the month. but three months gap.I am trying to extract Date 1st and tenth of the month.
    i am sending the code also please someone correct the logic ...code given below...

    -- exec date_validate 'mcl','rnv','02260'
    alter proc date_validate
    (

    @company glcompany,
    @locn gllocn ,
    @emp_code varchar(6)
    )

    as
    begin

    DECLARE @month_date varchar(25)
    select @month_date = GETDATE()
    --SELECT CONVERT(VARCHAR(10),@month_date(), 103)

    if(@month_date = ' JANUARY')
    begin


    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@month_date)-1),@month_date),103) AS cur_date,--'First Day of Current Month'
    CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@month_date))),DATEADD(dd,10,@mo nth_date)),103) as last_date --'Tenth Day of Current Month'
    end

    else if(@month_date = ' /03/ ')
    begin
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@month_date)-1),@month_date),103) AS cur_date,--'First Day of Current Month'
    CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@month_date))),DATEADD(dd,10,@mo nth_date)),103) as last_date --'Tenth Day of Current Month'
    end

    else if(@month_date = ' /06/ ')
    begin
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@month_date)-1),@month_date),103) AS cur_date,--'First Day of Current Month'
    CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@month_date))),DATEADD(dd,10,@mo nth_date)),103) as last_date --'Tenth Day of Current Month'
    end

    else if(@month_date = ' /09/ ')
    begin
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@month_date)-1),@month_date),103) AS cur_date,--'First Day of Current Month'
    CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@month_date))),DATEADD(dd,10,@mo nth_date)),103) as last_date --'Tenth Day of Current Month'
    end
    ELSE
    BEGIN
    'DATE NOT APPLICABLE...'
    END


    end


    PLEASE SOMEONE CHECK AND CORRECT THE CODE.
    lOGIC TRYING TO PUT IS
    IF(@MONTH_DATE ='APRIL')
    THEN @MONTH_DATE WILL SHOW ELSE DISPLAY DATE NOT APPLICABLE

    THANKS A LOT....

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please post a representative set of dates and the output you want for those dates. Code that does not work is not much use as a guide I am afraid.

    Also, I am correct in thinking that the parameters for this procedure are not used yes?

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    -- Create some test data
    declare @tab table (col1 datetime)
    
    insert into @tab
    select '1/20/2010' union
    select '2/10/2010' union
    select '3/15/2010' union
    select '4/04/2010' union
    select '5/21/2010' union
    select '8/20/2008' 
    
    -- Do the calculation
    select dateadd(mm, datediff (mm, 0, col1), 0), 
    	dateadd(dd, 9, dateadd(mm, datediff (mm, 0, col1), 0))
    from @tab

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh is that it?
    So what's the deal with "but three months gap" and only APRIL being applicable?

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    No idea. I figured I would fulfill this part of the requirement:
    Quote Originally Posted by op
    extract Date 1st and tenth of the month.
    , and let him figure out if it was even close. If not, then the OP can say what he really wants.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a pure "shot in the dark" based on your sample code, it may or may not resemble what you really want.
    Code:
    DECLARE @d			DATETIME
    
    SET @d = '2010-03-30'
    
    SELECT DateAdd(month, DateDiff(month, 0, @d), 0) AS 'First'
    ,  DateAdd(day, 9, DateAdd(month, DateDiff(month, 0, @d), 0)) AS 'Tenth'
       WHERE Month(@d) IN (3, 6, 9, 12)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Faintly appalled Mr Phelan - I thought you were the International Man Of SQL.

    Try running that with your language set to proper one, like British.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, I'd forgotten that the British are not ISO Standard Compliant.
    Code:
    DECLARE @d			DATETIME
    
    SET @d = '2010-03-30T00:00:00'
    
    SELECT DateAdd(month, DateDiff(month, 0, @d), 0) AS 'First'
    ,  DateAdd(day, 9, DateAdd(month, DateDiff(month, 0, @d), 0)) AS 'Tenth'
       WHERE Month(@d) IN (3, 6, 9, 12)
    Mea culpa.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think you'll find the problem is ISO is not British compliant. We never had this sort of problem when we had an empire.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You didn't have indoor plumbing either!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Jun 2010
    Posts
    3
    ok got but let me check once.....

  12. #12
    Join Date
    Jun 2010
    Posts
    3

    hi...

    today I modified code...what i want to do is if I will change the system date from June to July then output will be null else if June then output will be 01/06/2010 - 10/06/2010 .The output will show only 'April','July','October',' January' else all time null value.


    alter proc date_validate
    (

    @company glcompany,
    @locn gllocn ,
    @emp_code varchar(6)
    )

    as
    begin

    DECLARE @month_date varchar(25)
    select @month_date = GETDATE()




    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@month_date)-1),@month_date),103) AS cur_date,
    CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(month,1,@month_date))),DATEADD(dd,10, @month_date)),103) as last_date
    WHERE month(@month_date) IN (3, 6, 9, 12)





    end

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Congratulations? I guess.
    Dave

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I merged the response thread back into the original thread to keep things consolidated and tidy. Glad to see that things worked out, but I would never have guessed that I was that close. My suggestions was a pure "shot in the dark" to try and get things moving, I never considered that it might be a near verbatim solution.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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