December 18th, 2003, 04:36 AM
date time format function required
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
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
December 18th, 2003, 10:19 AM
This is the best I could come up with. Not sure if it is close or not...
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...