Default Language and DateTime

Hi,
I have set British English as my default English. The DateTime field still
accepts the date as mdy. Why?
Thanks
VivekHopefully following posting can help you:
1.
http://www.microsoft.com/technet/co...r />
96A551&ca
tlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
2.
http://www.microsoft.com/technet/co...r />
96A551&ca
tlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Vivek Sharma" wrote:

> Hi,
> I have set British English as my default English. The DateTime field stil
l
> accepts the date as mdy. Why?
> Thanks
> Vivek

Default Language and DateTime

Hi,
I have set British English as my default English. The DateTime field still
accepts the date as mdy. Why?
Thanks
Vivek
Hopefully following posting can help you:
1.
http://www.microsoft.com/technet/com...3004596A551&ca
tlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
2.
http://www.microsoft.com/technet/com...3004596A551&ca
tlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Vivek Sharma" wrote:

> Hi,
> I have set British English as my default English. The DateTime field still
> accepts the date as mdy. Why?
> Thanks
> Vivek

Default Language and DateTime

Hi,
I have set British English as my default English. The DateTime field still
accepts the date as mdy. Why?
Thanks
VivekHopefully following posting can help you:
1.
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?query=stored+procedure+to+accept+dates+in+the+format&dg=microsoft.public.sqlserver.server&cat=en-us-technet-sqlserv&lang=en&cr=US&pt=261BA873-F3AB-420E-96D6-E3004596A551&catlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
2.
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?query=this+query+regarding+Date+format+in+SQL+Server&dg=microsoft.public.sqlserver.server&cat=en-us-technet-sqlserv&lang=en&cr=US&pt=261BA873-F3AB-420E-96D6-E3004596A551&catlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Vivek Sharma" wrote:
> Hi,
> I have set British English as my default English. The DateTime field still
> accepts the date as mdy. Why?
> Thanks
> Vivek

DEFAULT keyword performance

I have a function which performs a query and returns a table. The one
parameter that can get passed in is a date which defaults to NULL.
There is an IF statement in the function that will set the paramter to
an actual date if null. If I call the function while passing in a date
the function comes back a second or 2 later. But if I pass in DEFAULT
to the function, the same query takes 8 minutes. See code below and
sample call below.

CREATE FUNCTION fCalculateProfitLossFromClearing (
@.TradeDate DATETIME = NULL
)
RETURNS @.t TABLE (
[TradeDate] DATETIME,
[Symbol] VARCHAR(15),
[Identity] VARCHAR(15),
[Exchange] VARCHAR(5),
[Account] VARCHAR(10),
[Value] DECIMAL(18, 6)
)
AS
BEGIN
-- Use previous trading date if none specified
IF @.TradeDate IS NULL
SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()

-- Make the query
INSERT @.t
SELECT
@.TradeDate,
tblTrade.[Symbol],
tblTrade.[Identity],
tblTrade.[Exchange],
tblTrade.[Account],
SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
ELSE ABS(tblTrade.[Quantity]) END) * (tblPos.[ClosingPrice] -
tblTrade.[Price])) AS [Value]
FROM
Historical.dbo.ClearingTrade tblTrade
LEFT JOIN Historical.dbo.ClearingPosition tblPos ON (@.TradeDate =
tblPos.[TradeDate] AND tblTrade.[Symbol] = tblPos.[Symbol] AND
tblTrade.[Identity] = tblPos.[Identity])
WHERE
([TradeTimestamp] >= @.TradeDate AND [TradeTimestamp] < DATEADD(DAY,
1, @.TradeDate))
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]

RETURN
END

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing('09/25/2003')

it returns in 2 seconds.

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing(DEFAULT)

in which GetPreviousTradeDate() will set @.TradeDate to 09/25/2003 it
returns in 8 minutes.[posted and mailed, please reply in news]

Jason (JayCallas@.hotmail.com) writes:
> I have a function which performs a query and returns a table. The one
> parameter that can get passed in is a date which defaults to NULL.
> There is an IF statement in the function that will set the paramter to
> an actual date if null. If I call the function while passing in a date
> the function comes back a second or 2 later. But if I pass in DEFAULT
> to the function, the same query takes 8 minutes. See code below and
> sample call below.

The query seems familiar. :-)

The reason for this is that when SQL Server builds the query plan,
it considers the value of the input parameter. When you provide an
explicit date, SQL Server can consult the statistics for the table
and see that the value you provided is very selective, and use the
index.

But if you provide NULL, SQL Server will build the query plan on that
assumption. Obviously a NULL value would return no rows, but SQL Server
never makes any assumptions that could yield incorrect results. Since
you WHERE condition is for a range, SQL Server appears to prefer to
scan the table, than using a non-clustered index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>
> The query seems familiar. :-)
> The reason for this is that when SQL Server builds the query plan,
> it considers the value of the input parameter. When you provide an
> explicit date, SQL Server can consult the statistics for the table
> and see that the value you provided is very selective, and use the
> index.
> But if you provide NULL, SQL Server will build the query plan on that
> assumption. Obviously a NULL value would return no rows, but SQL Server
> never makes any assumptions that could yield incorrect results. Since
> you WHERE condition is for a range, SQL Server appears to prefer to
> scan the table, than using a non-clustered index.

I hate the restart this thread but I have hit a brick wall...

I am at a lose of whether to creat functions or stored procedures (or
even views).

The below questions/issues are based on a need to return information
on a particular date for one to many symbols.

For my example lets say 15 symbols. You could do a query like Symbol =
'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
Symbol IN ('a','b',...))

I would also like to give my functions and stored procedures to use a
default date if none is specified. I created a function named
GetPreviousTradeDate() which does this based on a calendar.

SO here is how I see it.

Stored procedures seem to be the fastest in terms of returning data
back. But they seem to be limited in the sense that they can return
ONE row or ALL the rows since you cannot pass in a variable list of
symbols. You also cannot use the SP as part of a query. You could just
return all the rows back to the client and do filter or searching on
that end but that does not seem efficient or professional.

A function also does not let you pass in a variable list of symbols
but at least you can use it in a query. You could do something like
SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
happens at the server side and only the needed rows gets sent back.

But functions seem to have MAJOR performance problems when default
values are passed in. When I pass in a specific date the query takes a
few seconds but when I pass in DEFAULT and set the date to the results
of the GetPreviousTradeDate() function the query takes anywhere from 8
minutes to 15 minutes. (This even happens if I do not use the
GetPreviousTradeDate() function and set my parameter to a hard-coded
value)

Any thoughts or comments would be appreciated.|||Jason (JayCallas@.hotmail.com) writes:
> A function also does not let you pass in a variable list of symbols
> but at least you can use it in a query. You could do something like
> SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
> happens at the server side and only the needed rows gets sent back.

I happen to have an article on my web site that discusses this case
in detail. You don't have to read all of it, but you can just get
the function you need at
http://www.algonet.se/~sommar/array...html#iterative.

> But functions seem to have MAJOR performance problems when default
> values are passed in. When I pass in a specific date the query takes a
> few seconds but when I pass in DEFAULT and set the date to the results
> of the GetPreviousTradeDate() function the query takes anywhere from 8
> minutes to 15 minutes. (This even happens if I do not use the
> GetPreviousTradeDate() function and set my parameter to a hard-coded
> value)

The difference is not always that big, but apparently your query is
sensitive for this. I suggest that you split up the procedure in two:

EXEC outer_sp @.date = NULL datetime
IF @.date IS NULL
SELECT @.date = dbo.yourfunctionfordefault()
EXEC inner_sp @.date

And then inner_sp includes the actual query.

For a long treatise on this subject, search on Google news for articles
by Bart Duncan (a escalation engineer at Microsoft) and the phrase
"parameter sniffing".

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||JayCallas@.hotmail.com (Jason) wrote in message news:<f01a7c89.0310141510.28e9c846@.posting.google.com>...
> I hate the restart this thread but I have hit a brick wall...
> I am at a lose of whether to creat functions or stored procedures (or
> even views).
> The below questions/issues are based on a need to return information
> on a particular date for one to many symbols.
> For my example lets say 15 symbols. You could do a query like Symbol =
> 'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
> Symbol IN ('a','b',...))
> I would also like to give my functions and stored procedures to use a
> default date if none is specified. I created a function named
> GetPreviousTradeDate() which does this based on a calendar.
> SO here is how I see it.
> Stored procedures seem to be the fastest in terms of returning data
> back. But they seem to be limited in the sense that they can return
> ONE row or ALL the rows since you cannot pass in a variable list of
> symbols. You also cannot use the SP as part of a query. You could just
> return all the rows back to the client and do filter or searching on
> that end but that does not seem efficient or professional.
> A function also does not let you pass in a variable list of symbols
> but at least you can use it in a query. You could do something like
> SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
> happens at the server side and only the needed rows gets sent back.
> But functions seem to have MAJOR performance problems when default
> values are passed in. When I pass in a specific date the query takes a
> few seconds but when I pass in DEFAULT and set the date to the results
> of the GetPreviousTradeDate() function the query takes anywhere from 8
> minutes to 15 minutes. (This even happens if I do not use the
> GetPreviousTradeDate() function and set my parameter to a hard-coded
> value)
> Any thoughts or comments would be appreciated.

Since the stored procedure has both the speed and the ability to use
default values without performance hit, would it be normal practice or
efficient to send separate queries for each symbol to the stored
procedure? This could result in anywhere from a few to several hundred
calls at a time.|||Jason (JayCallas@.hotmail.com) writes:
> Since the stored procedure has both the speed and the ability to use
> default values without performance hit, would it be normal practice or
> efficient to send separate queries for each symbol to the stored
> procedure? This could result in anywhere from a few to several hundred
> calls at a time.

That does not seem like a good idea. Certainly more efficient to get
data for all symbols at once. See my previous post for suggestions.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> A function also does not let you pass in a variable list of symbols
but at least you can use it in a query ... Any thoughts or comments
would be appreciated. <<

Ever try putting the list of symbols into a one column table and using
an "IN (SELECT parm FROM Parmlist)" instead?

default Datetime parameter

Hi,
I'm editing some reports at the moment, and they've been set up using start
and end date parameters both having datetime datatype.
They both have a default value as the user would like them to run
immediately, the problem is they are both set to Date.Now() so no
information is coming out as the start date is now! i would like the start
date to default to 5 yrs in the past, what can i type in. I'd like it to
use the Date.Now() or getdate() methods or something like that so it will
change automatically.
any suggestions would be much appreciated.
cheers GregSet the default value to =System.DateTime.Now.AddYears(-5).|||Set the default value to =System.DateTime.Now.AddYears(-5).|||To do the same thing via sql... create a dataset with
select dateadd(yy,-5,getdate())
and use this as the default...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Potter" wrote:
> Set the default value to =System.DateTime.Now.AddYears(-5).
>|||cheers guys,
went for potter's solution as it's running off a stored procedure and
couldn't be bothered to change it.
thanks
Greg
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:D8794D05-5CCD-4F96-92DE-09EB773C8F9C@.microsoft.com...
> To do the same thing via sql... create a dataset with
> select dateadd(yy,-5,getdate())
> and use this as the default...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Potter" wrote:
>> Set the default value to =System.DateTime.Now.AddYears(-5).
>>|||Ok new problem, and in fact part fo the reason why i asked the firsdt
question.
The report has a hyperlink to drillthrough to the next report, the
parameters are passed through by the hyperlink, the problem is i keep
getting an error on the start date. They are both set as datetime
datatypes. The error is
The value provided for the report parameter 'StartDate' is not valid for its
type. (rsReportParameterTypeMismatch)
cheers
Greg
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:D8794D05-5CCD-4F96-92DE-09EB773C8F9C@.microsoft.com...
> To do the same thing via sql... create a dataset with
> select dateadd(yy,-5,getdate())
> and use this as the default...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Potter" wrote:
>> Set the default value to =System.DateTime.Now.AddYears(-5).
>>|||Greg,
How are you going about this hyperlinking? Are you using 'jump to
report' or are you using the 'jump to url'?
Is the value for the date in the hyperlink actually the parameter value
(ie - =Parameters!Date.Value) or is it a value from a dataset (ie
=Fields!Date.Value)?
Potter|||Jump to report and it's literally just passing the parameter from the first
report to the parameter in the second report.
Greg
"Potter" <drewpotter@.gmail.com> wrote in message
news:1134671641.653682.112660@.o13g2000cwo.googlegroups.com...
> Greg,
> How are you going about this hyperlinking? Are you using 'jump to
> report' or are you using the 'jump to url'?
> Is the value for the date in the hyperlink actually the parameter value
> (ie - =Parameters!Date.Value) or is it a value from a dataset (ie
> =Fields!Date.Value)?
> Potter
>|||Also for the record, the links work fine in preview mode it's only a problem
when viewed through report manager
"Potter" <drewpotter@.gmail.com> wrote in message
news:1134671641.653682.112660@.o13g2000cwo.googlegroups.com...
> Greg,
> How are you going about this hyperlinking? Are you using 'jump to
> report' or are you using the 'jump to url'?
> Is the value for the date in the hyperlink actually the parameter value
> (ie - =Parameters!Date.Value) or is it a value from a dataset (ie
> =Fields!Date.Value)?
> Potter
>|||Try explicitly converting the value passed in the Jump To. ie =Convert.ToDateTime(Parameters!DateParam.Value)
Greg wrote:
> Also for the record, the links work fine in preview mode it's only a problem
> when viewed through report manager
> "Potter" <drewpotter@.gmail.com> wrote in message
> news:1134671641.653682.112660@.o13g2000cwo.googlegroups.com...
> > Greg,
> >
> > How are you going about this hyperlinking? Are you using 'jump to
> > report' or are you using the 'jump to url'?
> >
> > Is the value for the date in the hyperlink actually the parameter value
> > (ie - =Parameters!Date.Value) or is it a value from a dataset (ie
> > =Fields!Date.Value)?
> >
> > Potter
> >|||cheers, i haven't tried that as i have an in house written report viewer
which seems to deal with it no problem. So i'm not worried about report
manager having problems with it anymore. Thanks for your help though
Greg
"Potter" <drewpotter@.gmail.com> wrote in message
news:1134784157.263872.184070@.g47g2000cwa.googlegroups.com...
> Try explicitly converting the value passed in the Jump To. ie => Convert.ToDateTime(Parameters!DateParam.Value)
> Greg wrote:
>> Also for the record, the links work fine in preview mode it's only a
>> problem
>> when viewed through report manager
>> "Potter" <drewpotter@.gmail.com> wrote in message
>> news:1134671641.653682.112660@.o13g2000cwo.googlegroups.com...
>> > Greg,
>> >
>> > How are you going about this hyperlinking? Are you using 'jump to
>> > report' or are you using the 'jump to url'?
>> >
>> > Is the value for the date in the hyperlink actually the parameter value
>> > (ie - =Parameters!Date.Value) or is it a value from a dataset (ie
>> > =Fields!Date.Value)?
>> >
>> > Potter
>> >
>sql

Default Date Value (today)

I have migrated from Access to SQL Server.
In Access I have default date value of today's date.

In SQL Server, it is empty (after migration).
What is the corresponding syntax (value) to default to
the current Date & Time in a DATETIME Field (using Design Table) ?

Thanks you.

Also, what is the difference between DATETIME and SMALLDATETIME
fields ?

Pierre.Hey there,

When creating your table add a default constraint to your column, something like...

create table a_tbl (
col1 int not null,
col2 datetime constraint constr_1 default(getdate())
)

Or if you were creating the table using the table disign GUI, the default value should be getdate().

As for the difference between datetiem and smalldatetime, there is plenty of info in BOL. But basically smalldatetime is rounded to the nearest mintue, where as datetime is detailed to the fraction of a secound.

Hope this helps.

Originally posted by Plarde
I have migrated from Access to SQL Server.
In Access I have default date value of today's date.

In SQL Server, it is empty (after migration).
What is the corresponding syntax (value) to default to
the current Date & Time in a DATETIME Field (using Design Table) ?

Thanks you.

Also, what is the difference between DATETIME and SMALLDATETIME
fields ?

Pierre.|||As referred books online is the best bet to startwith the differences, syntax information and other examples which will give strength to the current knowledge in SQL server and ofcourse this forum is available to help you out in any consequences.

Good luck.

Default Date Value (today)

I have migrated from Access to SQL Server.
In Access I have default date value of today's date.

In SQL Server, it is empty (after migration).
What is the corresponding syntax (value) to default to
the current Date & Time in a DATETIME Field (using Design Table) ?

Thanks you.

Also, what is the difference between DATETIME and SMALLDATETIME
fields ?

Pierre.1.The function that returns the current timestamp is GETDATE()
2.datetime holds date and time from Jan 1,1753 to Dec 31,9999 with a three hundredth of a second accuracy
smalldatetime holds date and time from Jan 1, 1900 to June 6,2079 with accuracy to the minute, that fits with most applications.

Originally posted by Plarde
I have migrated from Access to SQL Server.
In Access I have default date value of today's date.

In SQL Server, it is empty (after migration).
What is the corresponding syntax (value) to default to
the current Date & Time in a DATETIME Field (using Design Table) ?

Thanks you.

Also, what is the difference between DATETIME and SMALLDATETIME
fields ?

Pierre.|||You can find this kind of info in the Books Online which is installed on your machine as you install sql server.|||An extra difference between datetime and smalldatetime types is for VB6 programmers. I've read somewhere that using smalldatetime gives problems in VB6 applications.

Ad.

Originally posted by dbadelphes
1.The function that returns the current timestamp is GETDATE()
2.datetime holds date and time from Jan 1,1753 to Dec 31,9999 with a three hundredth of a second accuracy
smalldatetime holds date and time from Jan 1, 1900 to June 6,2079 with accuracy to the minute, that fits with most applications.|||True its better to use DATETIME than the other with ADO/VB6.sql

Default date using datepicker

Hi
I'm working on a report in RS2005 and are using a start and enddate
parameter. They are of the datatype DateTime. By using this type it is
possible to use the datepicker on the report. However, I would like the
report to have a default date range say 01/01/2006 as startdate and
12/31/2006 as enddate, with the option to change this using the datepicker.
How do I set this up. I've tried using the Non-queried field on the parameter
wihtout any luck.
Thank you.This is how I do it
Create a dataset against some SQL database, either the db you're querying or
the Report Server.
Query : Select getdate() as Today, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
as FirstDayOfYear, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1,
0)) as LastDayOfYear
In your report, open the parameters pane.
Set the parameter data type to DateTime
Set the default values to "From Query", select the dataset you created and
select the column you want to use from your query.
The date picker control seems to need the time as well as the date, so don't
format the values from the SQL query.
The date picker will also display this timestamp if it's not 00:00:00 so you
might have to tweak the output a bit.
More tips on calculating different sql server dates at:
http://www.databasejournal.com/features/mssql/article.php/3076421
Kaisa M. Lindahl Lervik
"Martin GC" <MartinGC@.discussions.microsoft.com> wrote in message
news:26850882-D269-4F3C-8BA7-BECD2B1382C0@.microsoft.com...
> Hi
> I'm working on a report in RS2005 and are using a start and enddate
> parameter. They are of the datatype DateTime. By using this type it is
> possible to use the datepicker on the report. However, I would like the
> report to have a default date range say 01/01/2006 as startdate and
> 12/31/2006 as enddate, with the option to change this using the
> datepicker.
> How do I set this up. I've tried using the Non-queried field on the
> parameter
> wihtout any luck.
> Thank you.

Default date problem

Hi,

Good Day!

In my sproc, I m trying to set a default value for a parameter, but it's sending me an error. It seems like having probs with brackets!

Code Snippet

@.Date DateTime = GetDate(),

Code Snippet

Msg 102, Level 15, State 1, Procedure usp_Receive_Add, Line 8
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure usp_Receive_Add, Line 40
Must declare the scalar variable "@.ProductID".

Please tell me what's the prob! I m trying to set todays date to the parameter if nothing was supplied in the @.Date parameter.

Regards

Kapalic

Use the following logic to set the current date..

Code Snippet

Create proc MyProc
(
@.Date datetime = '1900-01-01'
)
as
Begin
Select @.Date = Case When @.Date <> '1900-01-01' Then @.Date Else Getdate() End
Select @.Date
End

go

Exec MyProc '2/2/2007'
Exec MyProc

|||How about setting default value of @.date to NULL and set it to GetDate() in store procedure body? I just wandering 1900-1-1 is valid value. here is valid value of DateTime according SQL Server 2005 document.

datetime

January 1, 1753, through December 31, 9999

|||

Yes.. You can do it with NULL.

Suppose if you want to store the explicit null value on your table then this logic wont work. RITE?

So we are setting some default value which we are assuming that it never passed from our UI.

1900-01-01 is valid value only. it is with in the given range Buddy.. It is a typical sql coders starting value [Cast(0 as Datetime)]

|||

In a variable declaration, you can set a variable to a constant, e.g., a value.

However, you cannot set it to the results of a function. Getdate() is a function.

As suggested, if you wish to make the parameter optional, set a default value of '01/01/1900', and then if you wish to set it to the current date/time, after entering the procedure code (after 'AS'), set the parameter = getdate().

Default date parameter in subscription

Hi all. Does anyone know how I can provide today's date as a subscription's
default date parameter? I thought I might be able to just state =Today() or
something similar when creating the subscription but it doesn't seem to work
out.
Thanks in advance.When you publish the report initially on the report server, the default
value of the report parameter has to be =Today. Expressions as parameter
values can only be defined on the initial report publishing.
Every explicitly user-specified parameter value on the report execution or
when setting up a subscription will be interpreted as constant value and not
as expression.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raul R" <roller8@.hotmail.com> wrote in message
news:ehkI80%231EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hi all. Does anyone know how I can provide today's date as a
subscription's
> default date parameter? I thought I might be able to just state =Today()
or
> something similar when creating the subscription but it doesn't seem to
work
> out.
> Thanks in advance.
>|||This may be a dumb question, but how do you set default values for parameters
when publishing?
I tried by going into the dataset properties and under the parameters tab
setting the value next to one of my params to '=datetime.today.adddays(-1)'
but when running the report i'm still prompted for the values...
My goal is to have this report setup w/ subscriptions that automatically run
using the previous days date, and also provide the flexibility to the user so
they can enter any date range they want...
Thanks!
"Robert Bruckner [MSFT]" wrote:
> When you publish the report initially on the report server, the default
> value of the report parameter has to be =Today. Expressions as parameter
> values can only be defined on the initial report publishing.
> Every explicitly user-specified parameter value on the report execution or
> when setting up a subscription will be interpreted as constant value and not
> as expression.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Raul R" <roller8@.hotmail.com> wrote in message
> news:ehkI80%231EHA.3820@.TK2MSFTNGP11.phx.gbl...
> > Hi all. Does anyone know how I can provide today's date as a
> subscription's
> > default date parameter? I thought I might be able to just state =Today()
> or
> > something similar when creating the subscription but it doesn't seem to
> work
> > out.
> >
> > Thanks in advance.
> >
> >
>
>|||Once a report is published on a report server and you then publish a report
with the same name and the same report parameter names, the information from
the new report gets merged with the old report. This is very useful for
production environments where you have your data sources pointing to
production databases and probably certain constant parameter default
settings. Hende, just "updating" the report won't trash your configuration
settings.
If you want to avoid this behavior, just delete the report from the report
server before you publish it again. Then the default values specified should
take effect.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben Sullins" <BenSullins@.discussions.microsoft.com> wrote in message
news:1EDE6675-EF22-47D4-A832-8FD90481D141@.microsoft.com...
> This may be a dumb question, but how do you set default values for
parameters
> when publishing?
> I tried by going into the dataset properties and under the parameters tab
> setting the value next to one of my params to
'=datetime.today.adddays(-1)'
> but when running the report i'm still prompted for the values...
> My goal is to have this report setup w/ subscriptions that automatically
run
> using the previous days date, and also provide the flexibility to the user
so
> they can enter any date range they want...
> Thanks!
> "Robert Bruckner [MSFT]" wrote:
> > When you publish the report initially on the report server, the default
> > value of the report parameter has to be =Today. Expressions as parameter
> > values can only be defined on the initial report publishing.
> > Every explicitly user-specified parameter value on the report execution
or
> > when setting up a subscription will be interpreted as constant value and
not
> > as expression.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Raul R" <roller8@.hotmail.com> wrote in message
> > news:ehkI80%231EHA.3820@.TK2MSFTNGP11.phx.gbl...
> > > Hi all. Does anyone know how I can provide today's date as a
> > subscription's
> > > default date parameter? I thought I might be able to just state
=Today()
> > or
> > > something similar when creating the subscription but it doesn't seem
to
> > work
> > > out.
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
> >

Default date parameter

I have two date parameters, a start and end date. I want the end date to
default to the start date if the user does not fill out the field for the end
date. If they do fill out the end date, then I want them to take this.
This should be really simple but everything I've tried has not worked. How
do you get SRS to do this? I have tried setting default values in Report
Parameters screen and I've also messed with the Allow Null Value checkbox.
Do I need to check this also? Please let me know how to configure the Report
Parameters.
Thank you.Ryan,
If I understand you correctly, you should be able to set the EndDate
parameter to 'Allow Null'.
In you SQL, check the parameter values passed in and conditionally
assign the EndDate the value of the StartDate if the EndDate is null.
Andy Potter

default date in sql2k

Having a table with two datetime columns(date_ & time_. One to store the date and the other to store the time (my client's design).

When I insert a date alone to the date_ column the time defaults to 12:00:00 AM (as expected).
But I have a problem when inserting / updating the time in the time_ column. When i insert the time from my asp application / query analyzer the date defaults to 1900-1-1(expected). When i insert the time from enterprise manager the date defaults to 1899-12-30.

Can anybody explain me why the date defaults to 1899-12-30 in enterprise manager

thanksThe following article explains this in detail:

article (http://www.databasejournal.com/features/mssql/article.php/1494281)

If you need further discussion, let me know and I will give you my 2 cents as to what is occurring.sql

default date in a subscription

Hello! How can i put a default date in one of my parameters in a subsciption?
i.e : each day - i want 'today' date in the parameter...
Thanks> Hello! How can i put a default date in one of my parameters in a
subsciption?
> i.e : each day - i want 'today' date in the parameter...
Use the Today(), Now() or DateString() VB functions for the default value
expression, e.g.
=DateString()
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Hello!
I did try the functions you wrote - but it seems that i can not do that in a
subscription - only in the report parameter.
I want to be able to do that when i define a subscription...
Thanks Any Way and if you have other idea - i will be happy if you wrote back.
Thanks!
"Penker" wrote:
> Hello! How can i put a default date in one of my parameters in a subsciption?
> i.e : each day - i want 'today' date in the parameter...
> Thanks|||When you create a subscription, you have possibility to use the default
values of the report parameters (at the bottom of the "New Subscription"
page). So, just put the VB function in the expression for the default value
of the report parameter, and then use this default value in your
subscription.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Penker" <Penker@.discussions.microsoft.com> wrote in message
news:0A004250-7E9A-4511-8A38-E8B94F2D7B34@.microsoft.com...
> Hello!
> I did try the functions you wrote - but it seems that i can not do that in
a
> subscription - only in the report parameter.
> I want to be able to do that when i define a subscription...
> Thanks Any Way and if you have other idea - i will be happy if you wrote
back.
> Thanks!
> "Penker" wrote:
> > Hello! How can i put a default date in one of my parameters in a
subsciption?
> > i.e : each day - i want 'today' date in the parameter...
> > Thanks|||I tried this several ways to accomplish this and have been unsuccessful. From
what i've read it seems you must set the default params in reoprt designer.
Still having trouble on how to accomplish that. But once I do here is the
function I had planned on using, if you figure out how to set the default
param values in report designer please post. Thanks...
=datetime.today.adddays(-1) --ive tested this in a textbox and it works...
"Dejan Sarka" wrote:
> When you create a subscription, you have possibility to use the default
> values of the report parameters (at the bottom of the "New Subscription"
> page). So, just put the VB function in the expression for the default value
> of the report parameter, and then use this default value in your
> subscription.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Penker" <Penker@.discussions.microsoft.com> wrote in message
> news:0A004250-7E9A-4511-8A38-E8B94F2D7B34@.microsoft.com...
> > Hello!
> > I did try the functions you wrote - but it seems that i can not do that in
> a
> > subscription - only in the report parameter.
> > I want to be able to do that when i define a subscription...
> > Thanks Any Way and if you have other idea - i will be happy if you wrote
> back.
> > Thanks!
> >
> > "Penker" wrote:
> >
> > > Hello! How can i put a default date in one of my parameters in a
> subsciption?
> > > i.e : each day - i want 'today' date in the parameter...
> > > Thanks
>
>

Default date in a derived column expression

one of my SSIS packages use this expression to put todays date in if it is NULL:

(ISNULL(datejs)) ? GETDATE() : datejs

however, what I really want to do though is put a default date in like '2007-01-01' but I get syntax error because SSIS thinks it's a string, which it is I suppose.

Is it possible to do what I want it to?

Thanks

Try casting it

(ISNULL(datejs)) ? (DT_DBTIMESTAMP)"2007-01-01" : datejs

|||Fresh from the Integration Services Expression Reference section of Books Online, we have the GETDATE (http://msdn2.microsoft.com/en-US/library/ms139875.aspx) topic. So using GETDATE is possible, but sounds like a variable or literal casted as you suggest is actually what is needed here.|||

Larry Charlton wrote:

Try casting it

(ISNULL(datejs)) ? (DT_DBTIMESTAMP)"2007-01-01" : datejs

That's done it. Thank You.

Default Date Format used by Mssql

Hi there,

What is the default date format used by transact sql? Would YYYY-MM-DD HOUR:MIN:SECS format work on Mssql?

I am working on a project that needs to work with atleast two databases (Mysql/Mssql). I use the above date format and while it works perfectly on all Mysql databases, it gives me trouble in some Mssql setups.

Most of the trouble arises when I am doing INSERT or SELECT queries.

How do I handle this? Is there some way that I can tell Mssql that I am using the yyyy-mm-dd format or should I find out what format that particular mssql is using and adopt it?YYYY-MM-DD hh:mm:ss should work fine with mssql. that's basically the ODBC format.

See http://msdn2.microsoft.com/en-us/library/ms187928.aspx for a list of all the different date formats in sql server.|||I've never had the ISO standard temporal format (YYYY-MM-DD HH:MM:SS.TTT) give me trouble with Microsoft SQL, that is actually the preferred format for dates and times. The only thing I've had trouble with Microsoft-SQL handling DATETIME values in that time format was because MS-SQL can only resolve time down to 3 ms so it sees all three of the following times as identical:

2006-08-07 06:05:04.000
2006-08-07 06:05:04.001
2006-08-07 06:05:04.002

If you are using SMALLDATETIME values, things get more interesting quickly, since those are only accurate to the minute. That might be a whole different issue.

-PatP|||One of my clients use Mssql and her server uses the YYYY-DD-MM date format. I had to change the date formatting in the code to make it work for her.

I would really need to know whether this is an issue in some mssql servers.|||No, I don't know of ANY condition under which MS-SQL 7.0 or later versions have any problem interpreting dates formatted as YYYY-MM-DD as long as the date is valid for the datatype you are using.

As I don't know what made you think you needed to change the date formatting, all I can do is say that the date format isn't the problem, something else is.

-PatP|||Yeah, When I changed my date format, the queries worked. So, the problem is really with the format.|||Maybe I'm not making this clear, but the ISO string format you are using for the date is NOT the problem. I don't know what is the problem, but the ISO format isn't it.

-PatP

Default Date Format

Hello All
In oracle we can specify the default date format for the datebase
connection, is there a way to specify the default date format and language
for SQL Server database connections. I am using DB-Library for C to
communicate to SQL Server from MS Visual C++.
Hope to get reply soon
Thanks
FaisalSET DATEFORMAT should work
regards,
Harshal.
"Faisal Mansoor" <fmansoor@.softpak.com> wrote in message
news:ukd6ezqLFHA.1180@.TK2MSFTNGP14.phx.gbl...
> Hello All
> In oracle we can specify the default date format for the datebase
> connection, is there a way to specify the default date format and language
> for SQL Server database connections. I am using DB-Library for C to
> communicate to SQL Server from MS Visual C++.
> Hope to get reply soon
> Thanks
> Faisal
>|||Only for input, not for output. Default date format for input is inherited f
rom the login, but can
be overridden by SET DATEFORMAT. I suggest you read
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Faisal Mansoor" <fmansoor@.softpak.com> wrote in message
news:ukd6ezqLFHA.1180@.TK2MSFTNGP14.phx.gbl...
> Hello All
> In oracle we can specify the default date format for the datebase
> connection, is there a way to specify the default date format and language
> for SQL Server database connections. I am using DB-Library for C to
> communicate to SQL Server from MS Visual C++.
> Hope to get reply soon
> Thanks
> Faisal
>

Default DATE and uniqueidentifier parameters?

I have several stored procedures and to facilitate getting the output of the stored procedures we have been adding default values for all of the input parameters. This works fine with the exception of DATE and uniqueidentifier parameters. I have defined stored procedures like:

ALTER PROCEDURE [dbo].[proc_GetOrderReasonByOrderGroupId]

@.OrderGroupId uniqueidentifier = NEWID

AS

and

ALTER PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom datetime = GETDATE,

@.DateTo datetime = GETDATE

but when I execute the following

SET FMTONLY ON

exec proc_shippedPackages

SET FMTONLY OFF

I get

Msg 241, Level 16, State 1, Procedure proc_shippedPackages, Line 0

Conversion failed when converting datetime from character string.

Any suggestions? The same error occurs with setting a uniqueidentifier. I want to create a default parameter that will more of less ensure that the output is empty.

Thank you.

Kevin

You could try the example below.

Beware, though, that if you do need to explicitly set the values of the @.DateFrom and @.DateTo parameters to NULL when calling the stored procedure [as opposed to simply not providing values for these optional parameters] then the parameters will be assigned a value of GETDATE() during execution, which may or may not be what you want. If this does turn out to be a problem then you could use an arbitrary [but unlikely to be used] date as the default value and then check for that value rather than NULL when assigning the value of @.Now.

Chris

Code Snippet

CREATE PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom DATETIME = NULL,

@.DateTo DATETIME = NULL

AS

--Ensure that both variables are set to

--equal values if defaults are required.

DECLARE @.Now DATETIME

SET @.Now = GETDATE()

IF @.DateFrom IS NULL

BEGIN

SET @.DateFrom = @.Now

END

IF @.DateTo IS NULL

BEGIN

SET @.DateTo = @.Now

END

SELECT @.DateFrom, @.DateTo

GO

|||

It is not bad idea to have NON-NULL values, suppose if you need to store / pass the NULL value from your code the below code wont break.

Code Snippet

Alter PROCEDURE [dbo].[proc_GetOrderReasonByOrderGroupId]

@.OrderGroupId uniqueidentifier = 0x0

AS

Select@.OrderGroupId = Case When @.OrderGroupId = 0x0 Then NewId() Else @.OrderGroupId End

go

Alter PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom datetime = '1900-01-01 00:00:00.000',

@.DateTo datetime = '1900-01-01 00:00:00.000'

as

SET @.DateFrom = Case When @.DateFrom = '1900-01-01 00:00:00.000' Then GetDate() Else @.DateFrom End

SET @.DateTo = Case When @.DateTo = '1900-01-01 00:00:00.000' Then GetDate() Else @.DateTo End

|||GETDATE and NEWID are functions and require () after them, unlike VB. Try:

@.OrderGroupId uniqueidentifier = NEWID()

@.DateFrom datetime = GETDATE(),

@.DateTo datetime = GETDATE()

|||TPhillips -> You are wrong; SP params only support the constant/NULL value as default value.|||Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

|||

Tom Phillips wrote:

Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

If you check the syntax with the Sql Management Studio it complains if you add the ().

|||

Kevin,

You cannot use the NEWID() and GETDATE() functions as default values in the parameter definition.

DEFAULT value assignments must be deterministic. Non-deterministic functions are not permitted in that context.

If your intent is to make the parameters optional, use '01/01/1900' (or NULL), then in the first lines of the sproc, check the values and if = '01/01/1900' (or NULL), then set the values = getdate().

Your original attempt failed because you are setting the default values to the string constants 'GETDATE' and 'NEWID.

NEWID() and GETDATE() both require parentheses as previously mentioned.

|||

Tom Phillips wrote:

Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

When I include the () I get:

Msg 102, Level 15, State 1, Procedure proc_GetCaseNotesByOrderGroupID, Line 4

Incorrect syntax near '('.

ALTER PROCEDURE [dbo].[proc_GetCaseNotesByOrderGroupID]

@.OrderGroupID uniqueidentifier = NEWID()

AS

|||As mentioned, you have to set the default to a "static", you cannot use a function on a default value.

What your code was doing without the () is equivalent to:

@.OrderGroupID uniqueidentifier = 'NEWID'

I assume you did not want the @.orderGroupID to be a string NEWID. I think this is a bug or at least hold over from Sybase which allows unquoted strings to be invisibly converted to a string.

The best way to do what you want is:

ALTER PROCEDURE [dbo].[proc_GetCaseNotesByOrderGroupID]

@.OrderGroupID uniqueidentifier = NULL -- or some other non-occurring number

AS

IF @.OrderGroupID IS NULL
SET @.OrderGroupID = NEWID()

sql

Default Date

Hi,

I have two parameters as StartDate and End Date. these dates are picked from the Datepicker.

What I want to do is to default the start date to the the 1st January of the current year and end date as 31st december of the current year.

I tried to use the default in 'Report Parameters'. But could not get through. Can anyone suggest me the solution.

regards

Josh

Under default values of the report parameters are, you can use expressions to create your default dates (using non-queried option)

EG, 1 Jan in current year, expression would be

=DateSerial(Year(Today),1,1)

and 31 Dec

=DateSerial(Year(Today),12,31)

|||

Hi Will,

Thanks a lot! This worked and was very helpful.

Regards

Josh

default date

in DateTime column called ExpireDate, I have default value of (1/1/2020)
yet when data entry is made (without ExpireDate value) the value is always
set at 1/1/1900
why is default not entered as 1/1/2020 ?
DEFAULTs only work when you don't provide a value for the column at all, or
use the keyword DEFAULT. What it looks like is that you provide the value 0
for the column, and 0 as a datetime is interpreted by SQL Server as
1/1/1900. See the following example:
CREATE TABLE TJS(Expire_Date DATETIME DEFAULT '20200101')
INSERT INTO TJS (Expire_Date) VALUES (0)
INSERT INTO TJS (Expire_Date) VALUES (DEFAULT)
SELECT Expire_Date FROM TJS
Jacco Schalkwijk
SQL Server MVP
"TJS" <nospam@.here.com> wrote in message
news:1132gu53p4kt9bd@.corp.supernews.com...
> in DateTime column called ExpireDate, I have default value of (1/1/2020)
> yet when data entry is made (without ExpireDate value) the value is always
> set at 1/1/1900
> why is default not entered as 1/1/2020 ?
>
>
|||your example works, but I am trying to use a stored procedure
I have this in the stored procedure:
@.ExpireDate datetime = DEFAULT
The column default value is set as (1/1/2020)
but it still enters 1/1/1900
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uIdTaWhJFHA.576@.TK2MSFTNGP15.phx.gbl...
> DEFAULTs only work when you don't provide a value for the column at all,
> or use the keyword DEFAULT. What it looks like is that you provide the
> value 0 for the column, and 0 as a datetime is interpreted by SQL Server
> as 1/1/1900. See the following example:
> CREATE TABLE TJS(Expire_Date DATETIME DEFAULT '20200101')
> INSERT INTO TJS (Expire_Date) VALUES (0)
> INSERT INTO TJS (Expire_Date) VALUES (DEFAULT)
> SELECT Expire_Date FROM TJS
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "TJS" <nospam@.here.com> wrote in message
> news:1132gu53p4kt9bd@.corp.supernews.com...
>
|||hi,
TJS wrote:
> your example works, but I am trying to use a stored procedure
> I have this in the stored procedure:
> @.ExpireDate datetime = DEFAULT
> The column default value is set as (1/1/2020)
> but it still enters 1/1/1900
>
do you mean your procedure's code is
DECLARE @.ExpireDate datetime
SELECT @.ExpireDate = DEFAULT
INSERT INTO #test VALUES ( 1 , @.ExpireDate )
or
DECLARE @.ExpireDate datetime
SELECT @.ExpireDate = '20200101'
INSERT INTO #test VALUES ( 2 , @.ExpireDate )
?
the first code will actually raise an exception (Incorrect syntax near the
keyword 'DEFAULT'.) and non data will be entered...
can you please expand?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Alter PROCEDURE dbo.AddUser
(
@.Name nvarchar(50),
@.Email nvarchar(100),
@.Password nvarchar(50),
@.ExpireDate datetime = DEFAULT,
@.EnableNewsLetter bit,
@.UserID int OUTPUT
)
AS
INSERT INTO _Users
(
Name,
Email,
Password,
ExpireDate,
EnableNewsletter
)
VALUES
(
@.Name,
@.Email,
@.Password,
@.ExpireDate,
@.EnableNewsLetter
)
SELECT
@.UserID = @.@.Identity
|||hi TJS,
TJS wrote:
> Alter PROCEDURE dbo.AddUser
> (
> @.Name nvarchar(50),
> @.Email nvarchar(100),
> @.Password nvarchar(50),
> @.ExpireDate datetime = DEFAULT,
> @.EnableNewsLetter bit,
> @.UserID int OUTPUT
>.....
you can not use the DEFAULT keyword that way as you have to provide an
explicit default and not the "DEFAULT" keyword if you want it to be used for
not provided paramenter... that's to say you have perhaps to set it as
@.ExpireDate datetime = 'some date',
if you check your code, @.ExpireDate will always be NULL if not explicit
value has been specified for that parameter...
your code is like
SET NOCOUNT ON
GO
CREATE TABLE dbo._Users (
UserID int IDENTITY
, Name nvarchar (10) --(50)
, Email nvarchar (20) --(100)
, Password varchar(10) --(50)
, ExpireDate datetime DEFAULT '20050101'
, EnableNewsletter bit DEFAULT 0
)
GO
CREATE PROC dbo.AddUser (
@.Name nvarchar(50)
, @.Email nvarchar(100)
, @.Password nvarchar(50)
, @.ExpireDate datetime = DEFAULT -- this value will never be used and the
underlaying
-- column default can not be used
, @.EnableNewsLetter bit
, @.UserID int OUTPUT
)
AS
-- SELECT @.ExpireDate always returns NULL if no explicit value is passed
INSERT INTO dbo._Users
(
Name
, Email
, Password
, ExpireDate
, EnableNewsletter
)
VALUES
(
@.Name
, @.Email
, @.Password
, @.ExpireDate
, @.EnableNewsLetter
)
SELECT @.UserID = SCOPE_IDENTITY()
GO
DECLARE @.UserId int
EXEC dbo.AddUser @.Name = 'Andrea'
, @.Email = 'andrea@.andrea.com'
, @.Password = 'aerdna'
-- , @.ExpireDate -- param not provided
, @.EnableNewsLetter = 1
, @.UserId = @.UserId OUTPUT
EXEC dbo.AddUser @.Name = 'Andrea'
, @.Email = 'andrea@.andrea.com'
, @.Password = 'aerdna'
, @.ExpireDate = NULL -- param exlicitely NULL
, @.EnableNewsLetter = 1
, @.UserId = @.UserId OUTPUT
EXEC dbo.AddUser @.Name = 'Andrea'
, @.Email = 'andrea@.andrea.com'
, @.Password = 'aerdna'
, @.ExpireDate = '20050315' -- param provided
, @.EnableNewsLetter = 1
, @.UserId = @.UserId OUTPUT
SELECT *
FROM dbo._Users
-- WHERE UserID = @.UserId
GO
DROP PROC dbo.AddUser
DROP TABLE dbo._Users
--<--
UserID Name Email Password ExpireDate
EnableNewsletter
-- -- -- -- --
-- --
1 Andrea andrea@.andrea.com aerdna NULL
1 -- no value specified
2 Andrea andrea@.andrea.com aerdna NULL
1 -- explicit NULL specified
3 Andrea andrea@.andrea.com aerdna 2005-03-15
00:00:00.000 1
but modifyng the daclaration of the sp's parameters, providing an explicit
value for that parameter like
CREATE PROC dbo.AddUser (
@.Name nvarchar(50)
, @.Email nvarchar(100)
, @.Password nvarchar(50)
, @.ExpireDate datetime = '20050101'
, @.EnableNewsLetter bit
, @.UserID int OUTPUT
)
AS
....
you will get a different result as
--<--
UserID Name Email Password ExpireDate
EnableNewsletter
-- -- -- -- --
-- --
1 Andrea andrea@.andrea.com aerdna 2005-01-01
00:00:00.000 1
2 Andrea andrea@.andrea.com aerdna NULL
1
3 Andrea andrea@.andrea.com aerdna 2005-03-15
00:00:00.000 1
you can perhaps check your parameters like
IF ISNULL ( @.ExpireDate ) BEGIN
-- set it to whatever you want
END
or execute 2 different INSERT statements depending on the IF condition, ie:
do not provide the [ExpireDate] column if you want it to default to your
CREATE TABLE column default like
IF ISNULL ( @.ExpireDate ) BEGIN
INSERT INTO dbo._Users ( Name , Email , Password , EnableNewsletter )
VALUES ...
ELSE
INSERT INTO dbo._Users ( Name , Email , Password , ExpireDate ,
EnableNewsletter ) VALUES ...
but I'd better check for ISNULL and set it accordingly to your needs, as all
you other parameters should be checked as well
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||more...
you can even query the INFORMATION_SCHEMA.COLUMNS ANSI view for columns
information like nullability and default to perform your own check and
eventual default settings...
SET NOCOUNT ON
CREATE TABLE dbo.Test (
ID int NOT NULL ,
dt datetime DEFAULT getdate()
)
GO
SELECT c.COLUMN_DEFAULT , c.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'Test'
-- AND c.COLUMN_NAME = 'dt'
GO
DROP TABLE dbo.Test
--<--
COLUMN_DEFAULT IS_NULLABLE
-- --
NULL No
(getdate()) YES
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

default data value in gridview?

Hi,

I am trying to set a default value for a date field in my update parameters:

When I try an update with the DefaultValue="<% Now %>", I get this error:
"String was not recognized as a valid DateTime."

The updates work fine if no default value is set and it also works ok if I change the default value to a set string, such as "6/24/06". I've tried using different data sources and datasets, but no luck.

Anyone have any ideas on this?

Thanks!

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:db1ConnectionString%>" ProviderName="<%$ ConnectionStrings:db1ConnectionString.ProviderName%>" SelectCommand="SELECT [ID], [Name], [Date] FROM [Table1]" DeleteCommand="DELETE FROM [Table1] WHERE [ID] = ?" InsertCommand="INSERT INTO [Table1] ([ID], [Name], [Date]) VALUES (?, ?, ?)" UpdateCommand="UPDATE [Table1] SET [Name] = ?, [Date] = ? WHERE [ID] = ?"> <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Date" Type="DateTime" DefaultValue="<%Now()%>" /> <asp:Parameter Name="ID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Date" Type="DateTime" /> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /> <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /> <asp:BoundField DataField="Date" HeaderText="Date" ReadOnly="true" SortExpression="Date" /> </Columns> </asp:GridView>

I'm not sure you're using the right way to call the function in page script, but it works if I set the default value in the code behind:

SqlDatasSource1.SelectParameters.Add("@.OD", TypeCode.DateTime, DateTime.Now.ToString());

|||Great, thanks! I don't know why I didn't think to do it that way.

Just in case anyone else runs this, I ended up using an ObjectDataSource and added this event handler to set the parameters...it works great:

Protected Sub ObjectDataSource1_Inserting(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs)Handles ObjectDataSource1.Inserting e.InputParameters("Date") = DateTime.NowEnd Sub Protected Sub ObjectDataSource1_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs)Handles ObjectDataSource1.Updating e.InputParameters("Date") = DateTime.NowEnd Sub

|||I'm glad to hear that you managed to make it works BTW someone told me the reason why your code in page script (set DefaultValue for a parameter a value returned by a function) didn't work is that the Parameter elements can not be boundsql