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 Field Value for DateTime & SmallDateTime

In SQL Server 2000 / Asp.Net I am trying to use default values for all fields; hoping to eliminate nulls.

For number and character fields, the default is pretty obvious, but is there any empty value for a date field? I think a null there might be better than putting in a bogus date, at least it can be tested for.

Are there any more developend ideas on this question?

Many thanks
Mike ThomasHi, Mike.
The choise depends directly on the problem U r solving. Sometimes GETDATE() helps... just analize Ur task and make a corresponding conclusion: what value is permitable as a default one in the definite case...

Alex.sql

Default datetime values?

I'm confused. I have two variables:
@.StartDate and @.EndDate. I have to figure out how to
execute this report each day through email grabbing
yesterdays data. I tried just for a test to put in GetDate
() and GetDate()-1 and I got an error. I'm wondering how
do I set two datetime variables for yesterday 12:00 AM to
today 12:00 AM. Please help,
Regards,
BryanBmurtha,
Try using DateAdd function like
=DateAdd(DateInterval.Day, -1, Today())
Regards,
Cem
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:46c301c47352$60dcc490$a601280a@.phx.gbl...
> I'm confused. I have two variables:
> @.StartDate and @.EndDate. I have to figure out how to
> execute this report each day through email grabbing
> yesterdays data. I tried just for a test to put in GetDate
> () and GetDate()-1 and I got an error. I'm wondering how
> do I set two datetime variables for yesterday 12:00 AM to
> today 12:00 AM. Please help,
> Regards,
> Bryan|||Or try:
=Today.AddDays(-1)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:e4cADS1cEHA.3988@.tk2msftngp13.phx.gbl...
>
> Bmurtha,
> Try using DateAdd function like
> =DateAdd(DateInterval.Day, -1, Today())
> Regards,
> Cem
>
> "bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
> news:46c301c47352$60dcc490$a601280a@.phx.gbl...
> > I'm confused. I have two variables:
> > @.StartDate and @.EndDate. I have to figure out how to
> > execute this report each day through email grabbing
> > yesterdays data. I tried just for a test to put in GetDate
> > () and GetDate()-1 and I got an error. I'm wondering how
> > do I set two datetime variables for yesterday 12:00 AM to
> > today 12:00 AM. Please help,
> >
> > Regards,
> > Bryan
>

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 datetime error

hi friends,

i have two datetime parameters in my report ... based on these parameters , am searching records ... the issue is i cant get the full datetime value

for example

i am searching records based on 03/16/2007 and 03/20/2007... i can't the records for the date 03/20/2007...

bcoz, the value of the date is '03/20/2007 00:00:00.000'

i want to get the value like this ' 03/20/2007 11:59:59 pm''

can any one help me

You can specify the time part for the parameter and retrieve the records. I mean, you can type 3/20/2007 11:59:59 PM in the parameter field.

Shyam

|||

but the issue is i just want to give my date only... like this 3/20/2007

... it has to take the time by default 11:59:59 PM ....

|||

You have to specify the expression for your date parameter in dataset. For example, if your parameter name is @.to_date in your stored procedure, the report parameter name would be to_date.

Go to Data tab and select the appropriate dataset from the dropdown and then click the Edit (...) button besides it. Go to Parameters tab and for the @.to_date parameter, give the expression for Value as:

=DateAdd(DateInterval.Second, -1, DateAdd(DateInterval.Day, 1, Parameters!to_date.Value))

This will add 1 day to your given date and then subtract 1 second from that date and this value will be passed on to the stored procdure or SQL query (whichever you have used)

Shyam

|||i am pleased to thank you shyam...thanx for your help .. i have implemented ...|||

I know this solution is definitely working. But just wanted to be perfect on this. :-) The "correct" way to do something like this is to offset 3 milliseconds instead of 1 second. HTH.

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 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 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 a parameter to todays date

How can I set the default of a datetime parameter to be todays date?
Thanks,
AidanDId you try =Today() or =Now() ?
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Aidan Garnish" <aidan.garnish@.waterstons.co.uk> schrieb im Newsbeitrag
news:OUvRiODaFHA.1088@.TK2MSFTNGP14.phx.gbl...
> How can I set the default of a datetime parameter to be todays date?
> Thanks,
> Aidan
>|||That's great cheers
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uxNaGkDaFHA.1040@.TK2MSFTNGP10.phx.gbl...
> DId you try =Today() or =Now() ?
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Aidan Garnish" <aidan.garnish@.waterstons.co.uk> schrieb im Newsbeitrag
> news:OUvRiODaFHA.1088@.TK2MSFTNGP14.phx.gbl...
> > How can I set the default of a datetime parameter to be todays date?
> >
> > Thanks,
> >
> > Aidan
> >
> >
>

Declare variable problem

DECLARE @.x varchar
Set @.x = (SELECT COUNT(*)FROM #t)
DECLARE '@.date' + @.x datetime
Lets say the count is 5
I want to DECLARE a datetime variable called @.date5
How do I do this?
Thanks in advanced
This would require dynamic sql, which would be either a pointless exercise
or a dynamic coding nightmare. What are you trying to accomplish? Why is
it important that the name also convey information/data?
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.x varchar
> Set @.x = (SELECT COUNT(*)FROM #t)
> DECLARE '@.date' + @.x datetime
> Lets say the count is 5
> I want to DECLARE a datetime variable called @.date5
> How do I do this?
> Thanks in advanced
>
|||I don't know how many dates (rows) are going to be returned and I need to be
able to dynamically decare a variable for each date returned.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> This would require dynamic sql, which would be either a pointless exercise
> or a dynamic coding nightmare. What are you trying to accomplish? Why
> is
> it important that the name also convey information/data?
> "Jim Campau" <jim_campau@.bausch.com> wrote in message
> news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>
|||This sounds like a problem with your approach to solving the problem. Since
you used the term "rows", you probably should be thinking in terms of a
table. You can search the newsgroups for the many posts regarding dynamic
sql - if you want to go down that path.
There might be a set-based approach; one that is more appropriate for a
relational dbms. If you post some details about what you are trying to
accomplish, someone might be able to provide a different approach that is
better suited to the environment.
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uqnPiWAJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> I don't know how many dates (rows) are going to be returned and I need to
be[vbcol=seagreen]
> able to dynamically decare a variable for each date returned.
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
exercise
>
|||Jim Campau wrote:
> I don't know how many dates (rows) are going to be returned and I
> need to be able to dynamically decare a variable for each date
> returned.
Can you explain the problem, the tables involved, a sample query, and
what you expect to be returned. It sounds like what you're trying to do
can be done a lot more easily.
David Gugick
Imceda Software
www.imceda.com

Declare variable problem

DECLARE @.x varchar
Set @.x = (SELECT COUNT(*)FROM #t)
DECLARE '@.date' + @.x datetime
Lets say the count is 5
I want to DECLARE a datetime variable called @.date5
How do I do this?
Thanks in advancedThis would require dynamic sql, which would be either a pointless exercise
or a dynamic coding nightmare. What are you trying to accomplish? Why is
it important that the name also convey information/data?
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.x varchar
> Set @.x = (SELECT COUNT(*)FROM #t)
> DECLARE '@.date' + @.x datetime
> Lets say the count is 5
> I want to DECLARE a datetime variable called @.date5
> How do I do this?
> Thanks in advanced
>|||I don't know how many dates (rows) are going to be returned and I need to be
able to dynamically decare a variable for each date returned.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> This would require dynamic sql, which would be either a pointless exercise
> or a dynamic coding nightmare. What are you trying to accomplish? Why
> is
> it important that the name also convey information/data?
> "Jim Campau" <jim_campau@.bausch.com> wrote in message
> news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>|||This sounds like a problem with your approach to solving the problem. Since
you used the term "rows", you probably should be thinking in terms of a
table. You can search the newsgroups for the many posts regarding dynamic
sql - if you want to go down that path.
There might be a set-based approach; one that is more appropriate for a
relational dbms. If you post some details about what you are trying to
accomplish, someone might be able to provide a different approach that is
better suited to the environment.
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uqnPiWAJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> I don't know how many dates (rows) are going to be returned and I need to
be
> able to dynamically decare a variable for each date returned.
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
exercise[vbcol=seagreen]
>|||Jim Campau wrote:
> I don't know how many dates (rows) are going to be returned and I
> need to be able to dynamically decare a variable for each date
> returned.
Can you explain the problem, the tables involved, a sample query, and
what you expect to be returned. It sounds like what you're trying to do
can be done a lot more easily.
David Gugick
Imceda Software
www.imceda.com

Declare variable problem

DECLARE @.x varchar
Set @.x = (SELECT COUNT(*)FROM #t)
DECLARE '@.date' + @.x datetime
Lets say the count is 5
I want to DECLARE a datetime variable called @.date5
How do I do this?
Thanks in advancedThis would require dynamic sql, which would be either a pointless exercise
or a dynamic coding nightmare. What are you trying to accomplish? Why is
it important that the name also convey information/data?
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.x varchar
> Set @.x = (SELECT COUNT(*)FROM #t)
> DECLARE '@.date' + @.x datetime
> Lets say the count is 5
> I want to DECLARE a datetime variable called @.date5
> How do I do this?
> Thanks in advanced
>|||I don't know how many dates (rows) are going to be returned and I need to be
able to dynamically decare a variable for each date returned.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> This would require dynamic sql, which would be either a pointless exercise
> or a dynamic coding nightmare. What are you trying to accomplish? Why
> is
> it important that the name also convey information/data?
> "Jim Campau" <jim_campau@.bausch.com> wrote in message
> news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>> DECLARE @.x varchar
>> Set @.x = (SELECT COUNT(*)FROM #t)
>> DECLARE '@.date' + @.x datetime
>> Lets say the count is 5
>> I want to DECLARE a datetime variable called @.date5
>> How do I do this?
>> Thanks in advanced
>>
>|||This sounds like a problem with your approach to solving the problem. Since
you used the term "rows", you probably should be thinking in terms of a
table. You can search the newsgroups for the many posts regarding dynamic
sql - if you want to go down that path.
There might be a set-based approach; one that is more appropriate for a
relational dbms. If you post some details about what you are trying to
accomplish, someone might be able to provide a different approach that is
better suited to the environment.
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uqnPiWAJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> I don't know how many dates (rows) are going to be returned and I need to
be
> able to dynamically decare a variable for each date returned.
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> > This would require dynamic sql, which would be either a pointless
exercise
> > or a dynamic coding nightmare. What are you trying to accomplish? Why
> > is
> > it important that the name also convey information/data?
> >
> > "Jim Campau" <jim_campau@.bausch.com> wrote in message
> > news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> >> DECLARE @.x varchar
> >> Set @.x = (SELECT COUNT(*)FROM #t)
> >>
> >> DECLARE '@.date' + @.x datetime
> >>
> >> Lets say the count is 5
> >> I want to DECLARE a datetime variable called @.date5
> >> How do I do this?
> >>
> >> Thanks in advanced
> >>
> >>
> >
> >
>|||Jim Campau wrote:
> I don't know how many dates (rows) are going to be returned and I
> need to be able to dynamically decare a variable for each date
> returned.
Can you explain the problem, the tables involved, a sample query, and
what you expect to be returned. It sounds like what you're trying to do
can be done a lot more easily.
--
David Gugick
Imceda Software
www.imceda.com

Declare @var?

I am trying to get a grasp on the Sql Stored procedures it seems i dont really understnad what DECLARE @.Date DateTime means? I mean i think it means that i am just declaring a varible name Date that will hold a DateTime Value? is that correct or is it more to it?

CREATE PROCEDURE dbo.Tracking_GetStatus
AS
DECLARE @.Date DateTime
DECLARE @.Begining DateTime
DECLARE @.Ending DateTime
SET @.Date = GETDATE()
SET @.Begining = DATEADD(ss,(DATEPART(ss,@.Date)*-1),
DATEADD(mi,(DATEPART(mi,@.Date)*-1),
DATEADD(hh,(DATEPART(hh,@.Date)*-1),@.Date)))
SET @.Ending = DATEADD(ss,-1,
DATEADD(dd,1,DATEADD(ss,(DATEPART(ss,@.Date)*-1),
DATEADD(mi,(DATEPART(mi,@.Date)*-1),
DATEADD(hh,(DATEPART(hh,@.Date)*-1),@.Date)))))

SELECT
Vehicl,
UpdateTi
XCoord,
YCoord,
Status
FROM Track
WHERE UpdateTime >= @.Begining
AND UpdateTime <= @.Ending
RETURN

GO

You are correct. The DECLARE statement declares variables in T-SQL. All variables MUST be declared, and they only are only scoped (available) to the procedure or batch in which they are declared. The variable will initially contain NULL as it's value and needs to be initialized for use, but you did that with each of your SET statements.

Everything looks fine. Are you having a problem?

Declaration of record variable

Can someone say how to declare a record like variable in MSSQL-2000 Like:
mr_rec record of variables a int, b char(20), c datetime? I could not find any examples on BOL. I want to use this in a stored procedure create script.
Thanks, Vinniethere is no such thing as a "record" variable in mssql.

You can declare a table variable however, this might meet your need. A table variable is basically a temp table. See BOL for more info on table variables.

decimal to datetime puzzler

Ok, here's a puzzler for you...

It's the classic story; I have inherited a system etc etc which stores timesheet information.

The design is, as you'd expect, "intriguing".

There are fields to store the number of hours worked as a decimal; Rate1 & Rate2
There are also fields used throughout the application which are the datetime equivalents (from the base date) of these times; Eng_Hours_Rate1 & Eng_Hours_Rate2.

The problem is, the Eng_Hours_Rate fields do not get updated when a user changes the Rate value(s).

Now because I don't know the language the FE is programmed in too well, I thought I'd stick to something I am a bit better in.

So I thought - UPDATE TRIGGER!

However, I can't get my head around the conversion calculation - below shows the closest I have got yet. When I set the Rate field to 2.5, I get the datetime value 1900-01-01 02:29:59.940 which is very close, but not quite!

Any ideas on what to do to get this working are greatly appreciated :D

Here's the ddl of the relevant fields

--Create mock table
CREATE TABLE timesheet_mod (
UniqueID int identity(1,1) primary key NOT NULL
, Eng_Hours_Rate1 datetime NULL
, Eng_Hours_Rate2 datetime NULL
, Rate1 decimal(5,2) NULL
, Rate2 decimal(5,2) NULL
)

Here's some test data

--Insert test data
SET NOCOUNT ON
INSERT INTO timesheet_mod (Eng_Hours_Rate1, Eng_Hours_Rate2, Rate1, Rate2)
SELECT '1900-01-01 01:30:00.000','1900-01-01 01:00:00.000',1.50 ,1.00 UNION ALL
SELECT '1900-01-01 03:00:00.000','1900-01-01 00:00:00.000',3.00 ,0.00 UNION ALL
SELECT '1900-01-01 04:30:00.000','1900-01-01 00:30:00.000',4.50 ,0.50
SET NOCOUNT OFF
GO

Here's my attempt at the trigger

CREATE TRIGGER rate_Update
ON timesheet_mod
FOR UPDATE
AS
UPDATE timesheet_mod
SET eng_hours_rate1 = (u.rate1 / 24)
, eng_hours_rate2 = (u.rate2 / 24)
FROM timesheet_mod t
INNER
JOIN inserted u
ON u.uniqueid = t.uniqueid
WHERE t.UniqueID IN (SELECT UniqueID FROM inserted)
GO

And here's the test bit followed by a cleanup

SELECT * FROM timesheet_mod WHERE uniqueid IN (1,2,3)

UPDATE timesheet_mod
SET rate1 = 2.5
WHERE uniqueid IN (1,2)

SELECT * FROM timesheet_mod WHERE uniqueid IN (1,2,3)

--Clean up
DROP TRIGGER rate1_Update
DROP TABLE timesheet_mod

If I've not made this clear, or you fancy insulting my methods vigorously - please feel free ;)

EDIT: In theory the users should only ever be entering timesheet entries accurate to the nearest 1/4 of an hour (or so I'm told, ha!)Depends on the minimum unit you use perhaps.

DECLARE @.date AS DATETIME
, @.dec AS DECIMAL(5, 2)

SELECT @.date = 0
, @.dec = 2.5

SELECT @.date
, @.dec

SELECT @.dec = @.dec * 60
, @.date = DATEADD(mi, @.dec, @.date)

SELECT @.date|||Solved it

CREATE TRIGGER rate_Update
ON timesheet_mod
FOR UPDATE
AS
UPDATE timesheet_mod
SET eng_hours_rate1 = DateAdd(mi, u.rate1 * 60, 0)
, eng_hours_rate2 = DateAdd(mi, u.rate2 * 60, 0)
FROM timesheet_mod t
INNER
JOIN inserted u
ON u.uniqueid = t.uniqueid
WHERE t.UniqueID IN (SELECT UniqueID FROM inserted)
GO|||Solved itBlooming cheek :p|||Ha! I didn't realise you had posted that Poots - wrapped up in my own little bubble over here today :D

According to my business rules this should be AOK - but can obviously modified to be more accurate by using

DateAdd(ss, u.rate1 * 3600, 0)

Etc.

Appreciate the help Poots :)|||No probs. I suppose it is unlikely that anyone would bother entering a decimal representation of an hour that goes down to seconds but you never know...|||I've been assured that the FE won't allow it - but we all know what that really means.

Nice to have you back Poots :)

Debugging Query Analyzer & dates

Two of the sprocs input parameters are datetime. When I try to debug the
sproc SQL Server errors with
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
I've tried everything (except of course what might actually work).
Help!!!
regards
Frank Ashley
Try any of the ODBC styles, as defined in:
http://msdn.microsoft.com/library/en...on_03_04l0.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:uG9xu73gEHA.632@.TK2MSFTNGP12.phx.gbl...
> Two of the sprocs input parameters are datetime. When I try to debug the
> sproc SQL Server errors with
>
> [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
> specification
>
> I've tried everything (except of course what might actually work).
>
> Help!!!
> regards
> Frank Ashley
>
|||That worked first time.
Thanks
Frank Ashley
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OZ0mPj4gEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Try any of the ODBC styles, as defined in:
> http://msdn.microsoft.com/library/en...on_03_04l0.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> news:uG9xu73gEHA.632@.TK2MSFTNGP12.phx.gbl...
>

Debug with datetime datatype

Hi,
When I debug a stored procedure, if one of the input parameters is
datetime datatype, it doesn't matter what I type in, i got an error said
"Invalid character value for cast specification" Any idea what value I need
to key in?
example
create procedure ztest
@.Input smalldatetime
as
If @.input = '5/1/2005'
print 'Good'
Else
print 'Bad'
in debug mode, i key in '5/1/2005' for @.Input, but error occurs.
Thanks
EdI believe that one of below formats will work:
{ ts '1998-05-02 01:23:56.123' }
{ d '1990-10-02' }
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:62386CA6-E369-4D53-9BB6-13A80A175E53@.microsoft.com...
> Hi,
> When I debug a stored procedure, if one of the input parameters is
> datetime datatype, it doesn't matter what I type in, i got an error said
> "Invalid character value for cast specification" Any idea what value I ne
ed
> to key in?
> example
> create procedure ztest
> @.Input smalldatetime
> as
> If @.input = '5/1/2005'
> print 'Good'
> Else
> print 'Bad'
> in debug mode, i key in '5/1/2005' for @.Input, but error occurs.
> Thanks
> Ed
>|||Ed,
It's not you. The debugger data entry for datetime is buggy. ;)
Use the format
YYYY-MM-DD HH:MM:00.000, or for your date
2005-05-01 00:00:00.000
Put all the zeros in, even though you just have a smalldatetime (even if
you had datetime, you need to make them zero, because the debugger
doesn't handle more precision).
Here are a couple of old newsgroup threads on this bug:
http://groups-beta.google.com/group...ldatetime&hl=en
Steve Kass
Drew University
Ed wrote:

>Hi,
> When I debug a stored procedure, if one of the input parameters is
>datetime datatype, it doesn't matter what I type in, i got an error said
>"Invalid character value for cast specification" Any idea what value I nee
d
>to key in?
>example
>create procedure ztest
> @.Input smalldatetime
>as
>If @.input = '5/1/2005'
> print 'Good'
>Else
> print 'Bad'
>in debug mode, i key in '5/1/2005' for @.Input, but error occurs.
>Thanks
>Ed
>
>