Dates is this cheating? @inDate + '23:00:00'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dates is this cheating? @inDate + '23:00:00'

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2005-02-20 : 21:12:45

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

7423 Posts

Posted - 2005-02-20 : 21:17:27

>>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

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-02-21 : 05:44:53

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.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 06:36:06

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-21 : 10:40:52

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 10:54:11

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-21 : 12:44:45

>>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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 12:51:03

<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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-02-21 : 16:49:28

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

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-02-21 : 18:50:28

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.

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2005-02-22 : 11:02:29

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.

manoj_k_sri@rediffmail.co
Starting Member

1 Post

Posted - 2006-11-23 : 23:58:27

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

shijobaby
Starting Member

44 Posts

Posted - 2009-08-21 : 09:30:53

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-21 : 09:38:00

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources