Reputation: 7963
Ii have values stored in the SQL Server in the following manner : 02-Jul-12 12:00:00 AM
here the time and minutes, seconds can be anything like 02-Jul-12 12:15:52 PM
,02-Jul-12 6:02:12 AM
so on.
I want to have a where condition which will omit the time and take the data based on the date like the following where some_Date='02-Jul-12'
How would I do this?
Upvotes: 1
Views: 3715
Reputation: 22021
To select rows with today's date (not time)
select * from myTable where datediff(dd, dateColumn, getdate()) = 0
Upvotes: 1
Reputation: 1271231
If you are storing dates as characters -- which is not recommended -- you should at least use ISO format: YYYY-MM-DD hh:mm:ss. This makes the date useful for sorting and comparisons ("<" works, ">" works, "between" works as well as equals).
To extract the date, you can then use left(datestr, 10)
. In your format, you would use:
where left(datestr, 9) = '01-Jan-13'
If you are storing the fields as a datetime
or smalldatetime
, you may think they are stored as a string. They are not. They are stored as some number of days since some particular date, with day parts stored as fractional days. If you are using SQL Server 2005 or greater, then the best way is:
where cast(datetime as date) = '2013-01-01' -- I recommend ISO formats, even for constants. '20130101' is even better
Upvotes: 1
Reputation: 176956
SELECT * FROM dbo.tbl_MyTable
WHERE
REPLACE(CONVERT(VARCHAR(9), DateTimeValueColumn, 6), ' ', '-')='02-Jul-12'
or
On chage in code is instead of using getdate function voncert you datestring in datetime format and do compare this follow query will work for you
SELECT * FROM dbo.tbl_MyTable
WHERE
CAST(CONVERT(CHAR(10), DateTimeValueColumn, 102) AS DATE) =
CAST(CONVERT(CHAR(10),GETDATE(),102) AS DATE)
Upvotes: 1
Reputation: 3272
If you are on SQL2008 or later, you can cast your DATETIME
to DATE
.
See this post: http://blog.sqlauthority.com/2012/09/12/sql-server-get-date-and-time-from-current-datetime-sql-in-sixty-seconds-025-video/
But in a WHERE-clause it is better to search between dates, like this:
DECLARE @startDate DATETIME = '02-Jul-2012'
DECLARE @endDate DATETIME = DATEADD(DAY, 1, @startDate)
SELECT * FROM [table] WHERE [some_Date] BETWEEN @startDate AND @endDate
Upvotes: 1