Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SELECT data for today's date Expand / Collapse
Author
Message
Posted Tuesday, December 7, 2010 3:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 9, 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
Posted Tuesday, December 7, 2010 3:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 7,191, Visits: 13,645
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
Posted Tuesday, December 7, 2010 3:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:20 AM
Points: 2,107, Visits: 5,405
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
Posted Tuesday, December 7, 2010 3:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 9, 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
Posted Tuesday, December 7, 2010 3:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:33 AM
Points: 1,949, Visits: 8,310
Please see this link

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




Clear Sky SQL
My Blog
Kent user group
Post #1031115
Posted Monday, March 7, 2011 2:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 10:10 AM
Points: 48, Visits: 139
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
Posted Monday, March 7, 2011 5:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 4:01 AM
Points: 1, Visits: 85
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
Posted Monday, March 7, 2011 5:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:33 AM
Points: 1,949, Visits: 8,310
@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 »

Add to briefcase

Permissions Expand / Collapse