• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

INSERTing Dates correctly prior to 1899

There seems to be a hidden intelligenence working against me:)

I have 3 DropDownLists for users to select Day, Month and Year the values ofthese are passed to a string var tmpValue

            tmpValue = "#" & tmpDay & "/" & tmpMonth & "/" & tmpYear & "#"

        strSQL = "UPDATE Births SET [Date]= " & tmpDate & " WHERE BirthID = " & Session("WorkingRecord")

If tmpValue is "04/01/1997" the date is correctly updated and disaplyed as 4th January 1997

If tmpValue is "04/01/1897" the date is incorrectly updated and disaplyed as 1st April 1897

I am assuming this is a vb,net problem as it is running alongside classic asp without any problems.

Any suggestions as to how I can kill off this little gremlin?
0
gjpitt
Asked:
gjpitt
  • 8
  • 6
  • 4
1 Solution
 
gjpittAuthor Commented:
Further investigation shows this to be a .ToLongDateString problem which still needs correcting. I want to be able to display ALL date as 4 Jan 1923 etc. dd MMM yyyy - anyone got a customs bit of code?
0
 
Brian CroweDatabase AdministratorCommented:
where is tmpvalue used and where is tmpDate defined?
0
 
Brian CroweDatabase AdministratorCommented:
use a custom format and the datetime.tostring method

private const kDateFormat as string = "d mmm yyyy"

...

dim strDate as string = myDateTime.tostring(kDateFormat)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gjpittAuthor Commented:
Unfortunately it is the datre format that screw things up. I have found a work around for now...

I get the values from the DDL's
        tmpDay = DropDownList5.SelectedItem.Text
        tmpMonth = DropDownList6.SelectedItem.Text
        tmpYear = DropDownList7.SelectedItem.Text

        Dim srvPath, tmpValue, tmpText, tmpDate As String
        Dim strConnect, strCommand, strSQL, strFind As String

then force a string to hold the data in the way I want to view it
 
           tmpValue = tmpDay & "/" & tmpMonth & "/" & tmpYear

and prepare a different string to insert into the sql command
            tmpDate = "#" & tmpMonth & "/" & tmpDay & "/" & tmpYear & "#"

         Label26.Text = tmpValue

If might not be pretty but it works:)
Thanks for your help!
0
 
Brian CroweDatabase AdministratorCommented:


strSQL = "UPDATE Births SET [Date]= CONVERT(datetime, '" & tmpValue & "', 103) WHERE BirthID = " & Session("WorkingRecord")
0
 
gjpittAuthor Commented:
I'll check it out thanks
0
 
Anthony PerkinsCommented:
We can only hope that the [Date] column is not smalldatetime...
0
 
Brian CroweDatabase AdministratorCommented:
don't jinx it perkins ;-)
0
 
Anthony PerkinsCommented:
On seconds thoughts, I don't believe it (date set to smalldatetime) is possible.
0
 
gjpittAuthor Commented:
Unfortunately  get the error

Undefined function 'CONVERT' in expression.

??
0
 
Anthony PerkinsCommented:
Which would imply that you are not using MS SQL Server.  So please let us know what DBMS you are actually using?
0
 
gjpittAuthor Commented:
ACCESS - sorry I should have mentioned it before:)
0
 
Anthony PerkinsCommented:
In your case, the easiest way would be as follows:

Dim tmpDate As String = String.Format("'{0}-{1}-{2}'", tmpYear, tmpMonth, tmpDay)
strSQL = "UPDATE Births SET [Date]= " & tmpDate & " WHERE BirthID = " & Session("WorkingRecord")
0
 
gjpittAuthor Commented:
Woukld it work as

Dim tmpDate As String = String.Format("'{2}-{1}-{0}'", tmpDay,tmpMonth,tmpYear)
strSQL = "UPDATE Births SET [Date]= " & tmpDate & " WHERE BirthID = " & Session("WorkingRecord")

or is there a reason for the American foromat
0
 
Anthony PerkinsCommented:
I am not following you.  The format I am suggesting is yyyy-mm-dd. This date format is unambiguous.
0
 
gjpittAuthor Commented:
OK - thanks
0
 
gjpittAuthor Commented:
It works - Many Thanks

0
 
Anthony PerkinsCommented:
>>It works - Many Thanks<<
Than please close the question.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 8
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now