?
Solved

ASP.net SQL Date 1/1/1900

Posted on 2011-09-02
9
Medium Priority
?
548 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 19

Accepted Solution

by:
Limbeck earned 500 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 500 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:Murray Brown
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 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 500 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:Murray Brown
ID: 36480687
thank you for the help
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

801 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