Return only date

[Ofina]

Why can I not find a function that will return 12/14/2011 for 12/14/2011 10:12:15? And have it remain as a date and not change to a string?

 

[RTag]

DECLARE @Datevalue DATETIME
SET @Datevalue='12/14/2011 10:12:15'

--In SQLServer2008 (not sure about 2005)
SELECT CONVERT(DATE,@DateValue)

--In SQLServer 2000
SELECT CONVERT(DATETIME,FLOOR(CONVERT(DECIMAL(19,4),@DateValue)))

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[Olaf Doschke]

RTag is indicating the Date type is not available for long. I am sure, it is introduced in SQL2008, not 2005. If you run a database in compatibiliy mode that could also mean you only get dattimes or smalldatetimes. But I am not sure about that.

What I'm sure about is Date was introduced in MSSQL2008 and works for databases started in that server.

And then you can do as RTag suggests, I'd do:

DECLARE @Datevalue as Date;
Declare @Datevalue as Date;
Set @Datevalue = Convert(Date,Getdate())

--or

Sekect @Datevalue = Convert(Date,datetimefield) FROM ...

Bye, Olaf.

 

[Olaf Doschke]

Sorry for the obvious errors...

Declare @Datevalue as Date;
Set @Datevalue = Convert(Date,Getdate())

--or

Select @Datevalue = Convert(Date,datetimefield) FROM ...

Bye, Olaf.

 

[markros]

For older versions of SQL Server, this is the way to strip time portion

select dateadd(day, datediff(day,'19000101',CURRENT_TIMESTAMP),'19000101')

Tibor K has a great blog you can check on this topic
The ultimate guide to the datetime datatypes


PluralSight Learning Library

 

[Ofina]

I should have said that we have SQL 2005.

That last one worked, thanks. Though it didn't so much strip the time portion as make it all 00:00:00. However, it still helps me achieve what I was going for.

 

[markros]

Datetime time always has both date and time portions. If you want a date only, in SQL 2005 and less you can only convert to character.

See cast and convert functions in BOL.

PluralSight Learning Library

 

[RTag]

Datetime contains information about date and time, but the way how this information is presented is up to you. If you need to print the date somewhere (paper, pdf, excel as text field etc.) you can use convert with format option:

DECLARE @Datevalue DATETIME
SET @Datevalue='12/14/2011 10:12:15'
SELECT CONVERT(VARCHAR@Datevalue,101)
--101 will set the format to mm/dd/yyyy

If you need to compare the datevalue to another datevalue then use the formula from the previous post to cut the time part.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[Ofina]

Well that would at least explain why I couldn't find one (I knew about the one that converts to varchar 101); it doesn't exist.

It does exist in 2008 though?

 

[RTag]

DATE in SQLServer 2008 is 3 bytes while DATETIME is 8 bytes. I guess the only one reason you may want to use DATE is if you are concerned about the size of the table.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.