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 format

Hello:

Hello:
I have some code in a asp.net function in C# like this:

DateTime datetime = DateTime.Now;
SqlCommand CommandEvent = new SqlCommand("spAddNewEvents", Connection);
CommandEvent.Transaction = Trans;
CommandEvent.CommandType = CommandType.StoredProcedure;
*I try First : CommandEvent.Parameters.Add("@.date", datetime);
*I try Secound: CommandEvent.Parameters.Add("@.date", SqlDbType.DateTime,
8).Value = datetime;

and have some storeprocedure with this code

CREATE PROCEDURE dbo.spAddEvents
 (
 @.guid uniqueidentifier,
 @.language char (2),
 @.date as datetime,
 @.eventId as varchar (50),
 @.userid as varchar (20)
 )
AS
 execute('insert into tblEvents'+@.language+'( guid, [date], [id], userid)
values('''+@.guid+''','''+@.date+''','+@.eventId+','''+@.userId+''')')
GO

The problem is when I try to insert a new event. The event insert are fine,
but the datetime's secound in tblEvents always is 00, and I check the
datetime variable to insert and have secound different that 00.

the table definition is Data Type: datetime and Length: 8, how must be?

I run Profiler and a I get this:

exec spAddEvents @.Guid = 'C879D062-C268-4A3E-8D58-1937B7612EC2', @.language = N'ES', @.date = 'May 6 2004 11:29:58:140PM', @.eventId = 6, @.userid = N'anibal'

Best regards.
Owen.That's odd. Try writing your stored procedure without using a dynamic string. In this case, you don't need to use a dynamic statement. It might be causing trouble there. Also, if you're just putting in an insert date (not depending on any logic in your code), use getdate() within your stored procedure and forget about passing in the date. No need to generate that in your code.

DATETIME Default getDate() query

Hi,
I have created a datagrid within an ASP.NET page that links to an SQL
table - this works just fine. One of the columns retrieves the date which a
particular row was populated. However, the format
which the date is returned is:
19.12.2004 13:03:25
I only want to display the date that the row was populated, not the exact
time as well. When I created the table I used the following code (I've cut
out the rest of the fields):
CREATE TABLE Users
(
Paul,
You can use this as your default:
Default dateadd(d,0,datediff(d,0,getdate()))
It works by finding the number of day boundaries between day 0 (January
1, 1900) and now, and adds that many days back to day 0.
Steve Kass
Drew University
Paul Evans wrote:

>Hi,
>I have created a datagrid within an ASP.NET page that links to an SQL
>table - this works just fine. One of the columns retrieves the date which a
>particular row was populated. However, the format
>which the date is returned is:
>19.12.2004 13:03:25
>I only want to display the date that the row was populated, not the exact
>time as well. When I created the table I used the following code (I've cut
>out the rest of the fields):
>CREATE TABLE Users
>(
> .
> .
> .
> .
> u_entrydate DATETIME Default getDate()
>)
>
>Is this to do with the DATETIME Default getDate() code for creating the
>column? Is there something else I could use to get just the date only, with
>out the time of day the row was populated?
>I'm using a microsoft SQL server.
>Thanks for your time
>Paul Evans
>P.S. or is it a problem with my ASP.NET code?
>
>

Datetime and conversion to smalldatetime.

I am placing DateTime into SQL using an ASP.NET form. The date should be formatted dd/mm/yyyy hh/mm/ss.

I am getting the error below. Is there any way to convert the format of the DateTime function from the ASP.NET end?

Thanks

mes

"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value"

DateTimes aren't "formatted". It sounds like you are giving it a string, that you think looks like a date. Give the database a real datetime (Or specify the sqlparameter as being of datetime type), and your problem should go away. If that string format isn't valid for your culture, well... You'll have to fix that separately, or manually convert your "DateTime in a string" to a format that your SQL Server wants.

DateTime & SQL Server

I'm going slowly insane trying to get a Date AND Time value stored in SQL 2000 from ASP.NET 2.0.

The following works fine in Query Analyzer, but fails when run from ASP.NET:

INSERT INTO tbl_WIP_DATA_DateTime (ObjectInstanceID, ObjectPropertyID, PropertyData_DateTime) VALUES (32226,7,'20060120 10:43:44')

Does anyone have any suggestions?

Cheers
Vatic

Is that exactly what you have in your ASP.NET page (hardcoded values) or are you passing the values through parameters? If you are using parameters please post code.|||

This is exactly as it appears in code. The sql query is dynamically created.

|||

I've also tried the following:

Dim objconAsNew SqlConnection(System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString"))
Dim myCommandAs SqlCommand
Dim myParamAs SqlParameter
myCommand =New SqlCommand()
myCommand.Connection = objcon

DataQuery ="INSERT INTO " & myProperty.dataTable &" (ObjectInstanceID, ObjectPropertyID, " & myProperty.DataColumn &") VALUES (@.ObjectInstanceID, @.ObjectPropertyID, @.theDateTime)"

myCommand.CommandText = DataQuery
myCommand.Parameters.Add(New SqlParameter("@.ObjectInstanceID", SqlDbType.Int))
myCommand.Parameters("@.ObjectInstanceID").Value = myObjectInstance.ObjectInstanceID

myCommand.Parameters.Add(New SqlParameter("@.ObjectPropertyID", SqlDbType.Int))
myCommand.Parameters("@.ObjectPropertyID").Value = myProperty.ObjectPropertyID

myCommand.Parameters.Add(New SqlParameter("@.theDateTime", SqlDbType.DateTime))
myCommand.Parameters("@.theDateTime").Value = Now()

objcon.Open()
myCommand.ExecuteNonQuery()
objcon.Close()

Which returns:

The string was not recognized as a valid DateTime. There is a unknown word starting at index 0.Expected type is DateTime

|||

Try:

dim dt as datetime=datetime.parse(Now())

See if that throws you any errors.

datetime

I use Calendar asp.net(c#).It gives me Calendar.SelectedDate.Date is string
I convert it to datetime with Convert.toDateTime but Sql server give me
error...
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated.
How can I solve the error and add date to sql server?
Thanks...You're not using a proper datetime format for SQL Server:
http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Selen" <skiyanc@.yahoo.com> wrote in message
news:OLR5VrmCEHA.2656@.TK2MSFTNGP12.phx.gbl...
> I use Calendar asp.net(c#).It gives me Calendar.SelectedDate.Date is
string
> I convert it to datetime with Convert.toDateTime but Sql server give me
> error...
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value. The statement has been terminated.
> How can I solve the error and add date to sql server?
> Thanks...
>|||two options to go forwards
1. Use a neutral date format. These are the ISO and ISO 8601 standard. These
have the CONVERT sytle numbers of 112 and 126 respectively. Check the SQL S
erver documentation on the CONVERT function for details.
2. Change the language of the SQL Server login used to match the string that
is being sent down.

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 ***