Solved

Converting datetime to shortdatetime

Posted on 2007-04-03
6
4,654 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
6 Comments
 
LVL 10

Accepted Solution

by:
lahousden earned 250 total points
Comment Utility
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 250 total points
Comment Utility
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 100 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 50 total points
Comment Utility
SELECT CONVERT(VARCHAR(10), datetimefield, 101) AS formatted_date
0
 
LVL 19

Assisted Solution

by:folderol
folderol earned 100 total points
Comment Utility
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
Comment Utility
YZlat's Comment is the correct solution.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now