datepart + dateadd

[JEM]

I am trying to select children from a table whose birthday falls within
21 and 28 days from today but am not having much success. Below is the
sql statement. Does anyone know what i'm doing wrong? Thanks.

SELECT dbo.tblChild.FName, dbo.tblChild.LName,
dbo.tblChild.Birthday
FROM dbo.tblChild
WHERE (CAST(DATEPART(month, dbo.tblChild.Birthday) + DATEPART(day,
dbo.tblChild.Birthday) + DATEPART(year, GETDATE()) AS datetime) BETWEEN

DATEADD(d, 21, GETDATE()) AND DATEADD(d, 28,
GETDATE()))

 

[Sylvain Lafontaine]

You should convert the month, day and year to strings and concatenate them
into a proper string representation of the date using the separator /
between the month, day and year before casting the final result into a date.

Don't forget that this require that the current date format is MDY. If you
want to distribute your application, using the Convert() function with a
proper format id might be a better idea.

 

[JEM]

Thanks. I'm still not doing something right. Here is what i have:

SELECT dbo.tblChild.FName, dbo.tblChild.LName,
dbo.tblChild.Birthday
FROM dbo.tblChild
WHERE
(CAST(CONVERT(nchar(2), DATEPART(month, dbo.tblChild.Birthday), 0) +
N'/' +
CONVERT(nchar(2), DATEPART(day, dbo.tblChild.Birthday), 0) + N'/' +
CONVERT(nchar(4), DATEPART(year, GETDATE()), 0) AS datetime)
BETWEEN DATEADD(d, 21, GETDATE()) AND DATEADD(d, 28, GETDATE()))

Any ideas? I keep getting an error message that says something about
arithmetic overflow.

 

[Sylvain Lafontaine]

Looks good to me, I don't see the error.

Maybe replacing nchar(2) with nvarchar(2) and removing the trailing « , 0 »
at the end of the convert functions.

Whats happens if you remove the Between clause and move the cast function
from the Where to the Select statement?

 

[Sylvain Lafontaine]

Also, is the format for DateTime MDY or if it is something else like DMY in
your case?

 

[JEM]

Thanks for the advice, I tried your suggestions but i am still getting
the error message. Also, yes, it is MDY format.

 

[giorgio rancati]

Hi JEM,

Are you sure ?
anyway, you can use the iso format, it's indipendent from server
lcoalization.
----
SELECT FName, LName, Birthday
FROM dbo.tblChild
WHERE REPLACE(STR(Year(GETDATE()),4)+
STR(MONTH(Birthday),2)+
STR(DAY(Birthday),2)
,' ','0')
BETWEEN DATEADD(d, 21, GETDATE()) AND DATEADD(d, 28, GETDATE())