[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Converting datetime to shortdatetime

Posted on 2007-04-03
6
Medium Priority
?
4,755 Views
Last Modified: 2009-02-03
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

0
Comment
Question by:dc900
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 10

Accepted Solution

by:
lahousden earned 750 total points
ID: 18846135
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.
0
 
LVL 10

Assisted Solution

by:lahousden
lahousden earned 750 total points
ID: 18846158
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.
0
 
LVL 5

Assisted Solution

by:nakul_vachhrajani
nakul_vachhrajani earned 300 total points
ID: 18846159
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)
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 150 total points
ID: 18846414
SELECT CONVERT(VARCHAR(10), datetimefield, 101) AS formatted_date
0
 
LVL 19

Assisted Solution

by:folderol
folderol earned 300 total points
ID: 18846588
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.

select
-- returns string
convert(char(10),getdate(),101),  
-- returns datetime
convert(datetime,convert(char(10),getdate(),101)),
cast(datediff(day,0,getdate()) as datetime),
cast(floor(cast(getdate() as float)) as datetime)

Tom
0
 

Expert Comment

by:mkennon
ID: 23537112
YZlat's Comment is the correct solution.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question