drop minutes from timestamp search

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

SQL Server Development (2000)

drop minutes from timestamp search

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2005-04-11 : 17:44:24

Hi all, I have a stored procedure that I want to run a WHERE clause by a 'MM/dd/yyyy' date format. I want to run this for my TimeStamp field, that is a dateTime. How can I drop the time values in my timestamp so I can just match it with the date? (Do i have to drop it somehow in the SELECT statment?) I tried to search for this, but there seems to be an error with the search engine right now?

Thanks!

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-04-11 : 17:57:13

Use Convert function for your datetime field, to strip the time part of your datetime (assuming it is a datetime column and not sql server timestamp(row version which is binary)

search in the SQL server books online or in this forum..for Convert

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-11 : 18:53:10

It is better to select for a date range, because you do not have to convert each individual TimeStamp date and if you have an index on the TimeStamp column it can do an index lookup.

declare @From_date	datetime
declare @To_date	datetime

select	@From_date	= '2005/3/31 00:00:00'
select	@To_date	= dateadd(dd,1,@From_date)

select
	*
from
	MyTable
where
	-- Note that you cannot use a between,
	-- because the second criteria is less than
	MyTimeStamp >= @From_date	and
	MyTimeStamp <  @To_date

CODO ERGO SUM

hueby
Posting Yak Master

127 Posts

Posted - 2005-04-12 : 12:14:28

Thanks for the replies.. I'll have to play with that date range method. For now I am playing with the CONVERT suggestion.

I have this in my SP:


SELECT   CONVERT(char(10), Hours.TimeStamp1, 101), dbo.PurchaseOrder.Purchord, dbo.PurchaseOrder.JobNo, dbo.PurchaseOrder.Descr, dbo.contact.cid
FROM         dbo.PurchaseOrder INNER JOIN
                      dbo.Contact ON dbo.PurchaseOrder.cid = dbo.Contact.cid INNER JOIN
                      dbo.Hours ON dbo.PurchaseOrder.JobID = dbo.Hours.JobId
WHERE Hours.TimeStamp1 = '04/11/2005'

But it comes up with nothing still, I'm guessing because I can't use the CONVERTED timestamp1 in my WHERE clause? (or when I strip the TIME, it is not carrying to the WHERE clause?)

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-04-12 : 13:25:30

Try this..
SELECT CONVERT(char(10), Hours.TimeStamp1, 101), dbo.PurchaseOrder.Purchord, dbo.PurchaseOrder.JobNo, dbo.PurchaseOrder.Descr, dbo.contact.cid
FROM dbo.PurchaseOrder INNER JOIN
dbo.Contact ON dbo.PurchaseOrder.cid = dbo.Contact.cid INNER JOIN
dbo.Hours ON dbo.PurchaseOrder.JobID = dbo.Hours.JobId
WHERE CONVERT(varchar(10),Hours.TimeStamp1,101) = '04/11/2005'

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-12 : 13:29:06

[code]
SET NOCOUNT ON
DECLARE @x varchar(10)
SELECT @x = CONVERT(varchar(10),GetDate(),101)
SELECT @x

DECLARE @y table(x datetime)

INSERT INTO @y(x) SELECT GetDate()
INSERT INTO @y(x) SELECT GetDate()
INSERT INTO @y(x) SELECT GetDate()
INSERT INTO @y(x) SELECT GetDate()
INSERT INTO @y(x) SELECT GetDate()

SELECT * FROM @y
WHERE DATEDIFF(d,x,@x)=0
SET NOCOUNT OFF

[/code]

Brett

8-)

hueby
Posting Yak Master

127 Posts

Posted - 2005-04-12 : 14:25:37

Thanks cshah1! I have it working now

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-12 : 14:34:53

My bad...you actually want the last sql statement in this group...do a showplan to see the difference


SET NOCOUNT ON
DECLARE @x varchar(10)
SELECT @x = CONVERT(varchar(10),GetDate(),101)
SELECT @x, DATEADD(s,-1,@x),DATEADD(d,1,@x)

CREATE TABLE y(x datetime)
CREATE INDEX IX1 ON y(x)

INSERT INTO y(x) SELECT GetDate()
INSERT INTO y(x) SELECT GetDate()
INSERT INTO y(x) SELECT GetDate()
INSERT INTO y(x) SELECT GetDate()
INSERT INTO y(x) SELECT GetDate()

SELECT * FROM y 
WHERE DATEDIFF(d,x,@x)=0

SELECT * FROM y 
WHERE CONVERT(varchar(10),x,101) = @x

SELECT * FROM y 
WHERE x > DATEADD(s,-1,@x)
  AND x < DATEADD(d,1,@x)

SET NOCOUNT OFF
DROP TABLE y

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources