date range query not returning anticipated results

Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
Query:
select startdate
from auditlog
where startdate between '6/18/2004' and '6/20/2004'
I get results like this, with dates outside the specified range:
6/18/2004
6/18/2004
6/2/2004
6/19/2003
6/19/2003
6/2/2004
6/2/2004
6/2/2004
Any suggestions?
Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:

>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:

>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>

Date range + parallel time

Hi,

I'm quite new to MDX and found a problem I can not solve. I have a fact table which contains a start date and an end date, plus several measures. I also have a time dimension, with years and months. I need to dynamically build an mdx query that, given a year and a month, would show any measure in the fact table whose start date is lower than the given date and the end date is higher than the given date. To complicate things a bit, the same query must show the same measure in the previous year to the given date. Both results must be in the same axis.

We know how to show each result separately, using date ranges in the WHERE clause, but have no idea about how to combine both results.

Thanks

--eduardj

Finally solved it.

I created a date range dimension, with all the date ranges in the original fact table, and a measure-less fact table with a relationship with the new date range dimension and the old time dimension. After that, I created a Many-to-Many relationship between the original fact table and the time dimension through the newly created fact table. Besides, I created a hierarchy in the time dimension which related the times (parallel) that always had to be shown together.

Kind of messy, but it works beautifully.

thanks

--eduardj

Date range ...

Hi,
I have to count the number of invoices between two dates (starting date and ending date).
I have a time dimension, and a another with the two dates.
I've created a measure :

Code Snippet

CREATE MEMBER [CTNAV].[Measures].[Nb]
AS count(
Filter
(
[Bon Vouchers].[N° Bon Vouchers].[N° Bon Vouchers].Members,
[Bon Vouchers].[Starting Date ].CurrentMember.MemberValue >=
[Time].[Date].CurrentMember.MemberValue
)
)

The problem is that if I display all the dates, each measure is at 0 but the total is ok (>0).

Sorry for my english.

Thanks for your help.

date question

in my database i stored information on what time the purchaed were made something like this...

ItemNo|dateofPurchased
*********************
0001 | 12/08/2003
0002 | 1/24/2002
0003 | 5/18/2001

......

if i wish to get the itemno that were purchased before year 2002 how do i do it by sql statements?
thanks...Originally posted by HjF_84
in my database i stored information on what time the purchaed were made something like this...

ItemNo|dateofPurchased
*********************
0001 | 12/08/2003
0002 | 1/24/2002
0003 | 5/18/2001

......

if i wish to get the itemno that were purchased before year 2002 how do i do it by sql statements?
thanks...
The answer depends on your DBMS. For Oracle you would say:

select itemno
from xxx
where dateofpurchased < TO_DATE('01-JAN-2002','DD-MON-YYYY');

But TO_DATE is Oracle-specific.|||i using sql server2000..., so anyone know how to retreive it...?

date query question

Hi I have a calendar that the user selects a date from without a time.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
--
Paul G
Software engineer.found solution!
--
Paul G
Software engineer.
"Paul" wrote:
> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.

date query question

Hi I have a calendar that the user selects a date from without a time.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
Paul G
Software engineer.
found solution!
Paul G
Software engineer.
"Paul" wrote:

> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.

date query question

Hi I have a calendar that the user selects a date from without a time.
Anyhow I need the query to return the record even though the data in the
table contains a time.
this works but I do not have a time in the search.
select * from table where DateTime ='2003-05-09 10:00:00'
this does not work
select * from table where DateTime = '2003-05-09' but I need something
quivalent that will work.
thanks.
--
Paul G
Software engineer.found solution!
--
Paul G
Software engineer.
"Paul" wrote:

> Hi I have a calendar that the user selects a date from without a time.
> Anyhow I need the query to return the record even though the data in the
> table contains a time.
> this works but I do not have a time in the search.
> select * from table where DateTime ='2003-05-09 10:00:00'
> this does not work
> select * from table where DateTime = '2003-05-09' but I need something
> quivalent that will work.
> thanks.
> --
> Paul G
> Software engineer.

Date Problem

I have a problem with date parameter. I have selected data type as date time. I would like the date to be in British format. When I select a date from Calendar control, for example 25 December, then I get a error>

The value provided for the report prameter Startdate is not valid for its type.

Can anyone please help me on this?

regards

Josh

Hello JoshKer.

In Reporting Servies you can define the default Language of the reports, this Language is used for the Date Culture...

To edit him : In Layout Tab / Click on the Yello Part (out of the report) / Properties or F4 / then you accessed the Report Properties...

Alter the Language Propertie, to English (United KingDown)

If this not correct your error, then you can make the CAST on the Date Parameter on the Query....

I Hope Have helped you !

|||See if the problem is dedicated to this entry, if yes, vote for it.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=271928

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks for your reply. But it gives the same problem with the date picker.

Date problem

Hi there !
Can anyone tell me how to convert 1900-01-01 00:00:00 into current date and current time. ?

I am just one the beginer of MSSQL and ASP. I have a table name annouce which work fine before i altered it. I added one new field named tdate whcih has datetype smalldatetime & have default value getdate().

When not provide the any value when inserting a new row, I got the error "Column name or number of supplied values does not match table definition. " which is true because the number of supplied value is 1 less then number of column. But then what does the default value meant for ?

When i provide NULL means( " ") then it automatically add the above mention date and time. ?

Can anyone put some light why it happend ? Shall, i have to put all the fields name except the new added field ? or i have to do something else.

With Thankswhen you got that error message, it was most likely because you did not specify a column list for the INSERT statement

let's say your table has five fields, and you add a sixth one

in order for the default getdate() value to work, you have to sayinsert
into annouce
( fld1, fld2, fld3, fld4, fld5 )
values
( 1, 2, 3, 4, 5 )this way the number of supplied values equals the number of columns you are inserting into

the column you did not mention in the insert statement is then given its default value

rudy
http://r937.com/sql

Date portion comparison of a datetime field

I have a datetime variable coming from my ASP.NET application that has
a time portion. I give my users the option to perform an equals,
greater than, less than, or between comparison. The trouble comes in
the way the application builds the criteria string. The WHERE clause
passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
What I want to do is only compare the date portion of start_dt to the
date portion of the passed in time. Manipulating the start_dt with the
built-in SQL functions isn't a problem, but altering the date passed in
from the ASP.NET would be a massive framework change in the app.
Is there any way to only compare the date portions of both the SQL
field and the passed in value?
Thanks.Create a stored procedure instead creating the statement dynamically.
create procedure dbo.usp_proc1
@.sd datetime
as
set nocount on
select c1, ..., cn
from table1
where
start_dt >= convert(char(8), @.sd, 112)
and start_dt < convert(char(8), dateadd(day, 1, @.sd), 112)
return @.@.error
go
AMB
"colinhumber" wrote:

> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You have to convert it to a the valid format you want to comapre it to, e.g.
(from BOL --> Convert)
CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formatting
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You need to consider using CONVERT fnc with RIGHT
or using DATEPART !
exemple :
right(convert(varchar, @.datetime, 112),10)
or
cast(datepart(hour,@.datetime) as varchar) + ':' +
cast(datepart(minute,@.datetime) as varchar) + ':' +
cast(datepart(second,@.datetime) as varchar)|||Thanks for the quick reply.
Doing the conversion on the start_dt isn't a problem, but as the value being
passed in from the app is in a dynamic string, performing some string
manipulation would be difficult as the string length could vary. The
frameworks as it stands uses dynamic criteria strings so changing that is no
t
an option. I was hoping there was a way to compare only the date portions
without too much manipulation.
"Jens Sü?meyer" wrote:

> You have to convert it to a the valid format you want to comapre it to, e.
g.
> (from BOL --> Convert)
> CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formattin
g
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitr
ag
> news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>
>|||DateDiff(day, 0, <AnyDate> ) strips off the time portion...
so
Where DateDiff(day, 0, start_dt) <Operator> DateDiff(day, 0, @.PassedInDate)
is one way to do this generically. (Happens to be really fast too.)
"colinhumber" wrote:

> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.

Date Part of the date time

I have data in a smalldatetime column but the dates are
mixed. Some in the '3/7/04 11:47:00 AM' form and some
in '3/7/04' (This is when I open the table from EM).
When I select from the Query Analyzer I get
'2004-01-08 13:35:00'
My question is How can I select only the date part of it
(Not the time part) to compare with another column ?
Thnaks for any help.......select convert (varchar, <date>,101) returns the date in mm/dd/yyyy format.
It comes out as a string. That can easily be compared against another date.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:099201c3db71$adcdd100$a601280a@.phx.gbl...

quote:

---

> I have data in a smalldatetime column but the dates are
> mixed. Some in the '3/7/04 11:47:00 AM' form and some
> in '3/7/04' (This is when I open the table from EM).
> When I select from the Query Analyzer I get
> '2004-01-08 13:35:00'
> My question is How can I select only the date part of it
> (Not the time part) to compare with another column ?
> Thnaks for any help.......

---

|||SELECT CONVERT(SMALLDATETIME, CONVERT(CHAR(8), column, 112)) FROM table
You can wrap this in a function for slightly cleaner code and slightly more
overhead. Depending on the size of your table, of course. I have a
function called dbo.getDayFloor() that handles this for me.
Note: I always convert to the smaller of the two datetime datatypes if I'm
only dealing with day boundaries and not concerned about time.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:099201c3db71$adcdd100$a601280a@.phx.gbl...

quote:

---

> I have data in a smalldatetime column but the dates are
> mixed. Some in the '3/7/04 11:47:00 AM' form and some
> in '3/7/04' (This is when I open the table from EM).
> When I select from the Query Analyzer I get
> '2004-01-08 13:35:00'
> My question is How can I select only the date part of it
> (Not the time part) to compare with another column ?
> Thnaks for any help.......

---

sql

Date Part of the date time

I have data in a smalldatetime column but the dates are
mixed. Some in the '3/7/04 11:47:00 AM' form and some
in '3/7/04' (This is when I open the table from EM).
When I select from the Query Analyzer I get
'2004-01-08 13:35:00'
My question is How can I select only the date part of it
(Not the time part) to compare with another column ?
Thnaks for any help.......select convert (varchar, <date>,101) returns the date in mm/dd/yyyy format.
It comes out as a string. That can easily be compared against another date.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:099201c3db71$adcdd100$a601280a@.phx.gbl...
> I have data in a smalldatetime column but the dates are
> mixed. Some in the '3/7/04 11:47:00 AM' form and some
> in '3/7/04' (This is when I open the table from EM).
> When I select from the Query Analyzer I get
> '2004-01-08 13:35:00'
> My question is How can I select only the date part of it
> (Not the time part) to compare with another column ?
> Thnaks for any help.......|||SELECT CONVERT(SMALLDATETIME, CONVERT(CHAR(8), column, 112)) FROM table
You can wrap this in a function for slightly cleaner code and slightly more
overhead. Depending on the size of your table, of course. I have a
function called dbo.getDayFloor() that handles this for me.
Note: I always convert to the smaller of the two datetime datatypes if I'm
only dealing with day boundaries and not concerned about time.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:099201c3db71$adcdd100$a601280a@.phx.gbl...
> I have data in a smalldatetime column but the dates are
> mixed. Some in the '3/7/04 11:47:00 AM' form and some
> in '3/7/04' (This is when I open the table from EM).
> When I select from the Query Analyzer I get
> '2004-01-08 13:35:00'
> My question is How can I select only the date part of it
> (Not the time part) to compare with another column ?
> Thnaks for any help.......

date part of getdate

i want to use the date part of the function getdate in my sql query.
the getdate function return the current date and time. i want only the date part.
how can i do it.You can use the CONVERT functions and specify a date format. However, it's a lot harder if you want to return the result as an ISO XML format...but if you're not worried about that then convert will work fine|||CONVERT(VARCHAR(10),GETDATE(),101)

date part of date time

SELECT ltrim(str(datepart(yyyy,getdate()))) +'-'+
replicate('0',2-len(ltrim(str(datepart(mm,getdate())))))+ltrim(str (datepart(mm,getdate())))+'-' +replicate('0',2-len(ltrim(str(datepart(dd,getdate())))))
+ltrim(str(datepart(dd,getdate())))

This is how i am getting datepart of datetime.Is there any other way to get the date and also time seperately..

Thanks.Convert(char(10), getdate(), 120)|||SELECT CONVERT(varchar(10),GetDate(),101), CONVERT(varchar(10),GetDate(),108)|||Beat you by one second, Kaiser! Nyah nyah nyah! Too slow! Ya snooze, ya looze! :p :p :p :p :p|||SELECT CONVERT(varchar(10),GetDate(),101), CONVERT(varchar(10),GetDate(),108)

Where should i know to use 101 and 108 in convert functions.I could get that from BOl...
Can u help me with that.

Thanks and sorry for asking such a silly question.|||I'm sorry i got that myself.

Thank u all.|||I'm sorry i got that myself.

Thank u all.

Congratulations! Because of forum a lot of people became so lazy but not you.|||Beat you by one second, Kaiser! Nyah nyah nyah! Too slow! Ya snooze, ya looze! :p :p :p :p :p

Yeah...see what happens when work gets in the way...

I'm giving out specs over the phone to a noob while I'm doing this...

And this ain't sqlteam...that's 1 minute increments

Damn...[sniped] anyway

And what's with 120?

bleeeeeach...

:D|||And what's with 120?Wah, wah, waaaaah!

120 is just the "junior grade" version of 121, my preferred style!

-PatP|||121? How pretentious. 120 shines in its simplicity and elegance.

Dude, 121 is SOOOOO last version.....

And what's with the varchar, eh?|||Personally I like 126..talk about pretentions...but that's just my mainframe db2 talking...

too bad they lost pos 4-6 of their milliseconds...gotta be the cpu's fault..right?

Figured he wanted 101 though..nice and pretty...

And geez...I need to speak with Karolyn or ms_sql_dba to straighten you out?

:D

I saw you suck'n up blind dude... :)

Varchar...force of habit...

we need more banter in the corral...

http://www.dbforums.com/showthread.php?p=3672844#post3672844|||Don't threaten to sick your dogs on me!

Like I'm gonna be scared of a "126"er...|||Dude, 121 is SOOOOO last version.....Oh, they used to say that about my Leisure Suit and silver tipped cowboy boots too!

-PatP|||Hold on to those. They're coming back.|||Hold on to those. They're coming back.

I hated the 70's..

What do you guys think about this:

http://www.dbforums.com/t996699.html

Date part of a dateTime value

Hi,
This is no function in SQL Server to get only the date part of a datetime
variable, sometime, and usually most of the time, I would like to have a
query to get the result of only some specific day's, and I am not sure what
could be the best way to do this query.
For example, if I want to get all the records of my table for only todays.
Thanks in advance for your advice on this.
FrankYou can get the Date part of the date time using the datepart function.
select Convert(nvarchar(25),getdate(),100 )
You can use the datediff function for retrieving the current day's
record
Select * from table where datediff(d,getdate(),datecolumn) = 0|||Hi,
SELECT * from YOurTable
Where Datefield >= convert(VARCHAR(8),getdate(),112)
Uses an ISO Date format, you could also Convert the Datefield Column, but
this wont need to be neccessary because of causing performance issues.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is no function in SQL Server to get only the date part of a datetime
> variable, sometime, and usually most of the time, I would like to have a
> query to get the result of only some specific day's, and I am not sure
> what
> could be the best way to do this query.
> For example, if I want to get all the records of my table for only todays.
> Thanks in advance for your advice on this.
> Frank
>|||Continuation to the above message from me ...
Sorry .. use this
select Convert(nvarchar(25),getdate(),101 )
you can also check books online for the different values instead of 101
for getting the required output|||Thanks, balacr,
The second datediff(d, getdate(), datecolumn) = 0 is exactly what I want.
B/R
Frank
<balacr@.gmail.com> wrote in message
news:1113806479.336151.291970@.l41g2000cwc.googlegroups.com...
> You can get the Date part of the date time using the datepart function.
> select Convert(nvarchar(25),getdate(),100 )
> You can use the datediff function for retrieving the current day's
> record
> Select * from table where datediff(d,getdate(),datecolumn) = 0
>|||Hi Frank,
"Frank" <wangping@.lucent.com> wrote in message
news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is no function in SQL Server to get only the date part of a datetime
> variable, sometime, and usually most of the time, I would like to have a
> query to get the result of only some specific day's, and I am not sure
> what
> could be the best way to do this query.
> For example, if I want to get all the records of my table for only todays.
> Thanks in advance for your advice on this.
Use Northwind
/* (1) */
select * from orders
where
orderdate >= CAST(CONVERT(char(8), getdate(), 112) AS DATETIME) and
orderdate < DATEADD(day, 1, CAST(CONVERT(char(8), getdate(), 112) AS
DATETIME))
/* (2) */
select * from orders
where
day(orderdate) = day(getdate()) and
month(orderdate) = month(getdate()) and
year(orderdate) = year(getdate())
/* (3) */
Select * from orders
where
datediff(day ,getdate() , orderdate) = 0

> Frank
HTH,
Andrea|||Thanks, Jens,
I also noticed that the "where datediff()" method could causing performance
issues, since I was told that if in the "where clause", there is some
functions on the column, then even there is an index on that column, the
database query engine will not use it.
That is something like this,
select * from myTable where dateDiff(day, myDateColumn, getdate()) =0,
Then even on "myDateColumn" there is an index, the SQL Server will not use
that index.
Am I right?
B/R
Frank
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eNqfyI%23QFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Hi,
> SELECT * from YOurTable
> Where Datefield >= convert(VARCHAR(8),getdate(),112)
> Uses an ISO Date format, you could also Convert the Datefield Column, but
> this wont need to be neccessary because of causing performance issues.
>
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
> news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
datetime
todays.
>|||Yeah you are, thats why i pointed out not to do something with the column
rather than doing this "static" Conversion (because it will be only
evaluated once.)
HTH, Jens Smeyer.
http:/www.sqlserver2005.de
--
"Frank" <wangping@.lucent.com> schrieb im Newsbeitrag
news:uKcZMQ%23QFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Thanks, Jens,
> I also noticed that the "where datediff()" method could causing
> performance
> issues, since I was told that if in the "where clause", there is some
> functions on the column, then even there is an index on that column, the
> database query engine will not use it.
> That is something like this,
> select * from myTable where dateDiff(day, myDateColumn, getdate()) =0,
> Then even on "myDateColumn" there is an index, the SQL Server will not use
> that index.
> Am I right?
> B/R
> Frank
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:eNqfyI%23QFHA.3704@.TK2MSFTNGP12.phx.gbl...
> datetime
> todays.
>|||Thanks, Andrea,
I would like to choose the first of your ideas, I think there maybe some
performance issues for the second and third ones.
B/R
Frank
"Andrea Benedetti" <abenedetti@.absistemi.it> wrote in message
news:%232XsyM%23QFHA.3076@.tk2msftngp13.phx.gbl...
> Hi Frank,
> "Frank" <wangping@.lucent.com> wrote in message
> news:%23Bu0Lt9QFHA.3868@.TK2MSFTNGP10.phx.gbl...
datetime
todays.
> Use Northwind
> /* (1) */
> select * from orders
> where
> orderdate >= CAST(CONVERT(char(8), getdate(), 112) AS DATETIME) and
> orderdate < DATEADD(day, 1, CAST(CONVERT(char(8), getdate(), 112) AS
> DATETIME))
> /* (2) */
> select * from orders
> where
> day(orderdate) = day(getdate()) and
> month(orderdate) = month(getdate()) and
> year(orderdate) = year(getdate())
> /* (3) */
> Select * from orders
> where
> datediff(day ,getdate() , orderdate) = 0
>
> HTH,
> Andrea
>sql

Date Parameter without time

In the Report Parameters dialog I define my Parameter and set
Data Type to Datetime.
When I run report I get 22/2/2005 12:00:00 AM in parameter box on
top of report (parameters section of report), which is correct.
But I want to format this to appear and make input like 22/2/2005
ie.(dd/MM/yyyy) and without time part.
Thanks in advanceI use a string for all of my dates and it works fine. In my dataset or
SP that is called, I simply format the date and add 00:00:00 to the
start date, and 23:59:59 to the end date.|||Or you could use a custom code to return a short date string for that
field.
call Code.FormatDate(Fields!data.Value)
Function FormatDate(ByVal date as Object) as String
If IsDate(data) Then
Return CDate(date).ToShortDateString()
Else
Return String.Empty
End If
End Funtion

date parameter input question

is there a way that once a user enters a date parameter in that i can attach
a time to the end of the date.
eg.
user enters 11/11/05 in the parameter field. i want the date submitted to
the query to be 11/11/05 12:00:00 AM without them having to enter the time.In RS 2000 it automatically puts those times in. I had a case where I needed
it to be from 7 am to 7 am. So I set the parameter to text. Then you map the
query parameter to an expression.
= Parameters!MyParam.Value & " 7:00"
In 2005 there is a calendar control. If no time is selected it doesn't show
a time. If you have it as a date/time you can also use expressions to strip
the time portion. Look at VB date/time formating.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.matt.com> wrote in message
news:uX7uf2t5FHA.3276@.TK2MSFTNGP10.phx.gbl...
> is there a way that once a user enters a date parameter in that i can
> attach
> a time to the end of the date.
> eg.
> user enters 11/11/05 in the parameter field. i want the date submitted to
> the query to be 11/11/05 12:00:00 AM without them having to enter the
> time.
>

date parameter in reporting services

Hi
I need to have the following result format into a date time parameter with
the current date
='1/07/2005 5:00:00 AM'
so when i schudule this report to run it will look at the current date at 5a
m.
Thankyou
toddTango
Can you eloborate what you expect
Regards
R.D
"Tango" wrote:

> Hi
> I need to have the following result format into a date time parameter with
> the current date
> ='1/07/2005 5:00:00 AM'
> so when i schudule this report to run it will look at the current date at
5am.
> Thankyou
> todd
>|||my expectation is that when i schedule this report to run it looks at the da
y
at 5am & reports back. All records in my database have a date of at 5am.
so the parameter output should be in datetimeformat showing d/MM/yy h:mm:ss
tt
Thanks
Todd
"R.D" wrote:
> Tango
> Can you eloborate what you expect
> Regards
> R.D
> "Tango" wrote:
>sql

Date parameter

My report has a StartDate and a EndDate parameters. I use the datetime
parameter type, and the input textbox shows both the date and time portions.
How to show only the date portion in the parameter textbox? By the way, is it
possible to have something like a datetime picker to get the date parameter?
The textbox control is not user-friendly.When you specify the parameter as date time, both with display although the
user may enter only the date or the time, with the remaing defaulting to the
min...
Some folks will use a string parameter instead...
You could populate the dates from a table in the database... But using a
date picker, you'd have to put an html page in front of the report and
include the date picker in that, then call the report from a web service.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Matthew Chow" <Matthew Chow@.discussions.microsoft.com> wrote in message
news:025C4D51-7562-4532-ACE0-C5C39DF55D3E@.microsoft.com...
> My report has a StartDate and a EndDate parameters. I use the datetime
> parameter type, and the input textbox shows both the date and time
portions.
> How to show only the date portion in the parameter textbox? By the way, is
it
> possible to have something like a datetime picker to get the date
parameter?
> The textbox control is not user-friendly.|||On Mon, 24 Jan 2005 07:47:02 -0800, Matthew Chow <Matthew
Chow@.discussions.microsoft.com> wrote:
>My report has a StartDate and a EndDate parameters. I use the datetime
>parameter type, and the input textbox shows both the date and time portions.
>How to show only the date portion in the parameter textbox? By the way, is it
>possible to have something like a datetime picker to get the date parameter?
>The textbox control is not user-friendly.
DatePicker is rather "too much" to ask :))
As of date parameters, to avoid confusion among end-users with all
that extra time info, just use a string parameter with a prompt like
"Start Date (mm/dd/yyyy):" The proposed format should match the
locale of your server, so even foreign users (if you have any) won't
make mistakes. Then in your query use somethig like this:
WHERE MyTable.MyDate >= CONVERT(datetime, @.pStartDate)
If they enter something invalid, they will get a SQL error, like
"unable to convert to a datetime". This approach is less efficient,
because user's input is being validated on the database level, not on
the web page level, but at the same time provides a much cleaner way
for the users by avoiding confusion. We are now using this everywhere.|||Thanks for helping.sql

Date Parameter

