#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    South Asia
    Posts
    14
    Rep Power
    0

    date time format function required


    hi,

    I have a celebrity table with there date of birth field. i just want to write a query which return me the celebs who's DOB is in between next 14 days.

    i have wrote this query in MySQL 4.0. then i shifted my DB from MySQL to SQL server 2000.
    the query in MySQL is


    SELECT name,dob
    FROM celeb
    WHERE date_format(dob,'%c-%e') between date_format(current_date(),'%c-%e') and date_format(DATE_ADD(current_date(), INTERVAL 7 DAY),'%c-%e')

    dateformat function formats the date in month and day

    you can help me by just replacing this function (date_format) with any appropriate function. or u can also suggest me any alternate query with same result

    thankx in advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Cincinnati, OH USA
    Posts
    111
    Rep Power
    12
    This is the best I could come up with. Not sure if it is close or not...

    SELECT name,dob
    FROM celeb
    WHERE CAST(CAST(MONTH(dob) AS Varchar(2)) + '/' + CAST(DAY(dob) AS Varchar(2)) + '/' + CAST(YEAR(GETDATE()) AS Varchar(4)) AS Datetime) between GETDATE() and DATE_ADD(d, 7, GETDATE())

    This is assuming US date formats.

    Hope that helps...

    Jill

IMN logo majestic logo threadwatch logo seochat tools logo