I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220)
SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'
Where did you find that preferred way to compare dates is LIKE. You might want to use many date functions to compare dates available in 2k and 2k5.
OR if you incist on using LIKE convert "wheninstalled" to format that you specify in LIKE.
|||datetime is stored in database in an internal format. It is not in YYYY-MM-DD or whatsoever format.>> I have dates in this column ie 2006-02-13 11:30:54.220
This just how Query Analyser represent the date or format the date and time when it return the records.
>> SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'
To retireve records for month of Feb 2006,
SELECT * FROM workorderhistory WHERE wheninstalled >= '2006-02-01' and wheninstalled < '2006-03-01'
|||
Here is the text from the sql help file.
It is recommended that LIKE be used when you search for datetime values, because datetime entries can contain a variety of dateparts. For example, if you insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.
Thanks for your help.
|||Thanks for the help... I don't konw why I did not think of doing it that way... Slipped my mind I guess.|||try this may be helpful for you
SELECT * FROM
WHERE (CAST(FLOOR(CAST([date] AS FLOAT)) AS DATETIME) = '3/14/2006')
How can I use a like statement in there. I tried doing
select * from workorderhistory where (CAST(FLOOR(CAST([date] AS FLOAT)) AS DATETIME) like '03/%') and this did not work. Basically I want to see all the workorders that were installed in the month of march.
