March 23, 2006 at 2:46 am
1. SELECT *FROM SAMPLE WHERE
CONVERT(VARCHAR,CREATED_DATE,101) >= @START_DATE
This query returns me 10 rows.
2. The same query is modified on the Right hand side of the conditinal operator as follows:
SELECT *FROM SAMPLE WHERE
CONVERT(VARCHAR,CREATED_DATE,101) >=
CONVERT(VARCHAR,@START_DATE,101)
This query returns me 0 rows.
What is the problem with query. Can anyone explain the reason for this issue.
March 23, 2006 at 12:10 pm
1)Always specify the datalength when converting, otherwise you risk truncation
->CONVERT(VARCHAR(20),CREATED_DATE,101)
2)What datatypes are @START_DATE and CREATED_DATE?
March 23, 2006 at 9:58 pm
@START_DATE and CREATED_DATE are of Datetime data type.
March 24, 2006 at 5:34 am
101 is mm/dd/yyyy, so is not canonical; i.e. the year has less significance in a character string representation than the month or day. Therefore, character comparisons are only valid for equivalence.
In general, datetime comparisons are best done using the native datetime datatype, rather than relying on conversions, whether implicit or explicit.
March 24, 2006 at 6:49 am
If for some reason you must convert the datetime values (to strip times, for example), use style 112 instead:
SELECT *
FROM SAMPLE
WHERE CONVERT(VARCHAR(8),CREATED_DATE,112) >= CONVERT(VARCHAR(8),@START_DATE,112)
March 24, 2006 at 7:51 am
What value are you inputting for @START_DATE?
Run this and see what it returns: (replace the ? with the actual value)
DECLARE @START_DATE DATETIME
SET @START_DATE = '?'
SELECT CONVERT(VARCHAR,@START_DATE,101)
-SQLBill
March 24, 2006 at 8:49 pm
I think the point is being missed here. Both queries are incorrect.
Consider:
select convert(varchar, getdate(), 101), getdate(),
case when convert(varchar, getdate(), 101) = getdate() then 'equal' else 'not equal' end
03/24/2006 2006-03-24 20:45:04.533 not equal
The first query is doing a character string comparison of dates in two different styles (@START_DATE is implicitly casted to varchar). The number of rows it returns is meaningless because it is incorrect.
The second query is incorrect, because, for example, 01/02/2005 will evaluate to greater than 01/01/2006 because the day comes before the year in the character representation. Dates represented as character strings must be canonical (most significant digits first) in order to be compared correctly for anything besides equality or inequality (i.e. > or <).
March 25, 2006 at 5:02 am
Fred is correct. That's why using my query will work, since style 112 returns a date in the form of YYYYMMDD. Personally, though, I always convert back to datetime to perform date comparisons/calculations. If I needed to regularly perform queries requiring the date part only, I would also consider storing this in the table, possibly as a computed column.
Here's a sample script:
CREATE TABLE sample
(
id int IDENTITY(1,1) PRIMARY KEY
, created_date datetime
)
GO
SET NOCOUNT ON
INSERT sample (created_date) VALUES ('20060101 09:24:00')
INSERT sample (created_date) VALUES ('20060215 11:33:00')
INSERT sample (created_date) VALUES ('20060301 08:28:00')
INSERT sample (created_date) VALUES ('20060302 22:42:00')
INSERT sample (created_date) VALUES ('20060303 09:12:15')
INSERT sample (created_date) VALUES ('20060305 12:13:14')
INSERT sample (created_date) VALUES ('20060308 06:18:22')
SET NOCOUNT OFF
DECLARE @start_date datetime
SET @start_date = '20060301'
SELECT *, CONVERT(varchar(8), created_date, 112) AS DateOnly
FROM sample
WHERE CONVERT(varchar(8), created_date, 112) >= CONVERT(varchar(8), @start_date, 112)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply