problem with GetDate() in asp.net

Question

Hello all, I have problem with GetDate() method.When i am creating any new user,in DateTime field Date is coming correctly time is not coming correctly,it should show generate always system date and time.Ex:2/6/2009 12:00:00 AM,2/7/2009 12:00:00 AM,2/8/2009 12:00:00 AM etc. I mean here date is changing but system time is not changing. Here is my code:

CREATE PROCEDURE dbo.NewUser
(
@UserName varchar(50),
@Password varchar(50),
@EmailId varchar(50),
@DateTime Date
)
AS
insert into Login(UserName,Password,EmailId,DateTime)
       values(@UserName,@Password,@EmailId,GetDate())


com.Parameters.Add("@DateTime", DateTime.Now.ToShortTimeString());

Answer 1

I would suggest two changes:

• Change the parameter of the stored procedure to datetime2 instead of just date. (There are other options as well, such as datetimeoffset.) The date type only stores the date, so it's not surprising that you're not seeing any times :) You'll need to change the table as well if its schema uses a date column.

• Don't convert the parameter value to a string when you're calling it. Use

  com.Parameters.Add("@DateTime", DateTime.Now);
  

You might also want to consider using UTC instead of the local time on the server.

EDIT: splattne's absolutely right - by calling GetDate in the stored proc, the value of your parameter is irrelevant. Assuming you actually want to use the parameter, change the body of your stored proc to:

   insert into Login(UserName, Password, EmailId, DateTime)
   values(@UserName, @Password, @EmailId, @DateTime)

Double check the type of Login.DateTime though!

Answer 2

You must be using Sql 2008, with it's new Date datatype.

CREATE PROCEDURE dbo.NewUser ( @UserName varchar(50), @Password varchar(50), @EmailId varchar(50), @DateTime Date )

Change the Sql code to use DateTime (and, the table, if necessary) and you should be set.

Answer 3

If you're using getdate() in your stored procedure, your parameter @DateTime is obsolete. According to your code sample, it will be ignored.

So, the root of your problem must be somewhere else, if I correctly interpret your question.

I would check the datatype of the date column in the SQL table. The table declaration should be:

UserName varchar(50)
Password varchar(50)
EmailId  varchar(50)
DateTime DateTime  /* not "Date" */

Answer 4

Use Date of the DateTime.Now

com.Parameters.Add("@DateTime", DateTime.Now.Date)

Also my suggestion is to use UTC instaed

com.Parameters.Add("@DateTime", DateTime.UtcNow.Date)