asked on

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?

gjpitt

ASKER

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?

Brian Crowe

where is tmpvalue used and where is tmpDate defined?

Brian Crowe

use a custom format and the datetime.tostring method

private const kDateFormat as string = "d mmm yyyy"

...

dim strDate as string = myDateTime.tostring(kDateFormat)

gjpitt

ASKER

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!

Brian Crowe

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

gjpitt

ASKER

I'll check it out thanks

Anthony Perkins

We can only hope that the [Date] column is not smalldatetime...

Brian Crowe

don't jinx it perkins ;-)

Anthony Perkins

On seconds thoughts, I don't believe it (date set to smalldatetime) is possible.

gjpitt

ASKER

Unfortunately get the error

Undefined function 'CONVERT' in expression.

??

Anthony Perkins

Which would imply that you are not using MS SQL Server. So please let us know what DBMS you are actually using?

gjpitt

ASKER

ACCESS - sorry I should have mentioned it before:)

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gjpitt

ASKER

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

Anthony Perkins

I am not following you. The format I am suggesting is yyyy-mm-dd. This date format is unambiguous.

gjpitt

ASKER

OK - thanks

gjpitt

ASKER

It works - Many Thanks

Anthony Perkins

>>It works - Many Thanks<<
Than please close the question.