Solved

ASP.net SQL Date 1/1/1900

Posted on 2011-09-02
9
542 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:murbro
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 19

Accepted Solution

by:
Limbeck earned 125 total points
ID: 36472799
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
 
LVL 19

Assisted Solution

by:Bardobrave
Bardobrave earned 125 total points
ID: 36472966
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
 

Author Comment

by:murbro
ID: 36473906
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 125 total points
ID: 36474813
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
 
LVL 19

Expert Comment

by:Bardobrave
ID: 36476226
Also, usually the formatting of strings on YYY/MM/DD format usually solves month/day incongruences and works best than other formats.
0
 
LVL 1

Expert Comment

by:aneesa83
ID: 36477193
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 36478231
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36478242
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
 

Author Closing Comment

by:murbro
ID: 36480687
thank you for the help
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question