Converting datetime to shortdatetime

I am trying to convert a date field so I can yes it in a query

Original data is CrDate with data type of datetime. 2007-04-02 23:00:15.397
I would like to convert it to smalldatetime so I can run queries against it i.e. WHERE (CrDate = '4/2/2007')

I see a function CONVERT ( data_type [ ( length ) ] , expression [ , style ] ), but I'm not sure what to substitute such as style.

Environment SQL Server 2000

I would appreciate any suggstions.  Thanks.  DC900

Who is Participating?
lahousdenConnect With a Mentor Commented:
Just as an aside - if the only reason for changing the type is for being able to find rows in the database then datetime will work pretty much the same as smalldatetime.  I.e. If none of your Invoices were created at exactly midnight, then

select *
from Invoices
where created = '4/1/2007'

will find no rows whether created is datetime or smalldatetime.  The "gotcha" for new SQL developers is to query by range, e.g.

select *
from Invoices
where created >= '4/1/2007' and created < '4/2/2007'

will find all the Invoices for 4/1/2007.
lahousdenConnect With a Mentor Commented:
Incidentally, if you still feel you want to change the type then you can use Enterprise Manager to change the column type in the table - it will complain about losing information but you can choose to ignore this and go ahead with the change.
nakul_vachhrajaniConnect With a Mentor Commented:
I believe that even without conversion, your query should perform fine enough.
Even then if you are using stored procedures, you can try using SET DATEFORMAT (Refer SQL BOL for more details on this)
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

YZlatConnect With a Mentor Commented:
SELECT CONVERT(VARCHAR(10), datetimefield, 101) AS formatted_date
folderolConnect With a Mentor Commented:
Datetime is stored in SQL as a float value, the integer portion is days and the fractional portion is time. To strip off the time from a datetime value, you only need to convert it to an integer, the following are all popular formulas for doing this.  Copy / paste these lines to Query Analyzer to see the results.

-- returns string
-- returns datetime
cast(datediff(day,0,getdate()) as datetime),
cast(floor(cast(getdate() as float)) as datetime)

YZlat's Comment is the correct solution.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.