Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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