Post reply

find a the date of birthday fix my code

  • July 23, 2003 at 12:46 pm

    #159516

    help get work BirthDay today !!

    BirthDay this week work ok

    -----------------

    DROP VIEW UserBirthDay GO CREATE VIEW UserBirthDay AS SELECT [id],FirstName,LastName,Date_born, DATEDIFF(yyyy,Date_born,GETDATE()) AS Age, (CASE WHEN DATEADD(yyyy,YEAR(GETDATE())-YEAR(Date_born),Date_born) = GETDATE() THEN 1 ELSE 0 END) AS BirthdayToday, (CASE WHEN DATEADD(yyyy,YEAR(GETDATE())-YEAR(Date_born),Date_born) BETWEEN (GETDATE() - DATEPART(WEEKDAY,GETDATE()) + 1) AND (GETDATE() - DATEPART(WEEKDAY,GETDATE()) + 1 + 6) THEN 1 ELSE 0 END) AS BirthdayThisWeek FROM dbo.users GO

    ---------------------------

    thnks ilan

  • July 23, 2003 at 2:22 pm

    #466652

    This does what you wanted; the select at the end shows how you can find the # days before /number of days until the users next birthday:

    CREATE VIEW UserBirthDay

    AS SELECT

    FirstName,

    LastName,

    Date_born,

    DATEDIFF(yyyy,Date_born,GETDATE()) AS Age,

    (CASE WHEN DATEDIFF(dd,(DATEADD(yyyy,YEAR(GETDATE())-YEAR(Date_born),Date_born)),GETDATE()) =0 THEN 1 ELSE 0 END) AS BirthdayToday,

    (CASE WHEN DATEADD(yyyy,YEAR(GETDATE())-YEAR(Date_born),Date_born) BETWEEN (GETDATE() - DATEPART(WEEKDAY,GETDATE()) + 1) AND (GETDATE() - DATEPART(WEEKDAY,GETDATE()) + 1 + 6) THEN 1 ELSE 0 END) AS BirthdayThisWeek

    FROM dbo.users

    select DATEDIFF(dd,(DATEADD(yyyy,YEAR(GETDATE())-YEAR(Date_born),Date_born)),GETDATE()) AS NumDays from users

    the positive values are days before birthday, and the negative values are days until the next birthday; a zero value means birthday=today

    Lowell

    Edited by - lowell on 07/23/2003 2:27:59 PM

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 23, 2003 at 3:01 pm

    #466655

    thnks a lot for your help

    it work 100%

    ilan

Viewing 3 posts - 1 through 2 (of 2 total)

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