SELECT data for today's date

Question

SELECT data for today's date

A Little Help Please

SSCarpal Tunnel

Points: 4764
More actions
December 7, 2010 at 3:12 am

#97784

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'
ANDSuccess like 'Failure'
AND Action not like 'Process'
AND T.TimeStarted like (select convert(varchar(10),getdate(),120))
ORDER BY T.TimeStarted DESC

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

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/

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 3

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'

ANDSuccess 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

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 4

Please see this link

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

Clear Sky SQL
My Blog[/url]

Fai Ho Fu SSC Veteran Points: 241 More actions · Answer 5

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))+'%'

john.richards SSC Enthusiast Points: 161 More actions · Answer 6

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

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 7

@john-2 , 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[/url]