Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Location
    Amersfoort, The Netherlands
    Posts
    2

    Thumbs up Unanswered: Introduction & Question

    At the top of this page it says "Why not make your first post today by saying hello to our community in our Introductions forum." I tried searching for this forum but I was not able to find it. Either because I was not looking good enough, or because it was not there. So I decided to make this my introduction post as well as my question. (Sorry admins, mods if I did you wrong).
    So here it goes,

    I am Qwanta (a.k.a. Taco) from Amersfoort, the Netherlands. I work at a company that builds various products out of metal. In this company we run an ERP Package called Isah7. For this I need to figure out how to make Stored Procedures as well as the rest anyone needs to know to manage the program as efficiently as possible. We have a lot of problems I need to solve, for example a lot of our Reports run over a mess of tables and I want them to run efficiently thus use Stored Procedures. This is just one problem of many.

    In my spare time I Compose/Program Music. It is very relaxed to be able to make beats, breaks and great atmosphere and listen to what you have done afterwards. Silently I hope to be able to work with music as a profession, but this is something for a later life stadium. Besides music I am studying the Latin language as a way to not sit behind a computer and learn a bit of history. Also I want to learn Latin because i want to be able to Speak Spanish, Italian, French and Portuguese. For these languages Latin is a great basis. Besides this I want to learn German and a modern language like Chinese or Japanese before I die in many years.

    And now my question as I posted on vbtalk:

    I'm trying to find a way to calculate in Sybase SQL whether someone’s birthday is in the future or if it has past in the current year. But if it has passed it should return 0 and if it hasn't passed it should return 1. This all should be added in a new column beside the columns already selected. I have been searching google and this website, but I can’t find anything similar. The problem is not that I don’t know about DateDiff/DatePart/DateAdd/Etc but the problem is but the fact I want to have a conditional column with 1's and 0's. Case/Cast functions are not available so they have no value.
    Last edited by Qwanta; 04-11-06 at 08:09.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

    conditional column with 1's and 0's

    Welcome to the forum.
    Quote Originally Posted by Qwanta
    Case/Cast functions are not available so they have no value.
    Must be old version of Sybase then? Use charindex:
    Code:
    select charindex('-',convert(varchar(9),datediff(dd,'20060611','20060101')))
          ,charindex('-',convert(varchar(9),datediff(dd,'20060611','20070101')))
          ,charindex('-',convert(varchar(9),datediff(dd,'20060101','20060611')))
          ,charindex('-',convert(varchar(9),datediff(dd,'20070101','20060611')))
    
                                                    
    ----------- ----------- ----------- ----------- 
              1           0           0           1

  3. #3
    Join Date
    Apr 2006
    Location
    Amersfoort, The Netherlands
    Posts
    2

    Thumbs up

    Quote Originally Posted by pdreyer
    Welcome to the forum.

    Must be old version of Sybase then? Use charindex:
    Code:
    select charindex('-',convert(varchar(9),datediff(dd,'20060611','20060101')))
    Thanks for your answer pdreyer,

    Actually i just found out we are using ASE 12. I dont understand why the case function does not work, maybe my usage isn't proper.

    Besides my ignorance, i'm wondering if you could maybe explain your way of piping. I would like to understand how you think while you make this up. It works partially or at least the closest to where I got to by myself.

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    I've been toying around with a stored procedure I wrote:
    Code:
    create procedure sp_checkbirthday 
    	(@birthday datetime, @value int OUTPUT) 
    as 
    begin 
     
    -- compare month 
    if ( (datepart(mm, @birthday)) < (datepart(mm, getdate())) ) 
    begin 
    --birthday is history, return 0 
    	select @value = 0 
    	return 
    end 
     
    else if ( (datepart(mm, @birthday)) = (datepart(mm, getdate())) ) 
    -- birthdaymonth is current month, check day 
    begin 
    	if ( (datepart(dd, @birthday)) < (datepart(dd, getdate())) ) 
    	begin 
    	--birthday is history, return = 
    		select @value = 0 
    		return 
    	end 
    	else 
    	begin 
    	-- birthday is today or later this month, return 1 
    		select @value = 1 
    		return 
    	end 
    end 
    else 
    begin 
    --birthday is in future month, return 1 
    	select @value = 1 
    	return 
    end 
    return @value 
    end
    It doesn't work entirely, I can't get the returnvalue to display correctly. The whole if-else-construction should work though.
    Maybe it comes in handy, or maybe you can fix the flaws.
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    I used your proc (with one very small modification: I removed the return @value at the end) like this:

    [35] DBA_SQL.testdb.1> declare @value int
    [35] DBA_SQL.testdb.2> exec sp_checkbirthday 'jul 20 2006', @value output
    [36] DBA_SQL.testdb.1> go
    (return status = 0)


    -----------
    1

    (1 row affected)

    So it works just fine...

    Michael

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    The datediff will result in a positive or negative number.
    charindex will find the occurrence of the negative sign '-'
    which will be in position 1 if found or 0 if not found

    Here is an example using both case and charindex
    Code:
    create table #t1 (name varchar(10), birthdate datetime)
    insert into #t1 
    select 'dab','19010101' union all
    select 'dib','19111111' union all
    select 'dob','20001231'
    
    select name, birthdate
    , 'ck1'=case  
      when convert(varchar(4),datepart(yy,getdate()))
           +substring(convert(varchar(8),birthdate,112),5,4)<getdate() 
        then 0
        else 1
      end
    , 'ck2'=charindex('-',convert(varchar(9),datediff(dd,
            convert(varchar(4),datepart(yy,getdate()))
           +substring(convert(varchar(8),birthdate,112),5,4),getdate())))
    from #t1
    drop table #t1
    
    name       birthdate                      ck1         ck2         
    ----       ---------                      ----------- ----------- 
    dab        Jan  1 1901 12:00AM                      0           0 
    dib        Nov 11 1911 12:00AM                      1           1 
    dob        Dec 31 2000 12:00AM                      1           1

  7. #7
    Join Date
    Apr 2006
    Posts
    1
    great to be here

  8. #8
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by mpeppler
    I used your proc (with one very small modification: I removed the return @value at the end) like this:

    [35] DBA_SQL.testdb.1> declare @value int
    [35] DBA_SQL.testdb.2> exec sp_checkbirthday 'jul 20 2006', @value output
    [36] DBA_SQL.testdb.1> go
    (return status = 0)


    -----------
    1

    (1 row affected)

    So it works just fine...

    Michael
    I'm glad it works for you.
    I'm not crazy, I'm an aeroplane!

Posting Permissions

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