Create a View to Just Get Today's Data

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

Create a View to Just Get Today's Data

Author

Topic

iahii_2005
Starting Member

4 Posts

Posted - 2008-09-02 : 10:55:55

Hi,
Guys i am really new to SQL Server.Here is my problem i am trying to create a view to get just today's data from a table that has a 'tEndtime' Column that has the value in this format for example:7/29/2008 5:31:21 PM ; Is there a way to just extract just the date from the 'tEndtime' Column and to use a where clause to just dispaly today's data...whatever today is.Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 10:58:52

Assuming tEndTime is DATETIME or SMALLDATETIME, try this

SELECT	*
FROM	Table1
WHERE	tEndtime >= DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')
	AND tEndtime < DATEADD(DAY, DATEDIFF(DAY, '18991231', GETDATE()), '19000101')

E 12°55'05.63"
N 56°04'39.26"

iahii_2005
Starting Member

4 Posts

Posted - 2008-09-02 : 11:10:01

Wow! that works...can you please briefly explain how this functions?...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-02 : 11:12:57

See what this returns

SELECT
tEndtime = DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101'),
tEndtime = DATEADD(DAY, DATEDIFF(DAY, '18991231', GETDATE()), '19000101')

Madhivanan

Failing to plan is Planning to fail

iahii_2005
Starting Member

4 Posts

Posted - 2008-09-05 : 10:19:51

Hi,
It relation to above post how would i just display the data from 07:00AM to 23:30PM..so basically further filtering?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-05 : 10:27:36

Same thing.

SELECT	*
FROM	Table1
WHERE	tEndtime >= DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101 07:00')
	AND tEndtime < DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101 23:30')

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources