problem with inserting date in a datetime field

Hi, I have a problem when I insert a date in a datetime field in a MSSQLServer.
That's my problem:
if the server is in english version, I have to insert date with this code:

DateTime.Today.ToString("MM/dd/yyyy")

instead if the server is in italian version, I have to insert date with this code:

DateTime.Today.ToString("dd/MM/yyyy")

Is there a way to insert a date in standard way, without knowing the server version?

bye and thanks in advanceI usually find that yyyy-mm-dd does the trick. But I can't swear it will always work. Actually what am I talking about, you should be using params and this won't be a problem.|||What if you insert the date in datetime format instead of converting it to string? Then maybe, just maybe sql server will recognize the format.|||>> What if you insert the date in datetime format instead of converting it to string? Then maybe, just maybe sql server will recognize the format.

You're on the road to SQL hell if you do that. What culture is your client, what culture is the database? Use params, there is hardly *ever* a reason not too almost always a problem with an alternative - unless you've written very well thought out DB classes.

Problem with insert Time to database

Dear all,
I have insert the time to my database but it appear also the date by default.
This is my code in C# :
DateTime date = DateTime.Now;
int hour = date.Hour;
int minute = date.Minute;
int second = date.Second;
string requestedTime = hour+":"+minute+":"+second;
string query = "INSERT INTO workorder([timeRequest]) VALUES("'"+requestTime+"'");
in my database , the column timeRequest appear :1/1/1900 11:59:05 AM
I dont want the date by default to appear, i want only the time like : 11:59:05 AM in my database,
Anyone can help me?
Best Regards,
Moniphal

Use parameterized command to insert data to database. By example:

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO workorder(timeRequest) VALUES (@.TimeRequest)";
cmd.CommandType = CommandType.Text
cmd.Parameters.Add(new SqlParameter("@.TimeRequest",myDateTime));
cmd.ExecuteNonQuery();

Problem with GETDATE()

Hello All,

I have a problem as follows

if i execute SELECT GETDATE() statement multiple times in a single run it returns me the same datetime without any difference in even milliseconds.

I am unable to figure out what is wrong. I am assuming that whenever executed in a transaction it will give the same result.

could anybody let me know what is correct. Thanks for your help in advance.

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

even then i get the same date.

What are you trying to achieve? The amount of time it takes to run multiple Select GetDate() is very minor. We would be able to help you better if we knew what your goal was.

|||

Hi, mate

I just executed:

SELECTGETDATE()SELECT *FROM Table1SELECTGETDATE()

and the the two dates was different. (Table1 has 120 000 rows)

This means that the query is executing too fast (in less than a millisecond) and that is why you receive the same results.

|||

yeah... if u execute query select getdate() several times one after another u cant understand the difference of milliseconds. don't worry...

|||

Hi Diamsorn,

Thanks for the reply. but all i am trying to do was i have a history table and i have included modified date as a part of primary key and when i am trying to update my main table i am inserting a record into history table. eventhough i am doing it in different time system says it is a violation of primary key.

For eg. Table1 is having below columns

Column1 Column2 Column3 and Suppose Primary key is composite key of column1 and column2

I have HistoryTable having columns

Column1 Column2 modifieddate and Suppose Primary key is composite key of Column1,Column2 and Modifieddate. but when i am trying to update the table1, and though trigger i am capturing getdate() to fill modifieddate, then as it is not different it is giving error.

how to overcome this problem?

Gneralproblem

|||

Which table is giving the primary key violation error? Table1 or HistoryTable.

What is your purpose of having a composite primary key in your history table of column1, column2, and modified date?

I would move away from using a trigger to insert into your history table, and do your update/insert inside of a transaction in a stored procedure. Triggers are a maintenance nightmare and I avoid them personally at all costs.

|||

Hi Diamsorn,

History table is giving me error. As i have to update the same record in Table1 and track the changes in HistoryTable. As my operation is so fast and as it is caputring same date it is giving primary key violation.

I would appreciate if any way to handle this problem using Triggers.

Thanks,

GeneralProblem

Problem with GETDATE in SQL Stored Procedure

hi all,

i am using a stored procedure where i am using GETDATE to give default value to a field ( @.effectivedate as Datetime = GETDATE)

i am making the SP call in my code .

Dim cmd As System.Data.Common.DbCommand = db.GetStoredProcCommand("sel_TemplateData")

db.AddInParameter(cmd, "@.TemplateID", DbType.Int32, Convert.ToInt32(_templateId))

db.AddInParameter(cmd, "@.State", DbType.String, mrmParams("State").ToString())

db.AddInParameter(cmd, "@.SectionCode", DbType.String, mrmParams("SectionCode").ToString())

Dim ds As DataSet = db.ExecuteDataSet(cmd)

should i need to pass this as a parameter along with other parameter as below ? will it be defaultly taken.

when i try to add this parameter an error is thrown " cannot convert string to datetime .

is the syntax for GETDATE correct.

thanks in Advance

Since Getdate() is the non-deterministic function you can’t assign this function as your default value of the SP parameter.

Try to use the following approach to pick up the current date when there is no explicit value passed for datetime valued parameters.

C

Code Snippet

raete Procedure TestDateParam

(

@.Date as datetime= '1900-01-01'

)

as

Begin

Set @.Date = Case When @.Date = '1900-01-01' Then Getdate() Else @.Date End;

Select @.Date Date

End

Go

Exec TestDateParam --it will use the current date

Exec TestDateParam '1/1/2007' --it will use the passed date value

|||

Could you instead use NULL as your default value? If that is possible then you would not need the set statement but could use the ISNULL or COALESCE function -- something like:

ISNULL(@.Date, getdate())

or

COALESCE(@.Date, getdate())

within the body of your stored procedure

|||

If you are wanting to make @.EffectiveDate an optional parameter for the procedure, I suggest using Kent's suggestion of setting the optional value equal to NULL. It is cleaner than presuming a redefined date means none supplied...

|||In addition to Kent and Arnie you could consider using the syntax:

Create procedure someproc
(
@.SomeDate DATETIME = NULL
)
AS

SELECT
(...Something)
WHERE YourColumn = @.SomeDate OR @.SomeDate IS NULL

But this highly depends on your needs in the logic of the stored procedure.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

problem with functions and datetime parameters!

Hi all.
i've written a portion of sql code with a dtetime parameter that run very
very fast on a sql window, but when i create a function with the same code
the execution time is extremely long!
to recreate the same speed i found that i must declare a local variable
inside the scope of the function and then assign the variable passed to the
function.
does anyone had the same problem? there is a solution to this bug?
this is my code...
regards,
stefano
create function kp.getQuotaHWM (@.dd1 datetime)
returns float
as
begin
declare @.dd datetime
set @.dd = @.dd1
return (
... code of the function
)
endOn Mon, 8 Aug 2005 11:09:41 +0200, stefano wrote:

>Hi all.
>i've written a portion of sql code with a dtetime parameter that run very
>very fast on a sql window, but when i create a function with the same code
>the execution time is extremely long!
>to recreate the same speed i found that i must declare a local variable
>inside the scope of the function and then assign the variable passed to the
>function.
>does anyone had the same problem? there is a solution to this bug?
Hi stefano,
This is a known issue. Not exactly a bug - more an unwanted side effect
of a wanted feature.
Search this group (or the internet) for "parameter sniffing" to find
alll the details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo.
many thanks for your informations.
regards, stefano
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:q5iff1pndv10nhhr32c650b9vb5evi0fkb@.
4ax.com...
> On Mon, 8 Aug 2005 11:09:41 +0200, stefano wrote:
>
> Hi stefano,
> This is a known issue. Not exactly a bug - more an unwanted side effect
> of a wanted feature.
> Search this group (or the internet) for "parameter sniffing" to find
> alll the details.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Problem with format of DateTime parameter

I am in Australia.
Our machines have an Australian/English locale which as a date order of
Day/Month/Year.
I am using SQL RS 2005.
When I add a parameter of type DateTime and then preview the report a
strange thing happens.
* Use an Australian machine locale
* Create a dataset with no SQL. The dataset is just
SELECT @.MyDateParm
* Create a simple layout that uses this dataset
* Preview the report. You should get the nice date selection dialog.
* Select 1st December 2005
* View the report - It switches magically to 12th January 2005
* View again - it switches back etc..
Now what is going on here?
(Professor Julius Sumner Miller)
(I know my likely solution is to move to the USA - or at least change
my locale :)Has anyone ever encountered this problem?
I think it is a bug in RS2005.
Does anyone know how I could report this?
Thanks
RBot wrote:
> I am in Australia.
> Our machines have an Australian/English locale which as a date order of
> Day/Month/Year.
> I am using SQL RS 2005.
> When I add a parameter of type DateTime and then preview the report a
> strange thing happens.
> * Use an Australian machine locale
> * Create a dataset with no SQL. The dataset is just
> SELECT @.MyDateParm
> * Create a simple layout that uses this dataset
> * Preview the report. You should get the nice date selection dialog.
> * Select 1st December 2005
> * View the report - It switches magically to 12th January 2005
> * View again - it switches back etc..
> Now what is going on here?
> (Professor Julius Sumner Miller)
> (I know my likely solution is to move to the USA - or at least change
> my locale :)|||Well there are other people having problems with Date type parameters:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=150&messageid=286639
but where should I report this so it can be fixed?

problem with datetime value as parameter value

Hi all,
I created a stored proc that has input datetime parameters (begindate and enddate), I tried the command: exec storedproc '20060320' in the query command part in Microsoft SQL Server Management Studio and it works but when I try to create a report dataset using the stored proc and execute it using value 20060320 it does not work. I even try using '20060320' and "20060320" as the value and it did not work also. I received the following error:
TITLE: Microsoft Report Designer

An error occurred while executing the query.
Failed to convert parameter value from a String to a DateTime.

ADDITIONAL INFORMATION:
Failed to convert parameter value from a String to a DateTime. (System.Data)

String was not recognized as a valid DateTime. (mscorlib)

BUTTONS:
OK

Anyone have any idea on how can I solve it or go about it? Thanks in advance.
Daren
Try using the format: "mm/dd/yyyy", so it would be: 03/20/2006.|||Thanks Deepak,
This solved my problem.
Daren

problem with DateTime picker

Hello.
I've just created a new report and I added a prameter of type "DateTime".
I didn't even used it in the query.
Whan I preview the report I have the small calendar Icon.
after I pick a time from the picker And press "View Report"
I get the error:
"the value provided for the report parameter P1 is not valid for its type"

I'm using RS 2005.

Any body know whats the problem?

Thanks in advance,
Roy.

Does this happen for any DateTime you pick, or only certain ones?

Can you type in a Date, say, "1/1/2005" and successfully render the report?

Does the same thing happen when you publish the report and view it in Report Manager?

|||Hi Mike.

I think I know whats the problem.
Whan I run it an my local Pc its take the date in a diffrent format.
Whan I choose 1 in february I get in the box 2/1/2007.
instead of 1/2/2007 like it should be since I'm with hebrew settings.
So the problem is that if I choose 13 in february I get it as 2/13/2007 whice give the error since there is no 13 month!
Any way, when I deploy it to the server It was Ok from some reason.

So Its look like a little bug of yours.

Thank for the replay,
Roy.

problem with dateTime picker

Hello.
I've just created a new report and I added a prameter of type
"DateTime".
I didn't even used it in the query.
Whan I preview the report I have the small calendar Icon.
after I pick a time from the picker and press "View Report"
I get the error:
"the value provided for the report parameter P1 is not valid for its
type"
I'm using RS 2005.
Any body know whats the problem?
Thanks in advance,
Roy.On 15 Feb, 08:30, "nicknack" <roezo...@.gmail.com> wrote:
> Hello.
> I've just created a new report and I added a prameter of type
> "DateTime".
> I didn't even used it in the query.
> Whan I preview the report I have the small calendar Icon.
> after I pick a time from the picker and press "View Report"
> I get the error:
> "the value provided for the report parameter P1 is not valid for its
> type"
> I'm using RS 2005.
> Any body know whats the problem?
> Thanks in advance,
> Roy.
Hi Roy I had this too and it was the date format that the picker was
sending to the dataset query.
I got around it by formatting the datepicker value to be the same as
the sql table date format ie
I hope it works out for you.
SELECT loaddate ,*
FROM tbl_T
WHERE convert(varchar(12),tbl_T.loaddate,103) LIKE
convert(varchar(12), (@.LoadDate),103)|||Hi Caseywill,
Thanks, I also discover that I have a problem with the format.
Whan I choose 1/2/07 which should be the 1 in february I get in the
textbox 2/1/2007.
So when I choose 13/2/2007 which should be the 13 in february I get
2/13/2007 which give the error since it looks for the 13 month (I have
hebrew date settings).
I don't know how to fix it since the datepicker doesn't choose its
date from a query.
Any way, When I deploy it It seeems to be ok and only on local pc it
give me some problems.
Thanks for your replay.
caseywill67@.googlemail.com =D7=9B=D7=AA=D7=91:
> On 15 Feb, 08:30, "nicknack" <roezo...@.gmail.com> wrote:
> > Hello.
> > I've just created a new report and I added a prameter of type
> > "DateTime".
> > I didn't even used it in the query.
> > Whan I preview the report I have the small calendar Icon.
> > after I pick a time from the picker and press "View Report"
> > I get the error:
> > "the value provided for the report parameter P1 is not valid for its
> > type"
> >
> > I'm using RS 2005.
> >
> > Any body know whats the problem?
> >
> > Thanks in advance,
> > Roy.
> Hi Roy I had this too and it was the date format that the picker was
> sending to the dataset query.
> I got around it by formatting the datepicker value to be the same as
> the sql table date format ie
> I hope it works out for you.
> SELECT loaddate ,*
> FROM tbl_T
> WHERE convert(varchar(12),tbl_T.loaddate,103) LIKE
> convert(varchar(12), (@.LoadDate),103)

Problem with datetime expression

I have the following expression in a textbox in a table based on a dataset:
=IIF(Fields!Opened.Value="No","No",Format(Fields!Opened.Value,"yyyy-MM-dd
HH:mm:ss"))
The result I recieve when there is supposed to be a date is the date
mask (yyyy-MM-dd HH:mm:ss) instead of the actual value of the date.
Any ideas?
Best regards,
Peter!On Dec 7, 7:17 am, Peter Larsson <scape...@.hotmail.com> wrote:
> I have the following expression in a textbox in a table based on a dataset:
> =IIF(Fields!Opened.Value="No","No",Format(Fields!Opened.Value,"yyyy-MM-dd
> HH:mm:ss"))
> The result I recieve when there is supposed to be a date is the date
> mask (yyyy-MM-dd HH:mm:ss) instead of the actual value of the date.
> Any ideas?
> Best regards,
> Peter!
I assume that Fields!Opened.Value is a string value (because you are
using it twice there) that either contains the text "No" or a Date.
Try wrapping the second Value in a CDate() function to force a
conversion to DateTime. If the Format command is fed a value that it
can't convert, it returns the formatting string, not the value.
= IIF( Fields!Opened.Value = "No", "No", Format( CDate(Fields!
Opened.Value), "yyyy-MM-dd HH:mm:ss") )
-- Scott|||Orne wrote:
> On Dec 7, 7:17 am, Peter Larsson <scape...@.hotmail.com> wrote:
>> I have the following expression in a textbox in a table based on a dataset:
>> =IIF(Fields!Opened.Value="No","No",Format(Fields!Opened.Value,"yyyy-MM-dd
>> HH:mm:ss"))
>> The result I recieve when there is supposed to be a date is the date
>> mask (yyyy-MM-dd HH:mm:ss) instead of the actual value of the date.
>> Any ideas?
>> Best regards,
>> Peter!
> I assume that Fields!Opened.Value is a string value (because you are
> using it twice there) that either contains the text "No" or a Date.
> Try wrapping the second Value in a CDate() function to force a
> conversion to DateTime. If the Format command is fed a value that it
> can't convert, it returns the formatting string, not the value.
> = IIF( Fields!Opened.Value = "No", "No", Format( CDate(Fields!
> Opened.Value), "yyyy-MM-dd HH:mm:ss") )
> -- Scott
Hi Scott!
Thanks for the tip, now the date works but I get the #Error on the When
the value contains "No".
/Peter|||On Dec 7, 11:05 am, Peter Larsson <scape...@.hotmail.com> wrote:
> Orne wrote:
> > On Dec 7, 7:17 am, Peter Larsson <scape...@.hotmail.com> wrote:
> >> I have the following expression in a textbox in a table based on a dataset:
> >> =IIF(Fields!Opened.Value="No","No",Format(Fields!Opened.Value,"yyyy-MM-dd
> >> HH:mm:ss"))
> >> The result I recieve when there is supposed to be a date is the date
> >> mask (yyyy-MM-dd HH:mm:ss) instead of the actual value of the date.
> >> Any ideas?
> >> Best regards,
> >> Peter!
> > I assume that Fields!Opened.Value is a string value (because you are
> > using it twice there) that either contains the text "No" or a Date.
> > Try wrapping the second Value in a CDate() function to force a
> > conversion to DateTime. If the Format command is fed a value that it
> > can't convert, it returns the formatting string, not the value.
> > = IIF( Fields!Opened.Value = "No", "No", Format( CDate(Fields!
> > Opened.Value), "yyyy-MM-dd HH:mm:ss") )
> > -- Scott
> Hi Scott!
> Thanks for the tip, now the date works but I get the #Error on the When
> the value contains "No".
> /Peter- Hide quoted text -
> - Show quoted text -
Ok, just made some test data. I think that the second half of the IIF
statement is still being evaluated, and for those rows where the value
is "No", the CDate is failing, therefore the whole IIF is failing.
So, before we do the CDate, we have to check again if the value is a
non-convertable date, then if it is not convertable, set it to
Nothing. CDate(Nothing) is still Nothing, so then the second half of
the IIF would succeed.
The following works the way I think you want it to:
=IIF( Fields!Opened.Value = "No", "No", Format( CDate( IIF(Fields!
Opened.Value = "No", Nothing, Fields!Opened.Value) ), "yyyy-MM-dd
HH:mm:ss" ) )
-- Scott

problem with datetime datatype..

hi! can anybody pls. help me...is it posible for my 'date' column with datetime datatype to contain date only..without the date? any inputs will be greatly appreciated!!if you are talking about microsoft sql server, the answer is no, you cannot have only a date without a time

however, you can have a date with a midnight time, e.g. 2006-05-29 00:00:00

use dateadd(d,datediff(d,0,getdate()),0)

moving thread to microsoft sql server forum (if you're using some other database, please be sure to mention it)

Problem with DateTime column

Hi All,
I faced this strange problem with sql server.
I have datetime column and storing value from asp.net. If the user doesnt enter any date, then we dont want any value to be stored in the database. But when we checked SQL Server, it default takes this value "01/01/1900". When users clicks on edit button, this value is fetched from database and stored in front end.
I dont want this value to be stored in database if i didnt provide value. But in 1 particular page, there are 5 different date columns. I am not sure about how to go ahead. I tried the following way, but again ended up with same problem.
insert into tablename......values (..., txtFromDate.text, txtToDate.text,...).
Since value is not there, '' is sent to database and above said value is stored. i want NULL to be stored in teh database when user didnt specify any value.
i didnt encounter this problem in Oracle.

appreciate your reply.

In the table design window for this table put a check in the column for allow nulls for the datetime field and make sure that you have not set the field with a default value. Also validate the textbox before data insertion or update like so.
if(txtFromDate.Text.Trim(' ') != "")
(
Place your database insertion code.
}
This will make sure that the textbox actually has something in it before you insert the value. Also you should have to convert the value of the textbox to a datetime before the data is inserted like this.
yadayadayada = Convert.ToDateTime(txtFromDate.Text);
I hope that this helps.

|||Check ifthisarticle helps.
|||

hi,
thanks for your reply. I thought of doing it like check the textbox value. But the problem is i am having 5 date control in that particular page.
So the condition for NULL checking, i need to try out with all probability. I believe, in that case, i should use around 25 if statement with each statement checking for all 5 condidtion.
Is there any other way to doing it.
rgds
ramu

|||you can check for nulls at the time of passing the value to the stored proc. you will have only 5 conditions. just before you pass the value to the parameter check for null. if you post some code we can help you out.|||You can just do iif(me.txtDate1.text.length=0,dbNull.value, me.txtDate1.text) if you are using parms. If not iif(me.txtDate1.text.length=0,"NULL", me.txtDate1.text)

Nick|||hi nick
dbnull.value does not seem to work. we need to use a special sqldatetime.null and import the sqltypes namespace for it.|||hi all,
once again, thanks for your reply.
since it was getting complicated and time was main constraint for me, so i tried it in the following way.
If the value is null, i let it get stored as 01/01/1900 itself.
but while fetching record for edit/view, i am checking those date columns for for above. If yes, i display null value, otherwise the stored value is displayed.
the above one temporarily solved my problem.
based on your solution, i think iif will solve my issue.
rgds
ramu|||

ndinakar wrote:

hi nick
dbnull.value does not seem to work. we need to use a special sqldatetime.null and import the sqltypes namespace for it.

Maybe its because of the way its being used? I always pass dbNull.value to my stored procs if the value is blank....

Nick|||It works for all columns except datetime columns. Leaving datetime columns empty will throw errors.

Problem with DateTime and strings in stored procedures

I'm keeping in the database a log of all the sessions for my
application. I'm trying to write a stored procedure that returns all
the sessions that; the login contains a certain string, loggedin after
a certain datetime and loggedout before another datetime. Any
combination of these parameters can be used and, if none, returns all
the log.

Below is the code I came up with but I'm having a "Syntax error
converting datetime from character string" exception. When not using
DateTime parameters everything works fine. Can you tell me how can I
avoid this exception? Thanks in advance...

ALTER PROCEDURE dbo.RetrieveAllSessionHistoryItemsContaining
(
@.Pattern Varchar(255),
@.From DateTime,
@.To DateTime
)
AS
DECLARE @.Query VARCHAR(500)
SET @.Query = 'SELECT * FROM SessionHistoryItems, Sessions WHERE
SessionHistoryItems.SessionId = Sessions.SessionId'

DECLARE @.conditions nvarchar(257)
SET @.conditions = '';

IF LEN(@.Pattern) > 0 BEGIN
SET @.conditions = @.conditions + ' Sessions.Login LIKE ''%' + @.Pattern
+ '%'''
END

IF @.From IS NOT NULL BEGIN
IF LEN(@.conditions) > 0 BEGIN
SET @.conditions = @.conditions + ' AND '
END
SET @.conditions = @.conditions + ' SessionHistoryItems.LoggedOutAt >=
' + @.From
END

IF @.To IS NOT NULL BEGIN
IF LEN(@.conditions) > 0 BEGIN
SET @.conditions = @.conditions + ' AND '
END
SET @.conditions = @.conditions + ' SessionHistoryItems.LoggedInAt <= '
+ @.To
END

IF LEN(@.conditions) > 0 BEGIN
EXEC(@.Query + ' AND ' + @.conditions)
END
ELSE BEGIN
EXEC(@.Query)
END
RETURN<antao@.iilab.com> wrote in message
news:1117467828.906603.299460@.g49g2000cwa.googlegr oups.com...
> I'm keeping in the database a log of all the sessions for my
> application. I'm trying to write a stored procedure that returns all
> the sessions that; the login contains a certain string, loggedin after
> a certain datetime and loggedout before another datetime. Any
> combination of these parameters can be used and, if none, returns all
> the log.
> Below is the code I came up with but I'm having a "Syntax error
> converting datetime from character string" exception. When not using
> DateTime parameters everything works fine. Can you tell me how can I
> avoid this exception? Thanks in advance...
> ALTER PROCEDURE dbo.RetrieveAllSessionHistoryItemsContaining
> (
> @.Pattern Varchar(255),
> @.From DateTime,
> @.To DateTime
> )
> AS
> DECLARE @.Query VARCHAR(500)
> SET @.Query = 'SELECT * FROM SessionHistoryItems, Sessions WHERE
> SessionHistoryItems.SessionId = Sessions.SessionId'
> DECLARE @.conditions nvarchar(257)
> SET @.conditions = '';
> IF LEN(@.Pattern) > 0 BEGIN
> SET @.conditions = @.conditions + ' Sessions.Login LIKE ''%' + @.Pattern
> + '%'''
> END
> IF @.From IS NOT NULL BEGIN
> IF LEN(@.conditions) > 0 BEGIN
> SET @.conditions = @.conditions + ' AND '
> END
> SET @.conditions = @.conditions + ' SessionHistoryItems.LoggedOutAt >=
> ' + @.From
> END
> IF @.To IS NOT NULL BEGIN
> IF LEN(@.conditions) > 0 BEGIN
> SET @.conditions = @.conditions + ' AND '
> END
> SET @.conditions = @.conditions + ' SessionHistoryItems.LoggedInAt <= '
> + @.To
> END
> IF LEN(@.conditions) > 0 BEGIN
> EXEC(@.Query + ' AND ' + @.conditions)
> END
> ELSE BEGIN
> EXEC(@.Query)
> END
> RETURN

It looks like you need to CAST or CONVERT the datetime to a string (and add
quotes) in order to build up the @.conditions string:

declare @.dt datetime
set @.dt = getdate()

select 'x' + @.dt -- fails
select 'x''' + cast(@.dt as varchar(20)) + '''' -- succeeds

But in this case, using sp_executesql would probably be a better approach
anyway:

exec sp_executesql
N'select col1, col2 from dbo.MyTable where datecol >= @.From and datecol <=
@.To',
N'@.From datetime, @.To datetime',
@.From, @.To

See sp_executesql in Books Online, and also these articles for more
information/ideas:

http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html

Simon

Problem with DateTime and milliseconds (a bug?)

Hi,
I Hope someone can help answer this question.
I have a report containing two datetime parameters.
Both parameters are populated with data from a sql-query. The fields
returned from this query are datetime's.
Everything works as expected when the fields does not contain any
milliseconds, but when I changed this and added some records with
milliseconds I get this message "Default value or value provided for
the report parameter 'sortSchemeActivated' is not a valid value."
I change the records back again so there are no milliseconds and it's
working again. So the only ting a can come up with is that dateTime
parameters are not compatible with milliseconds.
Is this a bug?
Thanks
- PeterI may be wrong on this, however I belive DateTimes work by counting the
number of seconds from a specific event.
In *nix that date is Jan 01 1970, unsure what it is in windows. However,
coming back to your question, this count occurs as the "second" level and
not below that. I don't know if milliseconds are stored as decimal values as
part of this, I dont think it is.
Taz
"Peter H." <peter.hamann@.eu.fkilogistex.com> wrote in message
news:1157641606.487218.72860@.d34g2000cwd.googlegroups.com...
> Hi,
> I Hope someone can help answer this question.
> I have a report containing two datetime parameters.
> Both parameters are populated with data from a sql-query. The fields
> returned from this query are datetime's.
> Everything works as expected when the fields does not contain any
> milliseconds, but when I changed this and added some records with
> milliseconds I get this message "Default value or value provided for
> the report parameter 'sortSchemeActivated' is not a valid value."
> I change the records back again so there are no milliseconds and it's
> working again. So the only ting a can come up with is that dateTime
> parameters are not compatible with milliseconds.
> Is this a bug?
> Thanks
> - Peter
>|||Thanks Taz
But not sure that this answers my question.
So to be more specific I will start by asking if anyone can reproduce
this scenario. This will eliminate the possibility of my computer
having a specific setting being the cause.

Problem with DateTime

After Retriveing DateTime data from sql server 2005 through vb.net 2003,the
value of miilSecond Part of the datetime is different from one shown in
database.
ex:
when i use sql query in query analyzer ,the value returns 04:32:00.140
when same query executing through vb.net ,the value returns 04:32:00.320Can you post the query that you are using to retrieve the data?|||select dbo.ToDate(<field> ) from <Table>
Here ToDate is user defined function and field is bigInt dataType.
"Omnibuzz" wrote:

> Can you post the query that you are using to retrieve the data?
>|||Can you post the function definition?|||sorry
what written in sql server,same written in .net.
"Omnibuzz" wrote:

> Can you post the function definition?
>

Problem with date filters in SQL 2005 Report Builder

Hi,

I'm having a hard time getting date filters to work properly in Report Builder 2005. One of my model entites contains a datetime field called Date Opened, which corresponds to a datetime field in my database table. This is how the data looks like: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM.

1. I created an ad-hoc report and added a filter on Date Opened field, setting condition to PROMPT where Date Opened EQUALS some value. Next to EQUALS I get a dropdown list with the following values: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM. It looks like this list was generated by pulling all Date Opened values (including a time stamp) from the database. The problem comes up when I run this report and Date Opened filter limits me to choosing exact date time, when I want to see all rows for the specific date (the entire day). Is there any way to make EQUALS list not include the time stamps and show only dates, or give me a calendar control instead so I can choose the date (no time) to filter on? I noticed that if database had no existing values for Date Opened, the Equals list lets me choose a date with a calendar. I need to accomplish the same even if there are some values in the database.

2. The same setup as above. This time I choose to see all rows where Date Opened is AFTER specific date and set it to PROMPT the user. If I actually choose a default value on Filter Date dialog, run the report having picked some new value in a filter, it behaves as expected giving me all rows AFTER the date I specified. If I leave default value unspecified, run the report having picked some new value in a filter, it returns all rows on the date I specified and AFTER. In other words, it behaves like ON or AFTER. Is this a bug?

3. Same as scenario #2 above just using On or BEFORE. If I leave the default value unspecified on Filter Date dialog, run the report having picked some new value in a filter, it returns all rows before the date I specified, behaving like BEFORE rather than On or BEFORE. Is this a bug?

Any help is greately appreciated. I know we'll be getting customer calls about these issues.

Zhenia

Let's start with #1 and proceed to the other stuff after you're using values you're happy with for the filter (and it's true, date match and date conversions as well as date representations, especially across locales, is always a PITA!!)

Can you add a field to your query that adds a CONVERT() to your actual date value, and use this for your list? This way (a) you can be sure of the representation vis-a-vis your locale and (b) take the timestamps off. Note: you may have to re-cast back to a date time in your filter expression, depending on exactly how you are doing this. I am not all that familiar with the ad-hoc Report Builder stuff.

>L<

|||

Hi Lisa,

Thanks for your reply. I don't have direct access to the query built by Report Builder, but your suggestion gave me an idea. I created a new date field as follows New Date Created = DATEONLY(Date Created). DATEONLY is a function provided by Report Builder. This gave me Date Created without a time stamp. I tried filtering using this new field and all of my issues disappeared. I now get a Calendar control as a user prompt and AFTER and ON OR BEFORE conditions work as they should.

I can only guess that Report Builder is not good at filtering on full date time fields. I now need to add this new date only field to all the datetime fileds in my models. What a pain! But at least it works!

Thanks again!

Zhenia

Problem with date filters in SQL 2005 Report Builder

Hi,

I'm having a hard time getting date filters to work properly in Report Builder 2005. One of my model entites contains a datetime field called Date Opened, which corresponds to a datetime field in my database table. This is how the data looks like: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM.

1. I created an ad-hoc report and added a filter on Date Opened field, setting condition to PROMPT where Date Opened EQUALS some value. Next to EQUALS I get a dropdown list with the following values: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM. It looks like this list was generated by pulling all Date Opened values (including a time stamp) from the database. The problem comes up when I run this report and Date Opened filter limits me to choosing exact date time, when I want to see all rows for the specific date (the entire day). Is there any way to make EQUALS list not include the time stamps and show only dates, or give me a calendar control instead so I can choose the date (no time) to filter on? I noticed that if database had no existing values for Date Opened, the Equals list lets me choose a date with a calendar. I need to accomplish the same even if there are some values in the database.

2. The same setup as above. This time I choose to see all rows where Date Opened is AFTER specific date and set it to PROMPT the user. If I actually choose a default value on Filter Date dialog, run the report having picked some new value in a filter, it behaves as expected giving me all rows AFTER the date I specified. If I leave default value unspecified, run the report having picked some new value in a filter, it returns all rows on the date I specified and AFTER. In other words, it behaves like ON or AFTER. Is this a bug?

3. Same as scenario #2 above just using On or BEFORE. If I leave the default value unspecified on Filter Date dialog, run the report having picked some new value in a filter, it returns all rows before the date I specified, behaving like BEFORE rather than On or BEFORE. Is this a bug?

Any help is greately appreciated. I know we'll be getting customer calls about these issues.

Zhenia

Let's start with #1 and proceed to the other stuff after you're using values you're happy with for the filter (and it's true, date match and date conversions as well as date representations, especially across locales, is always a PITA!!)

Can you add a field to your query that adds a CONVERT() to your actual date value, and use this for your list? This way (a) you can be sure of the representation vis-a-vis your locale and (b) take the timestamps off. Note: you may have to re-cast back to a date time in your filter expression, depending on exactly how you are doing this. I am not all that familiar with the ad-hoc Report Builder stuff.

>L<

|||

Hi Lisa,

Thanks for your reply. I don't have direct access to the query built by Report Builder, but your suggestion gave me an idea. I created a new date field as follows New Date Created = DATEONLY(Date Created). DATEONLY is a function provided by Report Builder. This gave me Date Created without a time stamp. I tried filtering using this new field and all of my issues disappeared. I now get a Calendar control as a user prompt and AFTER and ON OR BEFORE conditions work as they should.

I can only guess that Report Builder is not good at filtering on full date time fields. I now need to add this new date only field to all the datetime fileds in my models. What a pain! But at least it works!

Thanks again!

Zhenia

Problem with date filters in SQL 2005 Report Builder

Hi,

I'm having a hard time getting date filters to work properly in Report Builder 2005. One of my model entites contains a datetime field called Date Opened, which corresponds to a datetime field in my database table. This is how the data looks like: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM.

1. I created an ad-hoc report and added a filter on Date Opened field, setting condition to PROMPT where Date Opened EQUALS some value. Next to EQUALS I get a dropdown list with the following values: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM. It looks like this list was generated by pulling all Date Opened values (including a time stamp) from the database. The problem comes up when I run this report and Date Opened filter limits me to choosing exact date time, when I want to see all rows for the specific date (the entire day). Is there any way to make EQUALS list not include the time stamps and show only dates, or give me a calendar control instead so I can choose the date (no time) to filter on? I noticed that if database had no existing values for Date Opened, the Equals list lets me choose a date with a calendar. I need to accomplish the same even if there are some values in the database.

2. The same setup as above. This time I choose to see all rows where Date Opened is AFTER specific date and set it to PROMPT the user. If I actually choose a default value on Filter Date dialog, run the report having picked some new value in a filter, it behaves as expected giving me all rows AFTER the date I specified. If I leave default value unspecified, run the report having picked some new value in a filter, it returns all rows on the date I specified and AFTER. In other words, it behaves like ON or AFTER. Is this a bug?

3. Same as scenario #2 above just using On or BEFORE. If I leave the default value unspecified on Filter Date dialog, run the report having picked some new value in a filter, it returns all rows before the date I specified, behaving like BEFORE rather than On or BEFORE. Is this a bug?

Any help is greately appreciated. I know we'll be getting customer calls about these issues.

Zhenia

Let's start with #1 and proceed to the other stuff after you're using values you're happy with for the filter (and it's true, date match and date conversions as well as date representations, especially across locales, is always a PITA!!)

Can you add a field to your query that adds a CONVERT() to your actual date value, and use this for your list? This way (a) you can be sure of the representation vis-a-vis your locale and (b) take the timestamps off. Note: you may have to re-cast back to a date time in your filter expression, depending on exactly how you are doing this. I am not all that familiar with the ad-hoc Report Builder stuff.

>L<

|||

Hi Lisa,

Thanks for your reply. I don't have direct access to the query built by Report Builder, but your suggestion gave me an idea. I created a new date field as follows New Date Created = DATEONLY(Date Created). DATEONLY is a function provided by Report Builder. This gave me Date Created without a time stamp. I tried filtering using this new field and all of my issues disappeared. I now get a Calendar control as a user prompt and AFTER and ON OR BEFORE conditions work as they should.

I can only guess that Report Builder is not good at filtering on full date time fields. I now need to add this new date only field to all the datetime fileds in my models. What a pain! But at least it works!

Thanks again!

Zhenia