• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3062
  • Last Modified:

Casting a datetime field to a smalldatetime field not working

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
MovnOn
Asked:
MovnOn
  • 4
  • 2
2 Solutions
 
lluddenCommented:
Convert(varchar(10),Entry_Date,101)
0
 
Anthony PerkinsCommented:
SmallDate includes the time, so you should convert to a varchar as has been suggested.

Anthony
0
 
Anthony PerkinsCommented:
Since you need it in the format mm-dd-yyyy use the style = 110, as in:

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

Anthony
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Anthony PerkinsCommented:
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
 
MovnOnAuthor Commented:
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
 
Anthony PerkinsCommented:
That is fine with me.

Anthony
0
 
lluddenCommented:
Works for me.  Glad to be of help
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now