My report contains a date-time parameter. When the report is run it displays
both the date and time portions in my date drop down box e.g. 01/01/2004
12:00:00 AM.
How would I change this to only display the 01/01/2004 ?
Note: I want to maintain system localized date format.Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> My report contains a date-time parameter. When the report is run it
displays
> both the date and time portions in my date drop down box e.g. 01/01/2004
> 12:00:00 AM.
> How would I change this to only display the 01/01/2004 ?
> Note: I want to maintain system localized date format.|||Hi Ravi:
Where should I place it?
"Ravi Mumulla (Microsoft)" wrote:
> Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > My report contains a date-time parameter. When the report is run it
> displays
> > both the date and time portions in my date drop down box e.g. 01/01/2004
> > 12:00:00 AM.
> >
> > How would I change this to only display the 01/01/2004 ?
> >
> > Note: I want to maintain system localized date format.
>
>|||Click on the control you want to format (the particular field in the table
control, or a texbox for example) and go to properties, format, select
expression and put this in when the expression box comes up.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> Hi Ravi:
> Where should I place it?
> "Ravi Mumulla (Microsoft)" wrote:
> > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > My report contains a date-time parameter. When the report is run it
> > displays
> > > both the date and time portions in my date drop down box e.g.
01/01/2004
> > > 12:00:00 AM.
> > >
> > > How would I change this to only display the 01/01/2004 ?
> > >
> > > Note: I want to maintain system localized date format.
> >
> >
> >|||I am attempting to chnage the date format in the parameter control bar... as
far as I can see there is no properties selection to choose from.
"Bruce L-C [MVP]" wrote:
> Click on the control you want to format (the particular field in the table
> control, or a texbox for example) and go to properties, format, select
> expression and put this in when the expression box comes up.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > Hi Ravi:
> >
> > Where should I place it?
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > My report contains a date-time parameter. When the report is run it
> > > displays
> > > > both the date and time portions in my date drop down box e.g.
> 01/01/2004
> > > > 12:00:00 AM.
> > > >
> > > > How would I change this to only display the 01/01/2004 ?
> > > >
> > > > Note: I want to maintain system localized date format.
> > >
> > >
> > >
>
>|||Ahh, sorry. Everybody answering you was answering with regards to the
report. The parameter tool bar can not be modified as far as I know. You
have the option of creating your own web page and then integrate into RS
using URL control or web services.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:90B96A06-51F8-4E7C-BBD3-BBD671BA5887@.microsoft.com...
> I am attempting to chnage the date format in the parameter control bar...
as
> far as I can see there is no properties selection to choose from.
> "Bruce L-C [MVP]" wrote:
> > Click on the control you want to format (the particular field in the
table
> > control, or a texbox for example) and go to properties, format, select
> > expression and put this in when the expression box comes up.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > > Hi Ravi:
> > >
> > > Where should I place it?
> > >
> > > "Ravi Mumulla (Microsoft)" wrote:
> > >
> > > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > > >
> > > > --
> > > > Ravi Mumulla (Microsoft)
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > > My report contains a date-time parameter. When the report is run
it
> > > > displays
> > > > > both the date and time portions in my date drop down box e.g.
> > 01/01/2004
> > > > > 12:00:00 AM.
> > > > >
> > > > > How would I change this to only display the 01/01/2004 ?
> > > > >
> > > > > Note: I want to maintain system localized date format.
> > > >
> > > >
> > > >
> >
> >
> >|||Crystal Reports has the option to convert all date-time to dates.
This allows you to bring up all records which occured between a certain date
range irrespective of the time.
Having the time portion in RS causes problems. If I want to pull up all
records which occured from 1 jan 2004 to 1 jan 2004. RS views this as being 1
jan 2004 12 am to 1 jan 2004 12 am. It therefore leaves out the other 23:59
hours on Jan 1.
Are you saying this is not possible in RS without a custom webpage?
"Bruce L-C [MVP]" wrote:
> Ahh, sorry. Everybody answering you was answering with regards to the
> report. The parameter tool bar can not be modified as far as I know. You
> have the option of creating your own web page and then integrate into RS
> using URL control or web services.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:90B96A06-51F8-4E7C-BBD3-BBD671BA5887@.microsoft.com...
> > I am attempting to chnage the date format in the parameter control bar...
> as
> > far as I can see there is no properties selection to choose from.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Click on the control you want to format (the particular field in the
> table
> > > control, or a texbox for example) and go to properties, format, select
> > > expression and put this in when the expression box comes up.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > > > Hi Ravi:
> > > >
> > > > Where should I place it?
> > > >
> > > > "Ravi Mumulla (Microsoft)" wrote:
> > > >
> > > > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > > > >
> > > > > --
> > > > > Ravi Mumulla (Microsoft)
> > > > > SQL Server Reporting Services
> > > > >
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > > > My report contains a date-time parameter. When the report is run
> it
> > > > > displays
> > > > > > both the date and time portions in my date drop down box e.g.
> > > 01/01/2004
> > > > > > 12:00:00 AM.
> > > > > >
> > > > > > How would I change this to only display the 01/01/2004 ?
> > > > > >
> > > > > > Note: I want to maintain system localized date format.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||If you are going against SQL Server database it only has a datetime data
type. It does not have a separate date and time datatypes. I do all my
reports >= fromdate and < todate. So if you want a day's worth of data it is
between 1/1/04 00:00:00 and 1/2/04 00:00:00
One point, you can always ignore the time portion. The report parameter and
the query parameter are two different thing and you can map the query
parameter to an expression which takes the report parameter and strips the
time part of it. The downside of this is that the user will see the time in
the parameter bar.
One other point, you can have a text parameter instead of the date
parameter. The user puts in the date as text and then you do whatever you
want with it during the assignment to the query parameter.
The portal that RS provides in version 1 is limited with how you can
customize it. Hopefully we will see some improvements. For instance it sure
would be nice to format the parameters (as you mentioned) or add custom
error checking to it. Or a datepicker would be nice. No special knowledge of
what the improvements would be. Just that I do agree with you that more
control and options with it would be nice.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:53A33C93-0483-4305-BBF2-988B618FFF2D@.microsoft.com...
> Crystal Reports has the option to convert all date-time to dates.
> This allows you to bring up all records which occured between a certain
date
> range irrespective of the time.
> Having the time portion in RS causes problems. If I want to pull up all
> records which occured from 1 jan 2004 to 1 jan 2004. RS views this as
being 1
> jan 2004 12 am to 1 jan 2004 12 am. It therefore leaves out the other
23:59
> hours on Jan 1.
> Are you saying this is not possible in RS without a custom webpage?
> "Bruce L-C [MVP]" wrote:
> > Ahh, sorry. Everybody answering you was answering with regards to the
> > report. The parameter tool bar can not be modified as far as I know. You
> > have the option of creating your own web page and then integrate into RS
> > using URL control or web services.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:90B96A06-51F8-4E7C-BBD3-BBD671BA5887@.microsoft.com...
> > > I am attempting to chnage the date format in the parameter control
bar...
> > as
> > > far as I can see there is no properties selection to choose from.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Click on the control you want to format (the particular field in the
> > table
> > > > control, or a texbox for example) and go to properties, format,
select
> > > > expression and put this in when the expression box comes up.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > > > > Hi Ravi:
> > > > >
> > > > > Where should I place it?
> > > > >
> > > > > "Ravi Mumulla (Microsoft)" wrote:
> > > > >
> > > > > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > > > > >
> > > > > > --
> > > > > > Ravi Mumulla (Microsoft)
> > > > > > SQL Server Reporting Services
> > > > > >
> > > > > > This posting is provided "AS IS" with no warranties, and confers
no
> > > > rights.
> > > > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > > > > My report contains a date-time parameter. When the report is
run
> > it
> > > > > > displays
> > > > > > > both the date and time portions in my date drop down box e.g.
> > > > 01/01/2004
> > > > > > > 12:00:00 AM.
> > > > > > >
> > > > > > > How would I change this to only display the 01/01/2004 ?
> > > > > > >
> > > > > > > Note: I want to maintain system localized date format.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||I know this is an old thread but in my searches for an answer.....
Anyway I figured out a solution:
=Today.ToShortDateString()
or if you need to add days:
=Today.AddDays(-2).ToShortDateString()
Hope this helps
"Bruce L-C [MVP]" wrote:
> If you are going against SQL Server database it only has a datetime data
> type. It does not have a separate date and time datatypes. I do all my
> reports >= fromdate and < todate. So if you want a day's worth of data it is
> between 1/1/04 00:00:00 and 1/2/04 00:00:00
> One point, you can always ignore the time portion. The report parameter and
> the query parameter are two different thing and you can map the query
> parameter to an expression which takes the report parameter and strips the
> time part of it. The downside of this is that the user will see the time in
> the parameter bar.
> One other point, you can have a text parameter instead of the date
> parameter. The user puts in the date as text and then you do whatever you
> want with it during the assignment to the query parameter.
> The portal that RS provides in version 1 is limited with how you can
> customize it. Hopefully we will see some improvements. For instance it sure
> would be nice to format the parameters (as you mentioned) or add custom
> error checking to it. Or a datepicker would be nice. No special knowledge of
> what the improvements would be. Just that I do agree with you that more
> control and options with it would be nice.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:53A33C93-0483-4305-BBF2-988B618FFF2D@.microsoft.com...
> > Crystal Reports has the option to convert all date-time to dates.
> >
> > This allows you to bring up all records which occured between a certain
> date
> > range irrespective of the time.
> >
> > Having the time portion in RS causes problems. If I want to pull up all
> > records which occured from 1 jan 2004 to 1 jan 2004. RS views this as
> being 1
> > jan 2004 12 am to 1 jan 2004 12 am. It therefore leaves out the other
> 23:59
> > hours on Jan 1.
> >
> > Are you saying this is not possible in RS without a custom webpage?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Ahh, sorry. Everybody answering you was answering with regards to the
> > > report. The parameter tool bar can not be modified as far as I know. You
> > > have the option of creating your own web page and then integrate into RS
> > > using URL control or web services.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:90B96A06-51F8-4E7C-BBD3-BBD671BA5887@.microsoft.com...
> > > > I am attempting to chnage the date format in the parameter control
> bar...
> > > as
> > > > far as I can see there is no properties selection to choose from.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > Click on the control you want to format (the particular field in the
> > > table
> > > > > control, or a texbox for example) and go to properties, format,
> select
> > > > > expression and put this in when the expression box comes up.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > > news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > > > > > Hi Ravi:
> > > > > >
> > > > > > Where should I place it?
> > > > > >
> > > > > > "Ravi Mumulla (Microsoft)" wrote:
> > > > > >
> > > > > > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > > > > > >
> > > > > > > --
> > > > > > > Ravi Mumulla (Microsoft)
> > > > > > > SQL Server Reporting Services
> > > > > > >
> > > > > > > This posting is provided "AS IS" with no warranties, and confers
> no
> > > > > rights.
> > > > > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > > > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > > > > > My report contains a date-time parameter. When the report is
> run
> > > it
> > > > > > > displays
> > > > > > > > both the date and time portions in my date drop down box e.g.
> > > > > 01/01/2004
> > > > > > > > 12:00:00 AM.
> > > > > > > >
> > > > > > > > How would I change this to only display the 01/01/2004 ?
> > > > > > > >
> > > > > > > > Note: I want to maintain system localized date format.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>sql