SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Dates is this cheating? @inDate + '23:00:00'
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 02/20/2005 :  21:12:45  Show Profile  Reply with Quote
Hey all-
I need to pass the start date and end date to a SP. I need to be able to query for a single day or for many days.

SINGLE DATE
@inSTARTDATE = '2/20/2005'
@inENDDATE = '2/20/2005'

MANY DATES
@inSTARTDATE = '1/1/2004'
@inENDDATE = '2/20/2005'

The value is being stored as datetime and is using getdate() for the default value.

Whats the best way to do this? I guess I could used enddate + 1 or something? I hate dates, they always give me problems. I need to take the time and figure them out instead of all this hacking I keep doing..sorry for the little rant. 45 hours in 3 days is taking its toll, nice weekend huh?

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/20/2005 :  21:17:27  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>>I guess I could used enddate + 1 or something?

you are on the right track. if you are storing times with your dates, don't use:

WHERE SomeDate BETWEEN @StartDate AND @EndDate

rather, use:

WHERE SomeDate >= @StartDate and SomeDate < @EndDate+1



- Jeff
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 02/21/2005 :  05:44:53  Show Profile  Reply with Quote
I'm getting confused about date comparisons.

I've just been looking on here for some guidance but it has made me worse.

I was under the impression that using BETWEEN for dates was NOT a good practice as Jeff suggested(presumably because of the date element) but when I did a search on this site I couldn't find any reference to it and all I could find were lots of examples that use BETWEEN.

Does anyone have any sort of evidence on the best way to deal with this sort of thing, is a comparison operator the 'best' method, should we be using various DATE functions (DATEDIFF etc.) or is BETWEEN the best option in some circumstances.

I get a feeling that someone will say that it depends - but how can I be certain what it depends on?

thanks

steve



And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 02/21/2005 :  06:36:06  Show Profile  Visit spirit1's Homepage  Reply with Quote
well you could do:
1. where datediff(d, DateCol, getdate()) = 5 -- no index use
2. where DateCol between getdate() and getdate() + 5 -- use of index

in general date functions should be performed on variables and getdate,
not on columns as that renders indexes on that column useless.


Go with the flow & have fun! Else fight the flow
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 02/21/2005 :  10:40:52  Show Profile  Reply with Quote
I get good performance (and accurate results) using this technique:

--set up dates to be dateTime datatype and include full days
declare @sDate datetime
	,@eDate datetime

Select	@sDate = convert(datetime,@inStartDate)  --time = 12:00 AM
	,@eDate = convert(datetime, dateAdd(second,-1,dateAdd(day,1,@inEndDate))) --time = 11:59:50 PM

--where clause
Where	MyDatetimeColumn between @sDate AND @eDate


Be One with the Optimizer
TG
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 02/21/2005 :  10:54:11  Show Profile  Visit spirit1's Homepage  Reply with Quote
and how do you get the data?
because "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

Go with the flow & have fun! Else fight the flow
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/21/2005 :  12:44:45  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>>and how do you get the data?
>>because "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

It's two different SELECT statements. one to set the variables, the other to return the data.

- Jeff
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 02/21/2005 :  12:51:03  Show Profile  Visit spirit1's Homepage  Reply with Quote
<banging head on the wall>
the bottom where goes to the other statement... DOH!!!
</banging head on the wall>


Go with the flow & have fun! Else fight the flow
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/21/2005 :  16:49:28  Show Profile  Reply with Quote
What happens to a datetime values between 11:59:50 PM and midnight? This code should get all of those.

Select	@sDate = convert(datetime,@inStartDate)  --time = 12:00 AM
Select	@eDate = dateadd(dd,1,@sDate) -- next day at 12:00 AM

--where clause
Where	MyDatetimeColumn >= @sDate AND MyDatetimeColumn < @eDate



quote:
Originally posted by TG

I get good performance (and accurate results) using this technique:

--set up dates to be dateTime datatype and include full days
declare @sDate datetime
	,@eDate datetime

Select	@sDate = convert(datetime,@inStartDate)  --time = 12:00 AM
	,@eDate = convert(datetime, dateAdd(second,-1,dateAdd(day,1,@inEndDate))) --time = 11:59:50 PM

--where clause
Where	MyDatetimeColumn between @sDate AND @eDate


Be One with the Optimizer
TG



Codo Ergo Sum
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 02/21/2005 :  18:50:28  Show Profile  Reply with Quote
Chuck this is in your model database..


CREATE  function dbo.StartOfDay
(
@Date DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(d,DATEDIFF(d,0,@Date),0)
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
CREATE function dbo.EndOfDay
(
@Date DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(ms,-2,DATEADD(d,1,dbo.StartOfDay(@Date)))
END
GO
Select dbo.StartOfDay(getdate()), dbo.EndOfDay(getdate())
GO

Notice the 2 millisecond subtraction to overcome SQL's time resolution limit

Edit: Is that fixed in Yukon?

DavidM

A front-end is something that tries to violate a back-end.

Edited by - byrmol on 02/21/2005 18:56:57
Go to Top of Page

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 02/22/2005 :  11:02:29  Show Profile  Reply with Quote
quote:

A front-end is something that tries to violate a back-end.



HA HA HA! I love that sig line! The truth is just funny sometimes.
Go to Top of Page

manoj_k_sri@rediffmail.co
Starting Member

India
1 Posts

Posted - 11/23/2006 :  23:58:27  Show Profile  Reply with Quote



declare @sql varchar(8000)
select @sql = 'bcp "select P.MedicalRecordNo,p.FirstName,p.MiddleName,p.LastName,p.Address1,p.Address2,p.City,t.name State,p.ZIPCode,p.Country,p.Telephone1,p.DOB,p.SSN,p.Sex,p.Matiral_status,(m.Description + ',' + m.name)as NephrologistName,l.UPIN from AccessManager..tblPatients p left outer JOIN AccessManager..tblMasters m on p.Nephrologist_Id=m.serialno and masterType=1 left outer join AccessManager..tblMasters t on p.state=t.serialno and t.Mastertype=3 left outer join AccessManager..tblMastersAddtnl l on t.serialno=l.UPIN" queryout format H:\Result10.csv -c -t, -U sa -S'+ 'Manoj'
exec master..xp_cmdshell @sql




I used (m.Description + ',' + m.name)as NephrologistName concate function our BCP command to get record in CSV file. it will work if i use it with out bcp command

but I it showing error with this message.


Server: Msg 141, Level 15, State 1, Line 4
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.


I am in big Probleam .Please any one help me as soon as possible








A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.


manoj srivastava
Go to Top of Page

shijobaby
Starting Member

India
44 Posts

Posted - 08/21/2009 :  09:30:53  Show Profile  Visit shijobaby's Homepage  Reply with Quote
Hi

Actually this in the group of small errors consuming time

Just have aook on my blog

http://sqlerrormessages.blogspot.com/2009/08/msg-141-select-statement-that-assigns.html


Happy Programming
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 08/21/2009 :  09:38:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by shijobaby

Hi

Actually this in the group of small errors consuming time

Just have aook on my blog

http://sqlerrormessages.blogspot.com/2009/08/msg-141-select-statement-that-assigns.html


Happy Programming


What a clever way to promote your blog

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000