Return Date

Question

Return Date

Jones'e

Right there with Babe

Points: 774
More actions
January 5, 2007 at 1:09 pm

#171737

Is there away to only return the date part and not the time of say like GetDate()?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 1

Depends what you are looking for. If you are just looking to format the datetime value, use:

CONVERT(varchar(30), getdate(), 101)

If you want to keep the data type, you need to understand how datetime is stored. Read in BOL on datetime datatype, and you will see that the storage is 2 4-byte integers, one for days +/- since 1/1/1900, and the other for ticks since midnight. So date and time are always there, and the default display of a datetime value will include both. If you want one or the other, you adjust the display of the value by using Convert, like above.

Hope this helps

Mark

Jones'e Right there with Babe Points: 774 More actions · Answer 2

Mark thanks! That was exactly want I was looking for. Datetime always seems to give me problems.

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Mark's solution is OK if you need to convert the date to VARCHAR anyway. If you want to keep it as DATETIME, just strip away the time portion, this is preferred way to do it:

select DATEADD(d, DATEDIFF(d, 0, @YourDate), 0)

You can use either GETDATE() or CURRENT_TIMESTAMP in place of the datetime parameter/column, if you need to refer to "today".

Conversion to varchar and back to datetime has slightly worse performance (although nothing to be really troubled about unless it is more than 100k rows), and this solution also gives you possibility to change the date easily (like if you need to pass a date and find rows that are from the previous day):

select DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)--today

select DATEADD(d, DATEDIFF(d, 1, GETDATE()), 0)--yesterday

select DATEADD(d, DATEDIFF(d, -1, GETDATE()), 0)--tomorrow

Read this to find out more, e.g. how to obtain date of last Friday independent on language (DATEFIRST) setting: http://www.karaszi.com/SQLServer/info_datetime.asp

Jones'e Right there with Babe Points: 774 More actions · Answer 4

Thanks that worked out great as will and it keep it as datetime!