Strip time from date

[PCRIDE]

Hi, I have a column in SQL actually, assuming the commands would be the same in Access. I have a Created Date field and its Date and Time.

I have my basic query

Select * FROM DATABASE name
[Column Names],
[Column Names],
[Column Names],
[Created Date]

How do I write the query to format the date as Date without the time using

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

or

CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)

??

 

[PCRIDE]

Let me clarify, I need the Output of the Created Date field to be only the Date to use in Reporting Services as Filters.

 

[PCRIDE]

Looks like I figured it out!

 

[SydneyGeek]

What is the default value for CreatedDate? Is it Now() or Date()?

Just saw the earlier post -- crossed in the ether.
What was the solution?

Denis

 

[PCRIDE]

I used

Select CAST([Created Date] As Date) As [Created Date]

and just had it between several other fields and it worked perfect.

 

[PCRIDE]

However when using a String i.e str@CreatedDate in Reporting Services to create a Parameter for filtering my report, it still didn't remove the time from date, it only worked in the query result in SQL or Reporting Services (VS 2008)

So still trying to figure that one out. I probably need to create a function that strips the time from the date and process the data as an additional column. ie Date2

 

[SydneyGeek]

Maybe you could use INT or similar (in Access I typically use CLng to get the day portion of a date)

Denis

 

[psycoperl]

I typically use Format([fieldname], "Date format code here") as MyDateColumn

I.e. Format([thedatetime], "MM/DD/YYYY")

 

[boblarson]

If it is within Access (and not an ADP), even with SQL Server linked tables, you can use

DateValue([thedatetime])

as another alternative.