Solved

ASP.net SQL Date 1/1/1900

Posted on 2011-09-02
9
538 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 125 total points
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 19

Expert Comment

by:Bardobrave
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
thank you for the help
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now