Solved

Casting a datetime field to a smalldatetime field not working

Posted on 2003-10-21
7
3,032 Views
Last Modified: 2012-06-27
This just seems silly, but I simply cannot get a cast to work. When a record is saved to the db, an entry date (in datetime format) is saved to entry_date field. In a query, I need to get this date, but as a smalldatetime (mm-dd-yyyy) value.

I've tried:

1) CONVERT(smalldatetime, Entry_Date, 101) and still get hours:minutes:seconds
2) CAST(Entry_Date as smalldatetime(101)) as EntryDate and get the error CAST or CONVERT: invalid attributes specified for type 'smalldatetime'
3) cast(Entry_Date as smalldatetime) as EntryDate and get hours:minutes:seconds
4) cast(Entry_Date as smalldatetime, 101) and get the error Incorrect syntax near ','.

This can't be that hard. What am I doing wrong???
0
Comment
Question by:MovnOn
  • 4
  • 2
7 Comments
 
LVL 18

Accepted Solution

by:
lludden earned 125 total points
ID: 9592831
Convert(varchar(10),Entry_Date,101)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9593097
SmallDate includes the time, so you should convert to a varchar as has been suggested.

Anthony
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 9593110
Since you need it in the format mm-dd-yyyy use the style = 110, as in:

Select Convert(varchar(10), getdate(), 110)

Anthony
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9593131
Also, if all you want to do is save to a datetime column without the time, than use something like this:
Select Cast(Floor(Cast(GetDate() As float)) as datetime)

Sorry for the multiple posts.
Anthony
0
 

Author Comment

by:MovnOn
ID: 9593294
I'm adding 75 points to the question and will split them: lludden gets 125 for the first correct answer and 50 to Anthony for the additional info.

Fair enough?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9593470
That is fine with me.

Anthony
0
 
LVL 18

Expert Comment

by:lludden
ID: 9593720
Works for me.  Glad to be of help
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 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

12 Experts available now in Live!

Get 1:1 Help Now