datetime and get date only

Question

datetime and get date only

pat-261672

Hall of Fame

Points: 3325
More actions
December 10, 2008 at 5:19 pm

#216395

How can i get date only from datatime filed.
CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME) i tried this but still getting 2008-12-10 00:00:00.000
I only want 12/10/2008
please guide!!
I tried cast and convert but still getting same answer i might be doing something wrong?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

As long as it's datetime, you'll still get the time field. Try using the CONVERT with 101 and CAST to VARCHAR. That should do what you need.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

pat-261672 Hall of Fame Points: 3325 More actions · Answer 2

so will it be

select CONVERT ( smalldatetime , start_date , 101 ) from table

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

Yeah, but why do you want to format the code is SQL? Unless you're formatting to write to a file, then you should let the formatting be done in the GUI.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

pat (12/10/2008)
so will it be
select CONVERT ( smalldatetime , start_date , 101 ) from table

No, you need to convert to a string datatype (char, nchar, varchar, nvarchar). Since the format is fixed, using char would be the best choice:

SELECT CONVERT(char(10), start_date, 101) FROM table;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 5

Or if you want to make things easier, at least for me, use DAY(), MONTH(), YEAR() and concatenate those (after casting to char)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

Yeah, what Jeffrey wrote is correct for the cast & conversion. However, I do agree with Jeff. Normally I wouldn't recommend doing this sort of formatting in SQL Server.

BTW, 2008 introduces data and time data types in addition to the good old datetime.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning