Professional Web Applications Themes

Searching on Day/Month Portion of DateTime - Microsoft SQL / MS SQL Server

I have a USERS table with a Birthday column as shown below (create statement significantly trimmed down for simplicity) CREATE TABLE [USERS] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [BIRTHDAY] [datetime] NULL , CONSTRAINT [PK_USER] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] ) ON [PRIMARY] GO Birthdays are entered as a proper date for example 04/14/1976. The current stored procedure shown below allows me to search for USERS born between two dates. If both birthday_from and birthday_to are incoming as NULL, all users are returned. ALTER PROCEDURE users_search ( BIRTHDAY_FROM datetime = NULL, BIRTHDAY_TO datetime = NULL ...

  1. #1

    Default Searching on Day/Month Portion of DateTime

    I have a USERS table with a Birthday column as shown below (create statement
    significantly trimmed down for simplicity)

    CREATE TABLE [USERS] (
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [BIRTHDAY] [datetime] NULL ,
    CONSTRAINT [PK_USER] PRIMARY KEY CLUSTERED
    ( [ID] ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    Birthdays are entered as a proper date for example 04/14/1976. The current
    stored procedure shown below allows me to search for USERS born between two
    dates. If both birthday_from and birthday_to are incoming as NULL, all
    users are returned.

    ALTER PROCEDURE users_search
    (
    BIRTHDAY_FROM datetime = NULL,
    BIRTHDAY_TO datetime = NULL
    )
    AS
    SELECT U.[ID],
    FROM [USERS] U
    WHERE
    (ISNULL(U.BIRTHDAY, '') >= COALESCE(BIRTHDAY_FROM, U.BIRTHDAY, ''))
    AND
    (ISNULL(U.BIRTHDAY, '') <= COALESCE(BIRTHDAY_TO + 1, U.BIRTHDAY, ''))


    How can I modify this stored procedure so that I can find out for example,
    all users born in June. 6/1 through 6/30 regardless of the year?

    Thank You!



    George Guest

  2. #2

    Default Re: Searching on Day/Month Portion of DateTime

    Use a built in date function....

    SELECT id FROM users WHERE DATEPART(month, birthday) = 6

    --
    Keith, SQL Server MVP

    "George Durzi" <com> wrote in message news:phx.gbl... 
    Keith Guest

  3. #3

    Default Re: Searching on Day/Month Portion of DateTime

    Hi George,

    You can use some minor trickery with CONVERT, but you have to treat
    returning all users when you don't supply a date at all, because the users
    for whom the birthday is NULL won't show up if you make any comparisons.

    ALTER PROCEDURE users_search
    (
    BIRTHDAY_FROM datetime = NULL,
    BIRTHDAY_TO datetime = NULL
    )
    AS
    IF BIRTHDAY_FROM IS NULL AND BIRTHDAY_TO IS NULL
    THEN
    SELECT U.[ID],
    FROM [USERS] U
    ELSE
    SELECT U.[ID],
    FROM [USERS] U
    WHERE
    CONVERT(CHAR(5), U.BIRTHDAY, 1) >= CONVERT(CHAR(5),
    COALESCE(BIRTHDAY_FROM, '19000101'), 1)
    AND
    CONVERT(CHAR(5), U.BIRTHDAY, 1) <= CONVERT(CHAR(5),
    COALESCE(BIRTHDAY_TO, '19001231'), 1)


    "George Durzi" <com> wrote in message
    news:phx.gbl... 
    statement 
    two 


    Jacco Guest

Similar Threads

  1. Replies: 3
    Last Post: September 21st, 10:22 AM
  2. datetime class: setting day, month...
    By Ante Perkovic in forum ASP.NET General
    Replies: 6
    Last Post: July 2nd, 11:34 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139