select convert(char,getdate(),101) will return the date without the time part.

Is there a way doing the same without converting it to a char? I need the
date part only but it must be a datetime data type.

Why do you need a datetime type ?
There is no Date Type in SQL Server, so you can´t convert itin something
like theformat 01-01-05 unless you convert it to char / varchar.

If you want to do something in your frontend with that data you should
consider letting your frontend do this.


HTH, Jens SUessmeyer.

---
http://www.sqlserver2005.de
---

Show quoteHide quote

Its for SQL Reporting services, it needs a datetime parameter, if you
convert\cast it into a date only part it doesnt work. I'd rather display the
date paramter as "2005-10-20" rather than "2005-10-20 00:00:00"

Show quoteHide quote

1)  The basic principle of a tiered architecture is that display is
done in the frpon, not the data base.  You are still writing code as
SQL were a Cobol file system, integrated worth with the applications.

2) Time comes in duration, so you need to code the entire date
'2005-10-20' as a pair of srart and stop times in your schema. :
(''2005-10-20 00:00:00', ''2005-10-20 23:59:59.999999..)  with the
appropiate constraints.  This will let you use BETWEEN predicates for
queries.

3) SQL Server has a DATETIME data type; there is no DATE data type.  If
you want an ugly proprietary way to get midnight try this:

CAST (FLOOR (CAST (@my_date AS FLOAT)) AS DATETIME) AS start_date;

You can then get the open end of the interval with a CEILING() function
and a DATEDIFF to clip off a fraction of a second.

On Fri, 1 Jul 2005 04:59:23 -0700, NH wrote:

a datetime ALWAYS represents an instant in time, not a whole day.

If you need a datetime value for Reporting Services, you're not going to
get anything else. 

If Reporting Services is demanding a datetime value, you must give it a
datetime value.

If Reporting Services is not printing what you want, then perhaps you'd
better ask in a Reporting Services group.  Here we are but SQL programmers.

Then you can cast or convert it further:

select
    cast (convert(char,getdate(),101)  as datetime)
,   convert (datetime, convert(char,getdate(),101))

select convert(char,getdate(),101) will return the date without the time
part.

Is there a way doing the same without converting it to a char? I need the
date part only but it must be a datetime data type.

But that still returns the date as "2005-10-20 00:00:00"....

Show quoteHide quote

Then use CONVERT!  There is no such thing as a date only.  For the third
time.

If you read the previous reply you'll see that it suggested you could do what
I want. So its not like I am dumb and cant undestand a replies. So why dont
you just go away if you are not going to be helpful

Show quoteHide quote

I'll go away.  Just keep in mind that I was trying to help you "get it"
because you don't seem to.  Have fun.

Can't you format it in RS?

But that still returns the date as "2005-10-20 00:00:00"....

Show quoteHide quote

Case Statement?
View Explaination
Index Question
Warning: Null value is eliminated (causing sproc to fail)
Query urgent