LT1415
asked on
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
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
or
SELECT InputText, convert(varchar(10), LogTime1, 120) FROM dbo.Log Where FnctName = 'blah' and UserName ='blah' And LogTime >'2010-12-31'
SELECT InputText, convert(varchar(10), LogTime1, 120) FROM dbo.Log Where FnctName = 'blah' and UserName ='blah' And LogTime >'2010-12-31'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT InputText, convert(varchar, LogTime1, 101) FROM dbo.Log Where FnctName = 'blah' and UserName ='blah' And LogTime >'2010-12-31'