Query with GETDATE() - SQL Server Forums

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)

Query with GETDATE()

Author

Topic

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-30 : 17:09:34

Hello. I would like to do a select where I get all the records with a date from the previous day. The date field in my record has the time with it. 2003-04-30 22:00, for example. How can I word my select so that it retrieves any record that does not have today's date? If I do a < GETDATE(), I could end up getting records that are from the present day. Don't want to have to pass a date value in if I can help it.
Not sure how to possible do a GETDATE and then have the time part of the value equal to 00:00. Thanks.
John

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 17:12:37

This is one way to do it -- there are others. I believe Mr Fribble has a method out there somewhere ....

select convert(datetime, convert(int, getdate()))

- Jeff

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-30 : 17:18:56

Thanks Jeff. That gives me something to look for in a search to see exactly why it should work. Appreciated.

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-04-30 : 17:25:44

You could also use

WHERE DATEDIFF(d, yourdatetime, GETDATE()) > 0

which should give you any day that isn't today.

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-30 : 17:48:03

Thanks again.

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-30 : 19:34:36

I have another question regarding datetime values. In one table my datetime has the day and time value together. When I transfer it to another table, there is really no need in having the time value there. Seems to me I read where a datetime always stores the time regardless. I am wondering if that is true. If not, then how can I lop off the time value and have just the day, month and year. Especially if that saves on space. Thank you sir(s.

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-04-30 : 21:42:57

Have a look at the CAST and CONVERT section in Books Online. It should cover those things pretty thoroughly.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 21:45:18

john -- i gave you the formula to do this already! instead of applying it to getdate(), apply it to any field or variable or whatever you want.

convert(datetime, convert(int, *ANY_DATE_TIME_VALUE*))

- Jeff

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-30 : 22:44:57

OOOps. Thanks and sorry.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-30 : 23:04:35

Arnold's method

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=21706

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-01 : 06:59:26

Keep in mind that if you have an index on the date column in your table, you will want to write your where clause like:


where
    my_date_column < dateadd(dd,datediff(dd,0,getdate()),0)

Jay White
{0}

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-05-01 : 10:41:01

To the millisecond

DECLARE @leftdate datetime
SET @leftdate = '2003-04-30 11:59:59.994'
SELECT CONVERT(int, @leftdate)
GO

-------
--37739

DECLARE @leftdate datetime
SET @leftdate = '2003-04-30 11:59:59.995'
SELECT CONVERT(int, @leftdate)

-------
--37740

Voted best SQL forum nickname...."Tutorial-D"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-01 : 11:10:47

3 milliseconds

DECLARE @leftdate datetime
SET @leftdate = '2003-04-30 11:59:59.997'
SELECT CONVERT(int, @leftdate)

-------
--37740

DECLARE @leftdate datetime
SET @leftdate = '2003-04-30 11:59:59.999'
SELECT CONVERT(int, @leftdate)

-------
--37740

quote:

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

Edited by - ValterBorges on 05/01/2003 11:12:33

Edited by - ValterBorges on 05/01/2003 11:14:29

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-01 : 13:09:49

Appreciated bigtime. I did some searches and managed to find the Convert and Cast. When you are referring to books on line, is that the site, bol.com?
Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-01 : 13:16:20

Books Online is the help tool for SQL Server. It is located on your computer if you opted to install it when you install SQL Server or just the Client Tools. Just go to start..programs..Microsoft SQL Server, you will see Books Online from there. It is the tool that you will probably use the most when using SQL Server.

Tara

Edited by - tduggan on 05/01/2003 13:17:13

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-01 : 14:46:35

Check out

www.microsoft.com/sql/techinfo/productdoc/2000/default.asp

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-05-01 : 17:10:18

Valter..

I thought I was showing the transition point
where a person would include the day or not
include the day. Important, NO?

DECLARE @leftdate datetime
SET @leftdate = '2003-04-30 11:59:59.994'
SELECT CONVERT(int, @leftdate)
SELECT convert(datetime, convert(int, @leftdate))
GO

-----------
--37739
------------------------------------------------------
--2003-04-30 00:00:00.000

DECLARE @leftdate datetime
SET @leftdate = '2003-04-30 11:59:59.995'
SELECT CONVERT(int, @leftdate)
SELECT convert(datetime, convert(int, @leftdate))

-----------
--37740
------------------------------------------------------
--2003-05-01 00:00:00.000

Voted best SQL forum nickname...."Tutorial-D"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-01 : 17:20:43

Sitka,
I understand

That's exactly why they should use Arnold's method
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=21706

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-05-02 : 06:03:45

ah ha, the Fribble circle completes itself, again

DATEADD(d, DATEDIFF(d, 0, time_stamp), 0)

Voted best SQL forum nickname...."Tutorial-D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources