Remove time part from datetime column

Question

Post reply

Remove time part from datetime column

Prakash Sawant

SSCrazy

Points: 2173
More actions
November 19, 2006 at 11:35 pm

#114746

I have a datetime column from which i want to remove time part & keep the output as date format only & varchar
i have tried the below code which gives me required output, what i want is is their any better way to get the same result?

select CAST(CAST(getdate() As VARCHAR(12)) AS DATETIME)

------------
Prakash Sawant
http://psawant.blogspot.com

Viewing 15 posts - 1 through 15 (of 17 total)

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Try this: SELECT CONVERT( varchar, GETDATE() , 101)

You can change the 101 to any other number of formats to fit your need...

I wasn't born stupid - I had to study.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Datetime value will always have part time. That's why it's named DATE-TIME.

The best you can do is to make time part = 00:00:00.000

Is it what you are after?

_____________
Code for TallyGenerator

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

DECLARE @Value AS datetime

SET @Value = '11/20/2006'

SELECT @Value

SELECT CONVERT( varchar, @Value, 101)

I wasn't born stupid - I had to study.

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 4

I want my final output should be date only not varchar.

this is what i am useing now..

select convert(datetime,convert(varchar,getdate(),105),105)

------------
Prakash Sawant

http://psawant.blogspot.com

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Can you elaborate? Your two choices: smalldatetime and datetime will yield "time". Are you using this value in a select statement or passing it to another database or a front-end application?

Thanks.

I wasn't born stupid - I had to study.

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 6

I have data in one table without time & while writing the query i have the statment says col1> getdate(), which gives different result as getdate comes with time so i want only date part not time part.

Hop this will give you an overview of what i am doing.

------------
Prakash Sawant

http://psawant.blogspot.com

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

If I am understanding you correctly, you have a column in one table that is varchar with a date only as the entry; I do not know any other datatype which would support a date with no time. SQL Server should be able to handle the conversion implicitly without having to use these functions to change your GETDATE().

Not knowing your indexing, etc., I cannot comment on the optimization plan effects.

If you can give us an example, (including a small set of data and the table designs) of what you are hoping to achieve: your desired results and you actual results. That may help me or someone else recognize the problem...

I wasn't born stupid - I had to study.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 8

When SQL Server converts a date stored as a string without a time value to a datetime, it assumes the time to be 00:00:00.000 AM. So even if you do not see a time value, when it is compared as a date, it will be done so as being 12 AM.

If you wnt to find rows where Col1 is today's date or later, then either do what Farrell suggested (converting back to datetime) or use the DateDiff() function to check to see if the time difference is more than 24 hours different.

Where DateDiff(day, Col1, getdate) >= 1

Or

Where DateDiff(hour, Col1, getdate) >= 24

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 9

I'm not sure if this is what your after but I found this snip out there somewhere onetime and use it all over. Better performance that the convert.

Select

-- Standard date with time

getdate() As DateWithTime,

-- Returns Date Portion Only

dateadd(d, 0, datediff(d, 0, getdate())) As DateOnly

Good luck

Tom

Farrell Keough SSCoach Points: 17414 More actions · Answer 10

I think that will still return a "datetime" value. Look it up in BOL. (It returned the 12:00:00 AM value in my Query Analyzer)

I wasn't born stupid - I had to study.

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 11

Thanks a lot for all your help.

I wanted my final output as datetime.

------------
Prakash Sawant

http://psawant.blogspot.com

Mike Eilbeck SSC Veteran Points: 226 More actions · Answer 12

Tom Goltl (11/21/2006)
-- Returns Date Portion Only
dateadd(d, 0, datediff(d, 0, getdate())) As DateOnly

That SQL was just what I was looking for. Thanks Tom! 🙂

No Idea Say Hey Kid Points: 675 More actions · Answer 13

I was doing something a little different. I had a column of dates that needed to be put in the MM/DD/YYYY format so it could be imported into SAS.

Most of the examples used getdate(), but Farrell Keough's example worked perfectly for me!

I used SELECT CONVERT(varchar, Dte_Eligibility_Begins , 101) and it converted 2008-01-01 00:00:00 to 01/01/2008.

Some of the other examples still put the time part in, even on just using getdate().

Thanks for the help.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 14

Looks like confusing running around this thread.

I think what the OP mean't by no time was '00:00.00' which is actually still time, it's just the beginning of the date.

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life