Database query
-
I have table (in sql server database) which contains two fields ,Name(varchar) and Date_of_Birth(Datetime) I want to fetch those records whose birth day is in this week (incuding today(system date)). Please help me with the query
SELECT t.Name FROM MyTable AS t WHERE DATEPART(week,t.Date_of_Birth)=DATEPART(week,GETDATE())Alexandre Kojevnikov MCP (SQL2K, Win/C#) Leuven, Belgium -
SELECT t.Name FROM MyTable AS t WHERE DATEPART(week,t.Date_of_Birth)=DATEPART(week,GETDATE())Alexandre Kojevnikov MCP (SQL2K, Win/C#) Leuven, Belgiumthats nice ,but i want only those date of birth which is between today and 7 days after today(not before). for example today date is 4/1/2003 i want to fetch those date of birth which is today and 7 days from today i.e the records should be like below() 4/1/2003 4/2/2003 4/3/2003 4/4/2003 4/5/2003 .........
-
thats nice ,but i want only those date of birth which is between today and 7 days after today(not before). for example today date is 4/1/2003 i want to fetch those date of birth which is today and 7 days from today i.e the records should be like below() 4/1/2003 4/2/2003 4/3/2003 4/4/2003 4/5/2003 .........
pradipta wrote: but i want only those date of birth which is between today and 7 days after today(not before).
SELECT t.Name FROM MyTable AS t WHERE t.Date_of_Birth between GETDATE() AND DATEADD(d, 7, GETDATE())You might need to manipulate this a bit. It will return exactly 7 days (7 days * 24 hours). The time element may throw the query off. For example, if the current system date/time is 04/02/2002 13:00:00 then a birthday with the following will not appear: 04/02/2002 1:00:00 and 04/02/2002 00:00:00. Jeremy Oldham -
thats nice ,but i want only those date of birth which is between today and 7 days after today(not before). for example today date is 4/1/2003 i want to fetch those date of birth which is today and 7 days from today i.e the records should be like below() 4/1/2003 4/2/2003 4/3/2003 4/4/2003 4/5/2003 .........
DECLARE @now AS datetime SET @now = GETDATE() DECLARE @today AS datetime SET @today = CAST(CONVERT(char(8), @now, 112) AS datetime) SELECT t.Name FROM MyTable AS t WHERE DATEDIFF(Day,@today,t.Date_of_Birth) BETWEEN 0 AND 6112 is ISO format: yyyyMMdd. SQL Server handles conversion from this format to datetime no matter which DATEFORMAT is active. Alexandre Kojevnikov MCP (SQL2K, Win/C#) Leuven, Belgium -
pradipta wrote: but i want only those date of birth which is between today and 7 days after today(not before).
SELECT t.Name FROM MyTable AS t WHERE t.Date_of_Birth between GETDATE() AND DATEADD(d, 7, GETDATE())You might need to manipulate this a bit. It will return exactly 7 days (7 days * 24 hours). The time element may throw the query off. For example, if the current system date/time is 04/02/2002 13:00:00 then a birthday with the following will not appear: 04/02/2002 1:00:00 and 04/02/2002 00:00:00. Jeremy Oldhami think U did not understand the problem.the query u have written only to retrive those DOB which will lie within this week.But DOB means only the month and day will be same whatever may be the year ,for exammple today is 4/4/2003 and DOB of a member 4/4/1975 then today is the birth day of that memeber. so i want only the dob from database whose day and month will be equal to today's day and month and also those whose b'day will be within 7 days beyond today just see the example dob in the data base DOB(mm/dd/yyyy) 4/4/1976 4/4/1970 4/6/1976 4/8/1999 1/1/1990 1/3/2000 ------------ Today is -4/4/2003 i want the the data from database 4/4/1976 4/4/1970 4/6/1976 4/8/1999 because the above dobs will lie on this week pradipta
-
i think U did not understand the problem.the query u have written only to retrive those DOB which will lie within this week.But DOB means only the month and day will be same whatever may be the year ,for exammple today is 4/4/2003 and DOB of a member 4/4/1975 then today is the birth day of that memeber. so i want only the dob from database whose day and month will be equal to today's day and month and also those whose b'day will be within 7 days beyond today just see the example dob in the data base DOB(mm/dd/yyyy) 4/4/1976 4/4/1970 4/6/1976 4/8/1999 1/1/1990 1/3/2000 ------------ Today is -4/4/2003 i want the the data from database 4/4/1976 4/4/1970 4/6/1976 4/8/1999 because the above dobs will lie on this week pradipta
pradipta wrote: the query u have written only to retrive those DOB which will lie within this week.But DOB means only the month and day will be same whatever may be the year... Sorry about that. Who knows where my mind has be drifting. :-O Try the following:
SELECT t.myName, cast(cast(Month(getdate()) as varchar(2)) + '/' + cast(Day(getdate())as varchar(2)) + '/' + cast(Year(getdate())as varchar(4)) as datetime), cast(cast(Month(t.DOB)as varchar(2)) + '/' + cast(Day(t.DOB)as varchar(2)) + '/' + cast(Year(getdate())as varchar(4)) as datetime) FROM DOB AS t WHERE datediff(d, cast(cast(Month(getdate()) as varchar(2)) + '/' + cast(Day(getdate())as varchar(2)) + '/' + cast(Year(getdate())as varchar(4)) as datetime), cast(cast(Month(t.DOB)as varchar(2)) + '/' + cast(Day(t.DOB)as varchar(2)) + '/' + cast(Year(getdate())as varchar(4)) as datetime)) Between 0 and 6Let me know for sure that this works properly. Jeremy Oldham