SELECT data for today's date

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » SELECT data for today's date

SELECT data for today's date

Rate Topic

Display Mode

Topic Options

Author

Message

A Little Help Please

Posted Tuesday, December 07, 2010 3:12 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:57 AM
Points: 322, Visits: 718

I have a table which has a column called TimeStarted.
The formate of the date in this column is YYYY-MM-DD HH:MM:SS

I want to do a SELECT against this table which ONLY pulls back data for today's date.

I have tried the following and it does not bring back any data:

SELECT 
	s.TaskID,
	t.TaskName,
	T.TimeStarted,
	s.Action,
	s.SourceHost As Source_File_Location,
	s.DestFileName As Destination_FileName,
	t.Success,
	t.LastErrorText
FROM
	Stats S
JOIN Taskruns T
	ON S.TASKID = T.TASKID	
	WHERE Success not like 'No xfers'
	AND	Success like 'Failure'
	AND Action not like 'Process'
	AND T.TimeStarted like (select convert(varchar(10),getdate(),120))
ORDER BY T.TimeStarted DESC

Post #1031099

ChrisM@Work

Posted Tuesday, December 07, 2010 3:23 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

A Little Help Please (12/7/2010)

I have a table which has a column called TimeStarted.
The formate of the date in this column is YYYY-MM-DD HH:MM:SS

Are you saying the column holds a character-type representation of a date, in this format, or the display format of a datetime is as shown? Assuming the latter, then the following will work:

	AND (t.TimeStarted >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) -- 2010-12-07 00:00:00.000
        AND t.TimeStarted < SELECT DATEADD(dd, 0, 1 + DATEDIFF(dd, 0, GETDATE()))) -- 2010-12-08 00:00:00.000

Edit: changed '=>' to '>='

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1031102

Adi Cohn-120898

Posted Tuesday, December 07, 2010 3:29 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:47 PM
Points: 2,023, Visits: 4,948

What is the type of the column TimeStarted? Is it a string or datetime?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1031107

A Little Help Please

Posted Tuesday, December 07, 2010 3:35 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:57 AM
Points: 322, Visits: 718

Hi,
The latter, the display format of a datetime is as shown.
The column is of Varchar(24) data type.

When I insert the lines you have provided I get the error:
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '>'.

SELECT 
	s.TaskID,
	t.TaskName,
	T.TimeStarted,
	s.Action,
	s.SourceHost As Source_File_Location,
	s.DestFileName As Destination_FileName,
	t.Success,
	t.LastErrorText
FROM
	Stats S
JOIN Taskruns T
	ON S.TASKID = T.TASKID	
	WHERE Success not like 'No xfers'
	AND	Success like 'Failure'
	AND Action not like 'Process'
	AND (T.TimeStarted => DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) -- 2010-12-07 00:00:00.000
	AND T.TimeStarted < DATEADD(dd, 0, 1 + DATEDIFF(dd, 0, GETDATE()))) -- 2010-12-08 00:00:00.000
ORDER BY T.TimeStarted DESC

Post #1031112

Dave Ballantyne

Posted Tuesday, December 07, 2010 3:43 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:05 AM
Points: 1,943, Visits: 8,229

Please see this link

http://www.karaszi.com/SQLServer/info_datetime.asp

Clear Sky SQL
My Blog
Kent user group

Post #1031115

Fai Ho Fu

Posted Monday, March 07, 2011 2:48 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 1:33 AM
Points: 48, Visits: 121

you could try....

DECLARE @today VARCHAR(10)
select @today = convert(varchar(10),getdate(),120)

then use the parameter in the where clause
AND T.TimeStarted like @today+'%'

or just use
AND T.TimeStarted like (select convert(varchar(10),getdate(),120))+'%'

Post #1074005

john.richards

Posted Monday, March 07, 2011 5:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:51 AM
Points: 1, Visits: 56

Hi,

Would the following not work:

SELECT
s.TaskID,
t.TaskName,
T.TimeStarted,
s.Action,
s.SourceHost As Source_File_Location,
s.DestFileName As Destination_FileName,
t.Success,
t.LastErrorText
FROM
Stats S
JOIN Taskruns T
ON S.TASKID = T.TASKID
WHERE Success not like 'No xfers'
AND Success like 'Failure'
AND Action not like 'Process'
AND convert(datetime,convert(varchar(12),T.TimeStarted)) =
(select convert(datetime,convert(varchar(12),getdate())))
ORDER BY T.TimeStarted DESC

this essentially puts the Timestarted and getdate in the same format, which although it inserts the time, makes it 00:00.000 on both sides of the clause.

Regards,

John

Post #1074068

Dave Ballantyne

Posted Monday, March 07, 2011 5:36 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:05 AM
Points: 1,943, Visits: 8,229

@John , it would work but its not SARGable. Or at least the date is not, which is what we are interested in here.

See the link i gave previously which describes this.

Clear Sky SQL
My Blog
Kent user group

Post #1074070

« Prev Topic | Next Topic »

Permissions