converting datetime | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

converting datetime

Hi, I ran into a small issue last night. I need to compare the datetime from my table with that the user has input. The user input is in the US format i.e., MM/DD/YYYY To get the desired results, I need to compare the datepart only with the one available in the table. I did select (convert varchar,starteddate,101) from coursedetails The output I have from this is in MM/DD/YYYY format but is now of varchar datatype. To compare with the user input data I need to convert back to datetime without the timedetails (i.e.,MM/DD/YYYY format). Somehow SQL server does not allow this. Is there any solution for this?
Thanks, StarWarsBigBang
Check: http://www.sql-server-performance.com/fk_datetime.asp Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
What is the problem I suppose your user input must be coming in string format only, if not try converting that value and equating the two.
My user input is in US datetime format i.e., mm/dd/yyyy (101 type format). I need to compare this with the value in the DB which is in the standard datetime format e.g.,2007-10-04 03:25:02.689 My comparision with the value in the DB should be for the date only and not the time part. As mentioned earlier the syntax I use:
select (convert varchar,starteddate,101) from coursedetails gives me the output in mm/dd/yyyy (101 type format) (same as input data format). But this value is now of varchar datatype. What I want to know is that can I convert this varchar back to datetime datatype. SQL server does allow. Hope I am clear. The URL mentioned by LuisMartin does not have any such example or illustration. Some one please find out a way….. Thanks.
Thanks, StarWarsBigBang
If your comparison string is in the US date format, it can be used without any further formatting. You can use a local datetime variable, and copy the string into that. To get only the date part from a datetime column, in US formatting, use CONVERT(VARCHAR(10), <column>, 101). When filtering on an indexed datetime column, note that the calculation on the column value means that SQL Server will probably not use the index. Check the link that Luis posted –http://www.sql-server-performance.com/fk_datetime.asp – for an excellent discussion of how to work with dates in SQL Server.
]]>