?
Solved

INSERTing Dates correctly prior to 1899

Posted on 2005-02-28
18
Medium Priority
?
187 Views
Last Modified: 2010-04-23
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
Comment
Question by:gjpitt
[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
  • 8
  • 6
  • 4
18 Comments
 

Author Comment

by:gjpitt
ID: 13420570
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13420615
where is tmpvalue used and where is tmpDate defined?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13420638
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:gjpitt
ID: 13421566
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13422133


strSQL = "UPDATE Births SET [Date]= CONVERT(datetime, '" & tmpValue & "', 103) WHERE BirthID = " & Session("WorkingRecord")
0
 

Author Comment

by:gjpitt
ID: 13423195
I'll check it out thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13426196
We can only hope that the [Date] column is not smalldatetime...
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13426366
don't jinx it perkins ;-)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13433009
On seconds thoughts, I don't believe it (date set to smalldatetime) is possible.
0
 

Author Comment

by:gjpitt
ID: 13447712
Unfortunately  get the error

Undefined function 'CONVERT' in expression.

??
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13449820
Which would imply that you are not using MS SQL Server.  So please let us know what DBMS you are actually using?
0
 

Author Comment

by:gjpitt
ID: 13450849
ACCESS - sorry I should have mentioned it before:)
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 13451496
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
 

Author Comment

by:gjpitt
ID: 13452334
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13453327
I am not following you.  The format I am suggesting is yyyy-mm-dd. This date format is unambiguous.
0
 

Author Comment

by:gjpitt
ID: 13456537
OK - thanks
0
 

Author Comment

by:gjpitt
ID: 13456672
It works - Many Thanks

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13459358
>>It works - Many Thanks<<
Than please close the question.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month8 days, 6 hours left to enroll

766 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