• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

SQL Query Help Truncating Date TimeStamp for Query

I need to write a query that will return a distinct InputText (address) for a distinct DAY, which in our database is denoted by column log time.
Our log time column appends a time stamp to the date which is becoming problematic

This query

SELECT Distinct InputText, LogTime  FROM dbo.Log Where FnctName = 'blah' and UserName ='blah' And LogTime >'2010-12-31'

Returns the following results listed below

I need a way to truncate the log time to get rid of the time feature so that this result just returns
one unique look up a day so that this:

InputText                                                  Log Time
Address: 1 ARABIAN RD, Zip: 98862      2011-07-15 08:52:27.000
Address: 1 ARABIAN RD, Zip: 98862      2011-07-15 08:56:25.000

Becomes this
Address: 1 ARABIAN RD, Zip: 98862      2011-07-15


0
LT1415
Asked:
LT1415
  • 3
1 Solution
 
ralmadaCommented:
try

SELECT InputText, convert(varchar, LogTime1, 101)  FROM dbo.Log Where FnctName = 'blah' and UserName ='blah' And LogTime >'2010-12-31'
0
 
ralmadaCommented:
or

SELECT InputText, convert(varchar(10), LogTime1, 120)  FROM dbo.Log Where FnctName = 'blah' and UserName ='blah' And LogTime >'2010-12-31'
0
 
ralmadaCommented:
oops, missed the distinct there

SELECT distinct InputText, convert(varchar, LogTime1, 101)  FROM dbo.Log Where FnctName = 'blah' and UserName ='blah' And LogTime >'2010-12-31'

or

SELECT distinct InputText, convert(varchar(10), LogTime1, 120)  FROM dbo.Log Where FnctName = 'blah' and UserName ='blah' And LogTime >'2010-12-31'

also you can do


SELECT InputText, max(LogTime1)  FROM dbo.Log Where FnctName = 'blah' and UserName ='blah' And LogTime >'2010-12-31'
group by InputText
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now