ASP.net SQL Date 1/1/1900

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

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
LimbeckCommented:
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
0
 
BardobraveCommented:
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.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor 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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
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!
0
 
BardobraveCommented:
Also, usually the formatting of strings on YYY/MM/DD format usually solves month/day incongruences and works best than other formats.
0
 
aneesa83Commented:
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
0
 
Anthony PerkinsCommented:
>>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.
0
 
Anthony PerkinsCommented:
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.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thank you for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.