Problem in DateTime Query
-
Hi !
I have a column in a Table in my Db that stores exit Date of employees. It has "DateTime" type. ( like : 2007/03/15 12:00:00)
I want to query the column "exitDate" on specific year and month .
my query code is like this :
Select * From [Vacation] Where (exitDate like @year_month) "
and @year_month is a SQL parameter with Nchar type. ( e.g : 2007/03% )
but this code don't aswer to me !!
How can I query a DateTime column on an specific year or month or both of them ?????
-
If you can split your parameter into seperate month and year parameters, try:
select * from vacation where datepart(mm,exitdate) = @month and datepart(yy,exitdate) = @year
--datename function does roughly the same thing.
-
Select * From [Vacation] Where Year(exitDate) like @year_month
and Month(exitDate) like @year_month
-
Using LIKE is forcing it to convert all the datetime fields to strings, but not necessarily in the same format as your @year_month parameter, so none of the records match in a text comparison.
You could force the datetime field to the same format as your argument:
SELECT
* FROM Vacation
WHERE CONVERT(CHAR(7), exitDate, 111) = @year_month
Or you could force your argument to a date
SELECT * FROM Vacation
WHERE DATEDIFF(MONTH, CAST(@year_month + '/01' AS DATETIME), exitDate) = 0
If you want to take advantage of an index on exitDate for performance, you have to avoid using it in any function. You can use as many functions as you like on @year_month though.
SELECT * FROM Vacation
WHERE exitDate BETWEEN CAST(@year_month + '/01' AS DATETIME)
AND DATEADD(MONTH, 1, CAST(@year_month + '/01' as datetime)) - 0.00000004
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply