CODEWITHSUNDEEP
asp.netsql-servervb.netdatedatetime
DreamTeK
DreamTeK

Reputation: 34217

How to submit a correctly formatted DATE or TIME input value into a MSSQL datetime column?

QUESTION

How to submit a correctly formatted DATE or TIME input value into a MSSQL datetime column?

ASPX

<asp:TextBox ID="txtDate" runat="Server" Type="date"/>
<asp:TextBox ID="txtTime" runat="Server" Type="time"/>

VB.NET

Dim Date As Date = CType(Item.FindControl("txtDate"), TextBox).Text
Dim Time As Date = CType(Item.FindControl("txtTime"), TextBox).Text

SUBMITTED VALUES

13/2/2014
12:00

NOTES

I am very familiar with retriving a datetime field from sql and outputting just the time or date part.

I would normally set txtDate and txtTime as a string but would like to know other ways to populate a sql datetime field when only the DATE or TIME is submitted. (Either in aspx, vb.net or mssql).

In my example when a date is submitted the time portion is not relevant and when a time is submitted the date portion is not relevant however the complete string will need submitting to sql to fill the datetime field correctly.

Upvotes: 0

Views: 1095

Answers (1)

TTeeple
TTeeple

Reputation: 2989

SQL Server has defaults for DATE and TIME when you insert into a DATETIME column

DECLARE @DateTimeTest TABLE (
    EnteredDate DATETIME
    )
DECLARE @DateOnly DATE = '12/1/2012'
DECLARE @TimeOnly TIME = '12:00PM'

SELECT * FROM @DateTimeTest

INSERT INTO @DateTimeTest
VALUES (@DateOnly)

INSERT INTO @DateTimeTest
VALUES (@TimeOnly)

SELECT * FROM @DateTimeTest

This returns

enter image description here

If you want to set your own defaults, just append whichever is missing before inserting into the table.

Upvotes: 2

Related Questions