Dates

trying to create a stored proc with this query:
select * from table1 where datefield BETWEEN @.startdate
AND @.enddate.
When using Crystal reports against this,it works fine
except when you are just using one day- for example your
start date and end date would both be 9/2/2004. Anyone run
into this? Should I do something different? THANKS!probably you are not considering time component. See following example.
create table t(dt datetime)
insert into t values ('20040903 12:24:15')
insert into t values ('20040903 1:24:15')
insert into t values ('20040903 16:24:15')
insert into t values ('20040903')
insert into t values ('20040904')
select * from t
where dt between '20040903' and '20040903 23:59:59'
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"Ann" <anonymous@.discussions.microsoft.com> wrote in message
news:51bb01c49134$9db6b420$a501280a@.phx.gbl...
> trying to create a stored proc with this query:
> select * from table1 where datefield BETWEEN @.startdate
> AND @.enddate.
> When using Crystal reports against this,it works fine
> except when you are just using one day- for example your
> start date and end date would both be 9/2/2004. Anyone run
> into this? Should I do something different? THANKS!

DatePart problem

Can anyone explain me why this query

SELECT DATEPART(dw, CONVERT(DATETIME, '2001-01-25 01:00:00', 120))

returns 5 on one SQLServer, and 4 on another one. It cannot be a regional settings problem because 25 could never be a month. Any way, is there a deterministic way to create a DATE regardless of the server's regional settings?

I mean, I have the year, month and day... how do I tell the server "This is the year; This is the month; This is the day... convert this into a DATE, and the tell me which day of the week is"??

TIA,I think I've figured it out... I'll post again if I need further help.|||And the solution was? *curious*|||I'll bet on SET DATFIRST (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_4qic.asp).

-PatP|||I'll bet on SET DATFIRST (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_4qic.asp).

-PatPGood bet! You were right. That was the solution I had found out.

Datename gives incorrect result

Hi!
I tried to run this query:
select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
The result is:
34 33
It's worng result, why?
Right answer is 33 in both datenamn item.
I have SQL Server 2000
Best regards
Bertil MorefltSQL Server doesn't calculate ws according to the ISO standard. I.e., don'
t use datepart or
datename for w number calculation. Search Books Online for ISOW and us
e that one instead. Or
use a calendar table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:%23rUlP0%23pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moreflt|||If you are looking for ISOWEEK, you can find one at the CREATE Function
example in BOL
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:%23rUlP0%23pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moreflt|||Here you will find a function to calculate the iso w.
http://msdn.microsoft.com/library/d...r />
_7r1l.asp
AMB
"Bertil Morefalt" wrote:

> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moref?lt
>|||You can use a calendar table for this, or the ISOWEEK() function in Books
Online, or the one listed here:
http://www.aspfaq.com/2519
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:%23rUlP0%23pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moreflt

DateAdd function

Hi guys,
In Access u can do something like the following in ur query:

SELECT...
WHERE (weekendingEmp.WeekEnding)>(DateAdd("ww",-3,Now()))

Now() function will get the current system date.
1st parameter which is "ww" means that it will add number of weeks to the current system date according to number specified by the 2nd parameter..
so basically, this query will use DateAdd function as part of WHERE statement and collect data from 3 weeks ago up to today...

unfortunately, i can't use Access object anymore and i need to use ADO.. i believe regular SQL statement doesn't understand "DateAdd" function so my question is.. does anyone kno how i can come around this problem w/out using DateAdd function?

thanksIt depends on the DB you are using. If you are using SQL Server, you can do the exact same thing with:

SELECT...
WHERE (weekendingEmp.WeekEnding)>(DATEADD(ww,-3,GETDATE()))