Insert Time into SQL server

Hi,

I have created a wizard form to collect user information.When use click Finish button all the details added to the SQL server database.
That part is ok.
But my problem is this

Through my interface I am giving user to select the date and time.
(I have used AJAX datepicker and MaskedEdit control)

After user type the date (Normal format is - HH:MM:SS) and click finish button I check the database.

It automatically Inserted the date also (Jan 1 1900)

So I dont want this date part and I just want Time only.How do I do this ??

(I have used datatype as : nvarchar instead of datetime .Because of if i use datatime it automatically inserts both data and time values)

Values with thedatetimedata type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after thebase date, January 1, 1900. The base date is the system reference date. Values fordatetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

So in your case the first 4 bytes are not used. By default the value of Jan 1 1900 is displayed.

I do suggest that you continue to use the datetime value to store your time for filtering and sorting purposes but when you display the data to the end user you simply format the data to a time format.

Hope this helps!

|||

Hi,

you can convert your datetime with the CONVERT command, e.g. (in Query Analyzer):

Declare @.input As DateTime
Set @.input = Getdate()

Select Convert(nvarchar,@.input,108)

Regards
Marc André

|||

Hi,

thanks for your reply.

Insert same data to two different tables with one submit button....

I would like to insert a set of data to my database to 2 different tables.
what will be the SQL Statement for such ? Is it possible to do so?

for example, a webpage contain 2 textbox which require user to key in data. Once click submit button. the info will saved to 2 tables...you can pass the data to a stored procedure and from there insert the data into as many tables as you need...( all in one trip)

hth|||Hie, thanks for replying me...

I'm not very clear with what u say...
u mean create 1 store procedure or 2 store procedure. Can u show me theSQL statement how to insert ?

for example:

dim mysqlcommand as new SqlCommand("insert into Table1 (name) values (@.name)", mycon)

Let say this is the initial command for one insert statement, how to insert the same thing to another Table ?
U were saying using SP, can u show me the statement...?

Thanx...|||There are a few ways to do it, but you can't do it in one SQL statement.

You would have to create to two insert statements inside a stored procedure.

You asked for an example of a SPROC, this should work:

CodeBehind (not tested):

Dim cmd As New SqlCommand
cmd.Connection = conn 'Or your connection string
cmd.CommandText = "YourSprocName"
cmd.CommandType = CommandType.StoredProcedure
sqlpar = cmd.Parameters.Add("@.Name", SqlDbType.VarChar, 50)
sqlpar.Value = tbName.Text
If conn.State = 0 Then conn.Open()
cmd.ExecuteNonQuery()
cmd.Dispose()
conn.Close()

SPROC (not tested):

CREATE PROCEDURE dbo.YourSprocName
(
@.Name AS varchar(50)
)
AS
INSERT INTO Table1
(Name) VALUES (@.Name)
INSERT INTO Table2
(Name) VALUES (@.Name)
GO

Another alternative would be to build a trigger on the Table1 that would insert into Table2.

A third alternative would be to duplicate the in-line SQL on your page. (this method is recommended the least, I recommend doing as much as you can in SPROCS)