April 20, 2011 at 12:07 am
Hi All,
My LogDate Column is in format '2011-04-20 10:29:50.547' if YYYY-MM-DD HH:MM:SS
I want to make it YYYY-MM-DD format only.
How to write it in using sql query if my column name is 'LogDate'.
Select FirstName, LastName, LogDate
FROM Employee
Where LogDate = '2011-04-20 10:29:50.547'
I'm confussed with Where Clause. How to format the LogDate ?
Thanks in Adv.
April 20, 2011 at 12:38 am
Try this:
Select FirstName, LastName, Convert(varchar(10),LogDate,126)LogDate
FROM Employee
Where datediff(d,LogDate,''2010-04-20'')=0
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
April 20, 2011 at 1:49 am
Actually I'm getting an error at ''2010 ......
FirstName LastNameLogDate
-------------------------------------------------
SamDeSouza2011-04-19 17:31:02.923
JohnWilson2011-04-19 18:50:27.233
TinaAlbert2011-04-20 10:29:50.547
RosyPinto2011-04-20 12:04:54.127
Select FirstName, LastName, Convert(varchar(10),LogDate,126)LogDate
From Employee
Where <Desired Date Paramater Here>
Here I want to send the desired paramater for LogDate in format "YYYY-MM-DD"
April 20, 2011 at 1:57 am
Sorry friend,
The error might be due to the double quotes that appear before the date parameter.This occurred when i copied the query from query analyzer.Please change it as '2010-04-20' instead of ''2010-04-20''.
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
April 20, 2011 at 1:57 am
Select FirstName, LastName, Convert(varchar(10),LogDate,126)LogDate
FROM Employee
Where datediff(d,LogDate,'2010-04-20')=0
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
April 20, 2011 at 2:12 am
Actually I tried it both ways.....
April 20, 2011 at 3:20 am
What is the error that occurs?
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
April 21, 2011 at 2:04 pm
Sorry guess i was wrong
April 24, 2011 at 10:35 pm
What???????????????????
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
April 26, 2011 at 2:03 pm
This will find all log entries from April 20, 2011, which I think is what you're trying to do here?
SELECT FirstName, LastName, LogDate
FROM Employee
WHERE LogDate >= '2011-04-20' AND LogDate < '2011-04-21'
I would suggest something like the above format rather than an earlier posted suggestion that uses a function on the left side of the equals sign: Where datediff(d,LogDate, '2010-04-20')=0
If your date column is indexed, you won't be able to use the index for queries b/c your WHERE clause replaces LogDate (indexed) with the result of the DATEDIFF() function (not indexed). There's a good write-up about this here (http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/[/url]) -- see Item #2, "Functions on indexed columns in predicates." It's a good article!
HTH,
Rich
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply