DateTime.Now expression expected problem

Hi - I'm using VWD, VB, and created a dataset/tableadapter to insert a record into a SQL Express database. The database has a couple of columns, but specifically a Datetime column.

Using the default insert created, I have the following code:

Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapter
Profile.partyid = da.Insert(Profile.UserName, tbName.Text, DateTime.Now)

The compiler throws an error though, saying 'Expression expected' - and it squiggles an underline under the closing bracket after DateTime.Now - I have no problem if I'm trying to update a record using:

Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapter
Dim pd as partyDetails.partyDetailsDataTable
pd = da.GetPartyDetailsByID(Profile.partyid)
da.Update(Profile.UserName, tbName.text, DateTime.Now, Profile.partyid, Profile.partyid)

Have I an error in my Insert section?

Thanks for any help,

Mark

Look at what the functions da.Insert and ds.Update are expecting as their arguments.

Maybe daInsert is expecting the date as a string instead of a DateTime object

datetime problem

my asp .net application is coming along nicely. however, i would like to record the users last login time. i have a datetime field, and when i insert or update using Now() as the data for the field, i simply get 1/1/1900 12:00:00 in the last_login field. Same thing using Today(). any suggestions? am i using the wrong data type? i need to be able to do date comparisons as i plan on connecting my site with a forum, and this would be the easiest way to determine if there were new posts from the user's perspective.

TIA

Use GetDate() in your SQL to input the current time.|||

Unless your business object has a say in what the date/time stamp value is, there is little reason to send it over the network both ways. Might as well have the database provide the value and hand it back to you.

|||

thanks mikesdotnetting! worked like a charm!

|||

Don't forget that getdate(0 and Now() work on different servers which can be in different time zones :)

Datetime comparsion

Hi,
i need to create a sql statement to find out record between a period, but i
only want to search with the date part only (e.g. 2006/1/23).
In my database, the type of the field is in datetime format. How can i
bypass the time part but still can find out what i need (e.g. 2006/1/23
00:00:00 - 2006/1/23 23:59:59)
sample SQL statement
select * from [table] where [datetime] between [fromdate] and [todate]
Apologies for non-good english, hope you get what i mean.
Thanks and Best Regards,
Sexball20060123 = 20060123 00:00
and
DATEADD(ss,-1,'20060124') = 20060123 23:59:59
select * from [table] where [datetime] between '20060123' and
DATEADD(ss,-1,'20060124')
HTH. Ryan
"sexball" <sexball@.sexball.com> wrote in message
news:%23vw7jBAIGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hi,
> i need to create a sql statement to find out record between a period, but
> i only want to search with the date part only (e.g. 2006/1/23).
> In my database, the type of the field is in datetime format. How can i
> bypass the time part but still can find out what i need (e.g. 2006/1/23
> 00:00:00 - 2006/1/23 23:59:59)
> sample SQL statement
> select * from [table] where [datetime] between [fromdate] and [todate]
> Apologies for non-good english, hope you get what i mean.
>
> Thanks and Best Regards,
> Sexball
>|||This will work,
SELECT getdate()
SELECT CAST(FLOOR(CAST( getdate() AS float)) AS DATETIME)
Thanks,
Sree
"sexball" wrote:

> Hi,
> i need to create a sql statement to find out record between a period, but
i
> only want to search with the date part only (e.g. 2006/1/23).
> In my database, the type of the field is in datetime format. How can i
> bypass the time part but still can find out what i need (e.g. 2006/1/23
> 00:00:00 - 2006/1/23 23:59:59)
> sample SQL statement
> select * from [table] where [datetime] between [fromdate] and [todate]
> Apologies for non-good english, hope you get what i mean.
>
> Thanks and Best Regards,
> Sexball
>
>|||Hi
You can try as:
select * from [table] where [datetime]
between cast([fromdate] as datetime) and cast([todate] as datetime)
I am not sure what you are looking for. But please let me know if this helps
you.
Please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"sexball" wrote:

> Hi,
> i need to create a sql statement to find out record between a period, but
i
> only want to search with the date part only (e.g. 2006/1/23).
> In my database, the type of the field is in datetime format. How can i
> bypass the time part but still can find out what i need (e.g. 2006/1/23
> 00:00:00 - 2006/1/23 23:59:59)
> sample SQL statement
> select * from [table] where [datetime] between [fromdate] and [todate]
> Apologies for non-good english, hope you get what i mean.
>
> Thanks and Best Regards,
> Sexball
>
>|||Thx Ryan, problem solved.
"Ryan" <Ryan_Waight@.nospam.hotmail.com> glsD:%23geO$GAIGHA.3056@.TK2MSFTNGP09.phx.
gbl...
> 20060123 = 20060123 00:00
> and
> DATEADD(ss,-1,'20060124') = 20060123 23:59:59
>
> select * from [table] where [datetime] between '20060123' and
> DATEADD(ss,-1,'20060124')
>
> --
> HTH. Ryan
> "sexball" <sexball@.sexball.com> wrote in message
> news:%23vw7jBAIGHA.1192@.TK2MSFTNGP11.phx.gbl...
>|||hi this
SELECT CONVERT(DATETIME,CONVERT(CHAR(10),GETDAT
E(),120),120)
?
"Chandra" <chandra@.discussions.microsoft.com> д?
news:6C1E767F-4026-4752-BA9F-5210A3274F81@.microsoft.com...
> Hi
> You can try as:
> select * from [table] where [datetime]
> between cast([fromdate] as datetime) and cast([todate] as datetime)
> I am not sure what you are looking for. But please let me know if this
helps
> you.
> Please let me know if you have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "sexball" wrote:
>
but i

datetime column formula

Hello all:

Using EM to add a column Date_Entered with data type dateTime, what is the syntax to default the date to current date when record is added and to ensure it does not update if the record is modified at some other time in the future. Is it also possible to exclude the time when the column is updated (instead of 4/18/2003 9:32:56 PM the colum would be derived as 4/18/2003)

Is there a publication with listing of all legal suntax used in SQL2000?

Thank youbol has the syntax.

I don't advise using e-m to update the schema.

The sql would be something like

alter table x add dte datetime not null default convert(varchar(8),getdate(),112)

The column is not updated - only defaulted on insert.

If you want it to be set to the current date on update you can do it in a trigger.

Datetimes always include a time - the above will set it to midnight. It is up to you the format in which you display it.

DateTime

I have an application that is sending the date with the following format dd/mm/yyyy the field in the table in sql is a datetime and the record is not being inserted. If I manually change the value in the query analyzer to mm/dd/yyyy the record is inserte
d successfully. How can I change the data type to accept the values as is
Thanks
Have you check what the dateformat for the language you are using. You can
do this by issuing the following command:
sp_helplanguage @.@.language
If you want a different format then your langauge format you can change it
by using the SET DATEFORMAT.
Here is an example of where I used two different language. In these
examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
Hopefully this will give you some ideas on how to fix you problem.
set language us_english
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
set language British
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> I have an application that is sending the date with the following format
dd/mm/yyyy the field in the table in sql is a datetime and the record is not
being inserted. If I manually change the value in the query analyzer to
mm/dd/yyyy the record is inserted successfully. How can I change the data
type to accept the values as is
> Thanks
|||does the set language command do that at the server level or database level or table level or field level?
can I change the datetime field in my db table to accept european time?
"Gregory A. Larsen" wrote:

> Have you check what the dateformat for the language you are using. You can
> do this by issuing the following command:
> sp_helplanguage @.@.language
> If you want a different format then your langauge format you can change it
> by using the SET DATEFORMAT.
> Here is an example of where I used two different language. In these
> examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> Hopefully this will give you some ideas on how to fix you problem.
> set language us_english
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> set language British
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> dd/mm/yyyy the field in the table in sql is a datetime and the record is not
> being inserted. If I manually change the value in the query analyzer to
> mm/dd/yyyy the record is inserted successfully. How can I change the data
> type to accept the values as is
>
>
|||I'm not sure what you are trying to do, but I don't think you want to mess
with your language setting. The "set language" command is only in affect
for the session. What I really think you need is to use the "set
dateformat" statement to control the format of your input data. Sorry for
the confusion. Something like this:
create table x(d datetime)
set dateformat dmy
insert into x values ('30/06/2002')
set dateformat mdy
insert into x values ('09/30/2002')
select * from x
drop table x
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> does the set language command do that at the server level or database
level or table level or field level?[vbcol=seagreen]
> can I change the datetime field in my db table to accept european time?
> "Gregory A. Larsen" wrote:
can[vbcol=seagreen]
it
> ----
--
> ----
--[vbcol=seagreen]
format[vbcol=seagreen]
not[vbcol=seagreen]
data[vbcol=seagreen]
|||Well, this is a vendor product. you're right, I don't want to change the language The tables are already created and i can't make any changes to the front end. It is a european vendor. The system came with an MSDE database. I am trying to switch it to
an enterprise version of sql 2000. When I made the switch, data wasn't being written to the main table. After running a trace, I noticed that the insert to that table was failing because the datetime that was being inserted was in European format and t
he datetime field was rejecting that. Changing the data type to varchar allowed the insert but I don't want to keep it as varchar obviously. Can this issue be fixed with some change to the Datetime field (like some formula or something), any other sugge
stions? I am not sure where to use set dateformat
thanks
"Gregory A. Larsen" wrote:

> I'm not sure what you are trying to do, but I don't think you want to mess
> with your language setting. The "set language" command is only in affect
> for the session. What I really think you need is to use the "set
> dateformat" statement to control the format of your input data. Sorry for
> the confusion. Something like this:
>
> create table x(d datetime)
> set dateformat dmy
> insert into x values ('30/06/2002')
> set dateformat mdy
> insert into x values ('09/30/2002')
> select * from x
> drop table x
>
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> level or table level or field level?
> can
> it
> --
> --
> format
> not
> data
>
>
|||http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:ABC7EAEA-A16B-42D9-84B7-A1B4FE9710DB@.microsoft.com...
> Well, this is a vendor product. you're right, I don't want to change the language The tables are
already created and i can't make any changes to the front end. It is a european vendor. The system
came with an MSDE database. I am trying to switch it to an enterprise version of sql 2000. When I
made the switch, data wasn't being written to the main table. After running a trace, I noticed that
the insert to that table was failing because the datetime that was being inserted was in European
format and the datetime field was rejecting that. Changing the data type to varchar allowed the
insert but I don't want to keep it as varchar obviously. Can this issue be fixed with some change
to the Datetime field (like some formula or something), any other suggestions? I am not sure where
to use set dateformat[vbcol=seagreen]
> thanks
>
> "Gregory A. Larsen" wrote:
|||> Well, this is a vendor product.
You should give constructive feedback to the vendor that they are idiots for
relying on regional date formats like d/m/y or m/d/y.
http://www.aspfaq.com/
(Reverse address to reply.)

DateTime

I have an application that is sending the date with the following format dd/mm/yyyy the field in the table in sql is a datetime and the record is not being inserted. If I manually change the value in the query analyzer to mm/dd/yyyy the record is inserted successfully. How can I change the data type to accept the values as is
ThanksHave you check what the dateformat for the language you are using. You can
do this by issuing the following command:
sp_helplanguage @.@.language
If you want a different format then your langauge format you can change it
by using the SET DATEFORMAT.
Here is an example of where I used two different language. In these
examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
Hopefully this will give you some ideas on how to fix you problem.
set language us_english
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
set language British
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> I have an application that is sending the date with the following format
dd/mm/yyyy the field in the table in sql is a datetime and the record is not
being inserted. If I manually change the value in the query analyzer to
mm/dd/yyyy the record is inserted successfully. How can I change the data
type to accept the values as is
> Thanks|||does the set language command do that at the server level or database level or table level or field level?
can I change the datetime field in my db table to accept european time?
"Gregory A. Larsen" wrote:
> Have you check what the dateformat for the language you are using. You can
> do this by issuing the following command:
> sp_helplanguage @.@.language
> If you want a different format then your langauge format you can change it
> by using the SET DATEFORMAT.
> Here is an example of where I used two different language. In these
> examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> Hopefully this will give you some ideas on how to fix you problem.
> set language us_english
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> set language British
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> > I have an application that is sending the date with the following format
> dd/mm/yyyy the field in the table in sql is a datetime and the record is not
> being inserted. If I manually change the value in the query analyzer to
> mm/dd/yyyy the record is inserted successfully. How can I change the data
> type to accept the values as is
> >
> > Thanks
>
>|||I'm not sure what you are trying to do, but I don't think you want to mess
with your language setting. The "set language" command is only in affect
for the session. What I really think you need is to use the "set
dateformat" statement to control the format of your input data. Sorry for
the confusion. Something like this:
create table x(d datetime)
set dateformat dmy
insert into x values ('30/06/2002')
set dateformat mdy
insert into x values ('09/30/2002')
select * from x
drop table x
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> does the set language command do that at the server level or database
level or table level or field level?
> can I change the datetime field in my db table to accept european time?
> "Gregory A. Larsen" wrote:
> > Have you check what the dateformat for the language you are using. You
can
> > do this by issuing the following command:
> >
> > sp_helplanguage @.@.language
> >
> > If you want a different format then your langauge format you can change
it
> > by using the SET DATEFORMAT.
> >
> > Here is an example of where I used two different language. In these
> > examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> > Hopefully this will give you some ideas on how to fix you problem.
> >
> > set language us_english
> > exec sp_helplanguage @.@.language
> > create table x(d datetime)
> > insert into x values ('30/06/2002')
> > insert into x values ('09/30/2002')
> > select * from x
> > drop table x
> >
> > set language British
> > exec sp_helplanguage @.@.language
> > create table x(d datetime)
> > insert into x values ('30/06/2002')
> > insert into x values ('09/30/2002')
> > select * from x
> > drop table x
> > --
> >
> ----
--
> ----
--
> > --
> >
> > Need SQL Server Examples check out my website at
> > http://www.geocities.com/sqlserverexamples
> > "Niles" <Niles@.discussions.microsoft.com> wrote in message
> > news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> > > I have an application that is sending the date with the following
format
> > dd/mm/yyyy the field in the table in sql is a datetime and the record is
not
> > being inserted. If I manually change the value in the query analyzer to
> > mm/dd/yyyy the record is inserted successfully. How can I change the
data
> > type to accept the values as is
> > >
> > > Thanks
> >
> >
> >|||Well, this is a vendor product. you're right, I don't want to change the language The tables are already created and i can't make any changes to the front end. It is a european vendor. The system came with an MSDE database. I am trying to switch it to an enterprise version of sql 2000. When I made the switch, data wasn't being written to the main table. After running a trace, I noticed that the insert to that table was failing because the datetime that was being inserted was in European format and the datetime field was rejecting that. Changing the data type to varchar allowed the insert but I don't want to keep it as varchar obviously. Can this issue be fixed with some change to the Datetime field (like some formula or something), any other suggestions? I am not sure where to use set dateformat
thanks
"Gregory A. Larsen" wrote:
> I'm not sure what you are trying to do, but I don't think you want to mess
> with your language setting. The "set language" command is only in affect
> for the session. What I really think you need is to use the "set
> dateformat" statement to control the format of your input data. Sorry for
> the confusion. Something like this:
>
> create table x(d datetime)
> set dateformat dmy
> insert into x values ('30/06/2002')
> set dateformat mdy
> insert into x values ('09/30/2002')
> select * from x
> drop table x
>
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> > does the set language command do that at the server level or database
> level or table level or field level?
> > can I change the datetime field in my db table to accept european time?
> >
> > "Gregory A. Larsen" wrote:
> >
> > > Have you check what the dateformat for the language you are using. You
> can
> > > do this by issuing the following command:
> > >
> > > sp_helplanguage @.@.language
> > >
> > > If you want a different format then your langauge format you can change
> it
> > > by using the SET DATEFORMAT.
> > >
> > > Here is an example of where I used two different language. In these
> > > examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> > > Hopefully this will give you some ideas on how to fix you problem.
> > >
> > > set language us_english
> > > exec sp_helplanguage @.@.language
> > > create table x(d datetime)
> > > insert into x values ('30/06/2002')
> > > insert into x values ('09/30/2002')
> > > select * from x
> > > drop table x
> > >
> > > set language British
> > > exec sp_helplanguage @.@.language
> > > create table x(d datetime)
> > > insert into x values ('30/06/2002')
> > > insert into x values ('09/30/2002')
> > > select * from x
> > > drop table x
> > > --
> > >
> >
> > ----
> --
> >
> > ----
> --
> > > --
> > >
> > > Need SQL Server Examples check out my website at
> > > http://www.geocities.com/sqlserverexamples
> > > "Niles" <Niles@.discussions.microsoft.com> wrote in message
> > > news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> > > > I have an application that is sending the date with the following
> format
> > > dd/mm/yyyy the field in the table in sql is a datetime and the record is
> not
> > > being inserted. If I manually change the value in the query analyzer to
> > > mm/dd/yyyy the record is inserted successfully. How can I change the
> data
> > > type to accept the values as is
> > > >
> > > > Thanks
> > >
> > >
> > >
>
>|||http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:ABC7EAEA-A16B-42D9-84B7-A1B4FE9710DB@.microsoft.com...
> Well, this is a vendor product. you're right, I don't want to change the language The tables are
already created and i can't make any changes to the front end. It is a european vendor. The system
came with an MSDE database. I am trying to switch it to an enterprise version of sql 2000. When I
made the switch, data wasn't being written to the main table. After running a trace, I noticed that
the insert to that table was failing because the datetime that was being inserted was in European
format and the datetime field was rejecting that. Changing the data type to varchar allowed the
insert but I don't want to keep it as varchar obviously. Can this issue be fixed with some change
to the Datetime field (like some formula or something), any other suggestions? I am not sure where
to use set dateformat
> thanks
>
> "Gregory A. Larsen" wrote:
> > I'm not sure what you are trying to do, but I don't think you want to mess
> > with your language setting. The "set language" command is only in affect
> > for the session. What I really think you need is to use the "set
> > dateformat" statement to control the format of your input data. Sorry for
> > the confusion. Something like this:
> >
> >
> > create table x(d datetime)
> > set dateformat dmy
> > insert into x values ('30/06/2002')
> > set dateformat mdy
> > insert into x values ('09/30/2002')
> > select * from x
> > drop table x
> >
> >
> > --
> >
> > ----
> > ----
> > --
> >
> > Need SQL Server Examples check out my website at
> > http://www.geocities.com/sqlserverexamples
> > "Niles" <Niles@.discussions.microsoft.com> wrote in message
> > news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> > > does the set language command do that at the server level or database
> > level or table level or field level?
> > > can I change the datetime field in my db table to accept european time?
> > >
> > > "Gregory A. Larsen" wrote:
> > >
> > > > Have you check what the dateformat for the language you are using. You
> > can
> > > > do this by issuing the following command:
> > > >
> > > > sp_helplanguage @.@.language
> > > >
> > > > If you want a different format then your langauge format you can change
> > it
> > > > by using the SET DATEFORMAT.
> > > >
> > > > Here is an example of where I used two different language. In these
> > > > examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> > > > Hopefully this will give you some ideas on how to fix you problem.
> > > >
> > > > set language us_english
> > > > exec sp_helplanguage @.@.language
> > > > create table x(d datetime)
> > > > insert into x values ('30/06/2002')
> > > > insert into x values ('09/30/2002')
> > > > select * from x
> > > > drop table x
> > > >
> > > > set language British
> > > > exec sp_helplanguage @.@.language
> > > > create table x(d datetime)
> > > > insert into x values ('30/06/2002')
> > > > insert into x values ('09/30/2002')
> > > > select * from x
> > > > drop table x
> > > > --
> > > >
> > >
> > > ----
> > --
> > >
> > > ----
> > --
> > > > --
> > > >
> > > > Need SQL Server Examples check out my website at
> > > > http://www.geocities.com/sqlserverexamples
> > > > "Niles" <Niles@.discussions.microsoft.com> wrote in message
> > > > news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> > > > > I have an application that is sending the date with the following
> > format
> > > > dd/mm/yyyy the field in the table in sql is a datetime and the record is
> > not
> > > > being inserted. If I manually change the value in the query analyzer to
> > > > mm/dd/yyyy the record is inserted successfully. How can I change the
> > data
> > > > type to accept the values as is
> > > > >
> > > > > Thanks
> > > >
> > > >
> > > >
> >
> >
> >|||> Well, this is a vendor product.
You should give constructive feedback to the vendor that they are idiots for
relying on regional date formats like d/m/y or m/d/y.
--
http://www.aspfaq.com/
(Reverse address to reply.)

DateTime

I have an application that is sending the date with the following format dd/
mm/yyyy the field in the table in sql is a datetime and the record is not be
ing inserted. If I manually change the value in the query analyzer to mm/dd
/yyyy the record is inserte
d successfully. How can I change the data type to accept the values as is
ThanksHave you check what the dateformat for the language you are using. You can
do this by issuing the following command:
sp_helplanguage @.@.language
If you want a different format then your langauge format you can change it
by using the SET DATEFORMAT.
Here is an example of where I used two different language. In these
examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
Hopefully this will give you some ideas on how to fix you problem.
set language us_english
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
set language British
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> I have an application that is sending the date with the following format
dd/mm/yyyy the field in the table in sql is a datetime and the record is not
being inserted. If I manually change the value in the query analyzer to
mm/dd/yyyy the record is inserted successfully. How can I change the data
type to accept the values as is
> Thanks|||does the set language command do that at the server level or database level
or table level or field level?
can I change the datetime field in my db table to accept european time?
"Gregory A. Larsen" wrote:

> Have you check what the dateformat for the language you are using. You ca
n
> do this by issuing the following command:
> sp_helplanguage @.@.language
> If you want a different format then your langauge format you can change it
> by using the SET DATEFORMAT.
> Here is an example of where I used two different language. In these
> examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> Hopefully this will give you some ideas on how to fix you problem.
> set language us_english
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> set language British
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> dd/mm/yyyy the field in the table in sql is a datetime and the record is n
ot
> being inserted. If I manually change the value in the query analyzer to
> mm/dd/yyyy the record is inserted successfully. How can I change the data
> type to accept the values as is
>
>|||I'm not sure what you are trying to do, but I don't think you want to mess
with your language setting. The "set language" command is only in affect
for the session. What I really think you need is to use the "set
dateformat" statement to control the format of your input data. Sorry for
the confusion. Something like this:
create table x(d datetime)
set dateformat dmy
insert into x values ('30/06/2002')
set dateformat mdy
insert into x values ('09/30/2002')
select * from x
drop table x
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> does the set language command do that at the server level or database
level or table level or field level?
> can I change the datetime field in my db table to accept european time?
> "Gregory A. Larsen" wrote:
>
can[vbcol=seagreen]
it[vbcol=seagreen]
> ----
--
> ----
--[vbcol=seagreen]
format[vbcol=seagreen]
not[vbcol=seagreen]
data[vbcol=seagreen]|||Well, this is a vendor product. you're right, I don't want to change the la
nguage The tables are already created and i can't make any changes to the fr
ont end. It is a european vendor. The system came with an MSDE database.
I am trying to switch it to
an enterprise version of sql 2000. When I made the switch, data wasn't bein
g written to the main table. After running a trace, I noticed that the inse
rt to that table was failing because the datetime that was being inserted wa
s in European format and t
he datetime field was rejecting that. Changing the data type to varchar all
owed the insert but I don't want to keep it as varchar obviously. Can this
issue be fixed with some change to the Datetime field (like some formula or
something), any other sugge
stions? I am not sure where to use set dateformat
thanks
"Gregory A. Larsen" wrote:

> I'm not sure what you are trying to do, but I don't think you want to mess
> with your language setting. The "set language" command is only in affect
> for the session. What I really think you need is to use the "set
> dateformat" statement to control the format of your input data. Sorry fo
r
> the confusion. Something like this:
>
> create table x(d datetime)
> set dateformat dmy
> insert into x values ('30/06/2002')
> set dateformat mdy
> insert into x values ('09/30/2002')
> select * from x
> drop table x
>
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> level or table level or field level?
> can
> it
> --
> --
> format
> not
> data
>
>|||http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:ABC7EAEA-A16B-42D9-84B7-A1B4FE9710DB@.microsoft.com...
> Well, this is a vendor product. you're right, I don't want to change the language
The tables are
already created and i can't make any changes to the front end. It is a euro
pean vendor. The system
came with an MSDE database. I am trying to switch it to an enterprise versi
on of sql 2000. When I
made the switch, data wasn't being written to the main table. After running
a trace, I noticed that
the insert to that table was failing because the datetime that was being ins
erted was in European
format and the datetime field was rejecting that. Changing the data type to
varchar allowed the
insert but I don't want to keep it as varchar obviously. Can this issue be
fixed with some change
to the Datetime field (like some formula or something), any other suggestion
s? I am not sure where
to use set dateformat[vbcol=seagreen]
> thanks
>
> "Gregory A. Larsen" wrote:
>|||> Well, this is a vendor product.
You should give constructive feedback to the vendor that they are idiots for
relying on regional date formats like d/m/y or m/d/y.
http://www.aspfaq.com/
(Reverse address to reply.)

Dates when inserting a record

Is it possible to have sql server automatically record date and time (in a designated field)when a record is created in the db? This may seam basic but it has caused me a lot of grief.You can use database trigger for automatic firing.|||yes it can
just use when creating the table for that field a default

something like this:

create table xxx (
id int not null,
dateadded datetime not null default(getdate())
)|||so if I used get date () in the field and queried the table. Will it show me the date and time as of that momnet or will it have embedded the date and time the record was added...|||getdate() is a function that returns the current date when the record is actually added. it's not a column name. so if u want to query the date when the record was added, u need to query that column.|||getdate() is not working. It works in the sense that it displays system date and time. The problem is that it updates every column whenever I view the table or query it...

I need an option which will ensure that each record added inserts into the designated Table/Field the actual system time at which the record was inserted.|||Foefie's solution should work for you. Specifying the Default value of a column to be GETDATE() will insert the SQL Server's current system date/time into the column when the record is inserted into the database. It willl not fluctuate or vary with queries -- it is concretely written into the record. We use this approach all of the time without incident.

Terri|||If you use GETDATE() as default value in the column, you can control what happens.

What I mean by that is, if you want the date to reflect the datetime of last change, then you can include the field in any update, but specify DEFAULT as the value to be inserted. This will then cause the date to be update.

Alternatively (if you don't want it to change on every update, and always reflect the original datetime when the record was created), you should omit the field from any update statements and it will remain as originally inserted (you don't need to specify the field on any insert statements either, since on the first insert any fields for which you don't explicitly specify a value should get the default)

HTH

Anton|||Thank you. This explains it much better. The confusion has been due to the fact that while using Enterprise Manager, rightclicking on the table in question and selecting return all rows, I have noticed that it always updates the date and time. I just assumed that the same would apply anytime I queried the data in the table.

Thanks!

Dates Error

Hi:

I got the next problem, when I try to modify a record of my SQL Server Database from my Delphi application the next message error appears

"Date is less than 01/12/2003"

The record that I'm trying to modify was inserted from the same applicaition.

I'm not so sure if it's a database problem, but I don't know why it is passing. What can I do?

Thaks for your help!!

Cristopher SerratoNope,

Probably someone wrote a trigger to check to rows modified date. Someone probably updated the row since you got it last..

Your update has to supply a date if I'm not mistaken...

They basically want you to requery the data so you can work with th most current version of data...

Just a guess...

Dates - information entered 3 months ago

Hello All,
I need to create stored procedure that will output information created 3
months after the record was created. For example: if the stored procedure was
run today or based on a date parameter I would like it to output all records
created 3 months ago to that day. There are other parameters I need,but I
think I can take care of those,
Thanks in advance.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200804/1On Apr 29, 5:22=A0pm, "Jay via SQLMonster.com" <u7124@.uwe> wrote:
> Hello All,
> I need to create stored procedure that will output information created 3
> months after the record was created. For example: if the stored procedure =was
> run today or based on a date parameter I would like it to output all recor=ds
> created 3 months ago to that day. There are other parameters I need,but I
> think I can take care of those,
> Thanks in advance.
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx=
/sql-server-reporting/200804/1
In SQL try:
SET DATEPARAM =3D DATEADD(MONTH,-3,GETDATE())
In SSRS/VB try:
=3DDateAdd(DateInterval.Month, -3, Today())
HTH
toolman

Datediff and Record navigation

Hi:

I need help to find the difference between two times within the same table as follow

For every alarm incident (alarm will repeat within the table at different times)

Alarm acknowledge time:AkTime = AkAlm – InAlm

Alarm repair time:ReTime = OutAlm – Akalm

Alarm DownTime:AdtTime = OutAlm – InAlm

The table contains a decent number of records (> 10,000,000) and is indexed by the timestamp.

The table looks like this:

Alarm

AlarmType

tsTimeStapm

3030

InAlm

1/11/05 9:11:00 AM

3030

AkAlm

1/11/05 10:48:00 AM

3030

OutAlm

1/11/05 1:32:00 PM

3032

InAlm

1/11/05 2:51:00 PM

3032

AkAlm

1/11/05 2:52:35 PM

3032

OutAlm

1/11/05 3:14:00 PM

3030

InAlm

1/11/05 3:24:00 PM

3030

AkAlm

1/11/05 3:26:30 PM

3030

OutAlm

1/11/05 4:15:15 PM

Additionally

I need to calculate the mean time between alarms i.e. the time difference from the OutAlm to the next InAlm

Ex: Data from the sample table

3030

OutAlm

1/11/05 1:32:00 PM

3032

InAlm

1/11/05 2:51:00 PM

3032

OutAlm

1/11/05 3:14:00 PM

3030

InAlm

1/11/05 3:24:00 PM

Thanks in advance

Jorge

Jorge:

Maybe something like this:

declare @.alarm table
( Alarm integer,
AlarmType varchar (7),
tsTimeStamp datetime,

primary key (Alarm, tsTimeStamp),
unique (tsTimestamp, Alarm)
)

insert into @.alarm values (3030, 'InAlm', '1/11/05 9:11:00 AM')
insert into @.alarm values (3030, 'AkAlm', '1/11/05 10:48:00 AM')
insert into @.alarm values (3030, 'OutAlm', '1/11/05 1:32:00 PM')
insert into @.alarm values (3032, 'InAlm', '1/11/05 2:51:00 PM')
insert into @.alarm values (3032, 'AkAlm', '1/11/05 2:52:35 PM')
insert into @.alarm values (3032, 'OutAlm', '1/11/05 3:14:00 PM')
insert into @.alarm values (3030, 'InAlm', '1/11/05 3:24:00 PM')
insert into @.alarm values (3030, 'AkAlm', '1/11/05 3:26:30 PM')
insert into @.alarm values (3030, 'OutAlm', '1/11/05 4:15:15 PM')
--select * from @.alarm

select Alarm,
inAlm as [Alarm Timestamp],
datediff (mi, inAlm, akAlm) as [Alarm Acknowledgment Time],
datediff (mi, akAlm, outAlm) as [Alarm Repair Time],
datediff (mi, inAlm, outAlm) as [Alarm Downtime]
from ( select a.alarm,
a.tsTimeStamp as InAlm,
( select min (b.tsTimeStamp)
from @.alarm b
where a.alarm = b.alarm
and b.tsTimeStamp > a.tsTimeStamp
and b.alarmType = 'AkAlm'
) as AkAlm,
( select min (c.tsTimeStamp)
from @.alarm c
where a.alarm = c.alarm
and c.tsTimeStamp > a.tsTimeStamp
and c.alarmType = 'OutAlm'
) as OutAlm
from @.alarm a
where alarmType = 'InAlm'
) alm

-- Alarm Alarm Timestamp Alarm Acknowledgment Time Alarm Repair Time Alarm Downtime
-- -- -- - -- --
-- 3030 2005-01-11 09:11:00.000 97 164 261
-- 3030 2005-01-11 15:24:00.000 2 49 51
-- 3032 2005-01-11 14:51:00.000 1 22 23

select avg (datediff (mi, lastOutage, nextOutage)) as [Mean Time Between Alarms]
from (
select a.Alarm,
a.alarmType,
a.tsTimeStamp as [lastOutage],
( select min (tsTimestamp)
from @.alarm b
where b.alarmType = 'InAlm'
and b.tsTimestamp > a.tsTimestamp
) as [nextOutage]
from @.alarm a
where a.alarmType = 'OutAlm'
) x
where nextOutage is not null

-- Mean Time Between Alarms
--
-- 44

dateadd update works but adds 2 or 3 yrs-not 1

I have a page that is supposed to add a year to a record when it loads. The problem is that it adds 2 or three years instead.
Here is the page_load event:

Sub page_load(sender as object, e as eventargs)
 Try
 Dim connection As SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
 Dim command As SqlCommand = new SqlCommand("Updateexpiredate", connection)
 command.CommandType = CommandType.StoredProcedure
 Dim param0 As SqlParameter = new SqlParameter("@.memberid",SqlDbType.Int)
 param0.Direction = ParameterDirection.Input
 param0.Value = memberid
 command.Parameters.Add(param0)
 connection.Open()
command.ExecuteNonQuery()
 connection.Close()
 myerror.Text = "Thank You! Your account was updated"
 Catch ex As Exception
 myerror.Text = ex.Message
 End Try
End Sub

And here is the SPROC:

CREATE PROCEDURE Updateexpiredate
 (
 @.memberid int
 )
 AS
 UPDATE
 members
 SET
 expiredate=(dateadd(year,1,expiredate))<--I also tried expiredate=(dateadd(month,12,expiredate)) with the same results
 WHERE
 memberID = @.memberID
GO

... I'm assuming you're calling that method or procedure 2 or three time. :)

edited: Step through your app. It could be that it's called multiple times.|||How do I do that?
I don't even know where to begin with that.
Thanks|||The first thing I would do is turn tracing on for the page, and do a trace.write as the command right before the ExecuteNonQuery. This should give you more information on how many times that part of your code is being executed.

If you are not familiar with trace.write, look here:Trace Logging to Page Output. That is a good debugging tool to have in your pocket.

Terri|||What do you use to develop in? Is it Visual Studio, DreamWeaver, WebMatrix, notepad?

Date/Time Stamp

Good day,
Is there a way to add a field in a SQL table that automatically inserts a
date/time stamp when the record is created?
Thank you in advance...On Feb 27, 10:38=A0pm, PsyberFox <Psyber...@.discussions.microsoft.com>
wrote:
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
> Thank you in advance...
You can do it by writing a an insert trigger on table.|||... or having a default value for the column. Of course, a default can be overridden by the INSERT
statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<vinodkus@.gmail.com> wrote in message
news:746d44f8-b988-4950-b74a-b5d152515abc@.62g2000hsn.googlegroups.com...
On Feb 27, 10:38 pm, PsyberFox <Psyber...@.discussions.microsoft.com>
wrote:
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
> Thank you in advance...
You can do it by writing a an insert trigger on table.|||"PsyberFox" <PsyberFox@.discussions.microsoft.com> wrote in message
news:9B7F88F8-B885-4A28-979B-9B43385D26FF@.microsoft.com...
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
Just create it with a default of GETDATE() and make sure your procedure(s)
that insert into it don't specify a value for it
CREATE TABLE test (
dtmTimeStamp DATETIME DEFAULT GETDATE(),
[more columns]
)
If you need it to be 'secure' then as someone else mentioned you'll have to
create triggers to keep the value fixed and/or make sure an explicit value
can't be inserted.

Date/Time Stamp

When a record is written to a table (via a asp form), I'd like the time
and date from the server to automatically populate a column in that
table. From what I can tell, timestamp isn't working. I rather not
have the time come from the client.

Thanks for the help.Add a column with a default of CURRENT_TIMESTAMP. This is nothing to do
with TIMESTAMP, which is the SQL Server keyword for a row-versioning
column, not for date and time.

ALTER TABLE your_table ADD date_created DATETIME NOT NULL
CONSTRAINT df_your_table_date_created DEFAULT CURRENT_TIMESTAMP

--
David Portas
SQL Server MVP
--|||alternatively, you can also use as

ALTER TABLE your_table ADD date_created DATETIME NOT NULL
CONSTRAINT df_your_table_date_created DEFAULT getdate()

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***

Date/Time Stamp

Good day,
Is there a way to add a field in a SQL table that automatically inserts a
date/time stamp when the record is created?
Thank you in advance...
On Feb 27, 10:38Xpm, PsyberFox <Psyber...@.discussions.microsoft.com>
wrote:
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
> Thank you in advance...
You can do it by writing a an insert trigger on table.
|||"PsyberFox" <PsyberFox@.discussions.microsoft.com> wrote in message
news:9B7F88F8-B885-4A28-979B-9B43385D26FF@.microsoft.com...
> Good day,
> Is there a way to add a field in a SQL table that automatically inserts a
> date/time stamp when the record is created?
Just create it with a default of GETDATE() and make sure your procedure(s)
that insert into it don't specify a value for it
CREATE TABLE test (
dtmTimeStamp DATETIME DEFAULT GETDATE(),
[more columns]
)
If you need it to be 'secure' then as someone else mentioned you'll have to
create triggers to keep the value fixed and/or make sure an explicit value
can't be inserted.