Is this correct? - 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

General SQL Server Forums

New to SQL Server Programming

Is this correct?

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-12 : 13:44:58

I want to get current date minus flg_cdt > 300

select cossn, count(fo) as PendinginFO
from t16pendall
where flg_cdt > dateadd(dd, -300, getdate())
group by cossn

Here's the table and some data to go inside the table.

Thanks.

[dbo].[T16pendall](
[COSSN] [char](11) NOT NULL,
[FLG_CDT] [datetime] NOT NULL,
[FO] [varchar](3) NOT NULL
) ON [PRIMARY]

insert into T16Pendall
select '1259798j733', 04/05/2006 '200' union all
select '5259478j745', 04/05/2004 '200' union all
select '7239798j787', 02/05/2004' 420' union all
select '8255798j747', 02/05/2010' 520' union all
select '9259768j765', 04/05/2002' a48' union all
select '1259748j766', 10/05/2010' a48' union all
select '1959798j771', 10/05/2010' a48' union all
select '1059798j780', 06/05/2009' 600' union all
select '1359798j713', 09/05/2009 '600' union all
select '1159798j741', 09/05/2009 '600' union all
select '1756798j765', 02/05/2010 '800' union all
select '1469798j765', 10/05/2009 '200' union all
select '1357798j765', 10/05/2008' 200'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-12 : 13:49:42

quote:

Is this correct?

Well does it work?

Do you care about the time portion of getdate() - 300?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-12 : 13:54:31

Yes it works I just want me make sure the < or > is the correct way so I can get my data.

I care about the datetime portion as I'm just getting counts. Does that matter?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-12 : 13:59:26

The time portion could matter, just depends on your requirements.

getdate() - 300 as of right now in my timezone would be 2009-12-16 17:58:30.500. So you aren't going to get a full day's worth of data for 12/16/2009.

If you want to throw out the time portion, then use this instead:
DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 300, 0)

Which produces this:
2009-12-16 00:00:00.000

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-12 : 14:16:19

Thanks I got the same results with that one as well.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-12 : 14:21:30

Be sure you understand the difference. There is no change in your results because you have no data that is between 00:00 and the current time.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-12 : 17:05:29

SQL Server supports ANSI/ISO Standard DATE data types and CURRENT_TIMESTAMP; you do not have to write 25+ year old dialect any more.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-12 : 17:14:38

Are you referring to the use of GETDATE? If so, then

.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources