CODEWITHSUNDEEP
sqlsql-server
Java Questions
Java Questions

Reputation: 7963

How to filter only the date from a string stored in a varchar

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

Answers (5)

Happy
Happy

Reputation: 826

SELECT * FROM whatever WHERE some_Date LIKE '02-Jul-12%';

Upvotes: 3

paul
paul

Reputation: 22021

To select rows with today's date (not time)

select * from myTable where datediff(dd, dateColumn, getdate()) = 0

Upvotes: 1

Gordon Linoff
Gordon Linoff

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

Pranay Rana
Pranay Rana

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

Jacco
Jacco

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

Related Questions