|
Home > Archive > MS Access project with SQL Server > April 2006 > datepart + dateadd
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
datepart + dateadd
|
|
|
| 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 2006-03-31, 1:32 pm |
| 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.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"JEM" <Jenn@JEMConsulting.org> wrote in message
news:1143825642.734760.280090@u72g2000cwu.googlegroups.com...
>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()))
>
| |
|
| 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 2006-03-31, 8:28 pm |
| 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, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"JEM" <Jenn@JEMConsulting.org> wrote in message
news:1143841863.179260.93530@z34g2000cwc.googlegroups.com...
> 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 2006-04-01, 3:26 am |
| Also, is the format for DateTime MDY or if it is something else like DMY in
your case?
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:eJ84NeSVGHA.4452@TK2MSFTNGP12.phx.gbl...
> 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, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "JEM" <Jenn@JEMConsulting.org> wrote in message
> news:1143841863.179260.93530@z34g2000cwc.googlegroups.com...
>
>
| |
|
| Thanks for the advice, I tried your suggestions but i am still getting
the error message. Also, yes, it is MDY format.
| |
| giorgio rancati 2006-04-03, 1:33 pm |
| 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(GET
DATE()),4)+
STR(MONTH(Birthday),
2)+
STR(DAY(Birthday),2)
,' ','0')
BETWEEN DATEADD(d, 21, GETDATE()) AND DATEADD(d, 28, GETDATE())
----
bye
--
Giorgio Rancati
[Office Access MVP]
"JEM" <Jenn@JEMConsulting.org> ha scritto nel messaggio
news:1143994101.689631.301270@u72g2000cwu.googlegroups.com...
> Thanks for the advice, I tried your suggestions but i am still getting
> the error message. Also, yes, it is MDY format.
>
|
|
|
|
|