ASP.net SQL Date 1/1/1900

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I am using the code below to Insert a date into a backend SQL table from ASP.net
I have tried to prepare the date using code to fit the required format
but am still getting 1/1/1900
'++++++++++++First prepare date to be accepted by SQL
        'Date values in SQL is always saved in "MM/DD/YY(YY)" format.
        'So first convert the string date around to this
        Dim ConverToDate = CDate(oDate)
        Dim sDay As String = Day(ConverToDate)
        Dim sMonth As String = Month(ConverToDate)
        Dim sYear As String = Year(ConverToDate)
        If Len(sDay) = 1 Then sDay = "0" & sDay
        If Len(sMonth) = 1 Then sMonth = "0" & sMonth

        Dim strSQLDate As String = sDay & "/" & sMonth & "/" & sYear

        Dim sSQL As String
        sSQL = "INSERT INTO Performance ([Date], [Team Leader], Foreman, Supervisor, Shaft, [Machine Number], [Hole Number], Shift, [Machine Operator], [Assistant Operator], [Assistant Operator 2], [Assistant Operator 3], [Drilled From], [Drilled To], AXT, BX, NX, Setup, [Drilled Total], [Rock Redrill], [Grout Hours], [Concrete Redrill], [Drill Hours], [Travel Hours], [Transport Hours], [Delay Hours 1], [Delay Code 1], [Delay Hours 2], [Delay Code 2], [Delay Hours 3], [Delay Code 3], [Servicing Hours], [DWR Hours], [Total Hours], Rods, Bits, Shells, Remarks ) " _
        & "SELECT " & "" & strSQLDate & " AS oDate, " _
        & "'" & oTeamLeader & "', " & "'" & oForeman & "', " & "'" & oSupervisor & "', " & "'" & oShaft & "', " & "'" & oMachineNumber & "', " & "'" & oHoleNumber & "', " & "'" & oShift & "', " _
        & "'" & oMachineOperator & "'," & "'" & oAssistantOperator & "'," & "'" & oAssistantOperator2 & "'," & "'" & oAssistantOperator3 & "'," _
        & oDrilledFrom & ", " & oDrilledTo & ", " & oAXT & ", " & oBX & ", " & oNX & ", " & oSetup & ", " & oDrilledTotal & "," _
        & oRockRedrill & ", " _
        & oGroutHours & ", " _
        & oConcreteRedrill & ", " _
        & oDrillHours & ", " _
        & oTravelHours & ", " _
        & oTransportHours & ", " _
        & oDelay1 & ", " _
        & "'" & oDelay_Code1 & "'," _
        & oDelay2 & ", " _
        & "'" & oDelay_Code2 & "'," _
        & oDelay3 & ", " _
        & "'" & oDelay_Code3 & "'," _
        & oServicingHours & ", " _
        & oDWRHours & ", " _
        & oTotalHours & ", " _
        & oRods & ", " _
        & "'" & oBits & "'," _
        & "'" & oShells & "'," _
        & "'" & oRemarks & "'"

        Dim cmd As New OleDbCommand(sSQL, cn)

        Try


            '    '// open the connection
            cn.Open()



            cmd.ExecuteNonQuery()

        Catch ex As Exception
            Response.Write(Err.Description)

        Finally
            If cn.State <> ConnectionState.Closed Then
                cn.Close()
            End If
        End Try

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
i would use parameters instead f.i.
http://www.shotdev.com/aspnet/vbnet-system-data-sqlclient/vbnet-system-data-sqlclient-parameter-query-sqlparameter/

a lot less sloppy, no risk of sql injection, and easier to change later on
1/1/1900 is the value SQL Server understands as empty for datetime type, so probably your date script construction is returning a null or an empty value.

Check the value of your string date on execution time to ensure it's holding your desired value.
Murray BrownASP.net/VSTO Developer

Author

Commented:
The values I am using are definitely not null. I need to know how to use a string date such as "27/08/2011" to construct my SQL statement
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Kevin CrossChief Technology Officer
Most Valuable Expert 2011
Commented:
SQL doesn't store dates in a specific format. it represents it numerically in relation to date 0 which as mentioned above happens to be 1900-01-01 for DATETIME columns. I would pass SQL the date in YYYY-MM-DD format, so ConverToDate.ToString("yyyy-MM-dd"). When you bring the data out of SQL, then use CONVERT() with format code 103 for example to get DD/MM/YYYY -- http://msdn.microsoft.com/en-us/library/ms187928.aspx.

Default display format which you are seeing is based on language setting of server or session. You can either change the server language to British English. You can do the same for connection. In T-SQL, you can use SET LANGUAGE to make it specific to a given session/statement -- http://msdn.microsoft.com/en-us/library/ms174398.aspx

Hope that helps!
Also, usually the formatting of strings on YYY/MM/DD format usually solves month/day incongruences and works best than other formats.

Commented:
please check whether ur strSQLDate is getting null or empty,if then, in sql by default it vl take 1/1/1900 as the date value
Top Expert 2012
Commented:
>>please check whether ur strSQLDate is getting null or empty,if then, in sql by default it vl take 1/1/1900 as the date value
...
please check whether ur strSQLDate is getting null or empty,if then, in sql by default it vl take 1/1/1900 as the date value <<

That is twice in a row now.  So let's set the record straight.  In SQL Server a datetime can be NULL and this is not the same as 1/1/1900.  So let's move on from that and address the author's question:
As mentioned previously, what is happening is that SQL Server is implicitly converting an empty string ("") to 1/1/1900.
Top Expert 2012

Commented:
And it would have helped if I had copied and pasted correctly, so let me try that again:
>>1/1/1900 is the value SQL Server understands as empty for datetime type, so probably your date script construction is returning a null or an empty value.
...
please check whether ur strSQLDate is getting null or empty,if then, in sql by default it vl take 1/1/1900 as the date value <<

That is twice in a row now.  So let's set the record straight.  In SQL Server a datetime can be NULL and this is not the same as 1/1/1900.  So let's move on from that and address the author's question:
As mentioned previously, what is happening is that SQL Server is implicitly converting an empty string ("") to 1/1/1900.
Murray BrownASP.net/VSTO Developer

Author

Commented:
thank you for the help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial