Insert date into Access

[zubie]

Hi All

 

I am using SQL to insert a date into an access table:

 

Regional settings are set to dd/mm/yyyy hh:mm

 

When I insert the following

SQL = "insert into DateTest (MyDate, MyTime) values ('25/12/2004', '12:00')

 

I get 25/12/2004

 

BUT

When I insert the following

SQL = "insert into DateTest (MyDate, MyTime) values ('01/12/2004', '12:00')

 

I get 12/01/2004

 

anyone know why this happens??

 

cheers ZuBiE

[Joe Mamma]

first whay are you spiltting into MyDate and MyTime? if they got together they should be one field 'MyDateTime'

 

second. . . Access likes 'Insert Into MyTable (MyDateTime) Values(#25/12/2004 12:00#)

 

Third, you should really use parameters:

 

from a string value-

MyCommand.CommandText = 'Insert Into MyTable (MyDateTime) Values(?)'

MyCommand.Parameters(0).Value = '25/12/2004 12:00'

MyCommand.ExecuteNonQuery

 

or a datetime value-

 

MyCommand.CommandText = 'Insert Into MyTable (MyDateTime) Values(?)'

MyCommand.Parameters(0).Value = DateTime.Now

MyCommand.ExecuteNonQuery

[zubie]

Well I am only testing at the moment

 

My problem is tha even if I use the parameter I'm still getting

 

25/12/2004 and 12/01/2004

instead of

 

25/12/2004 and 01/12/2004

[Ontani]

  Quote

Well I am only testing at the moment

 

My problem is tha even if I use the parameter I'm still getting

 

25/12/2004 and 12/01/2004

instead of

 

25/12/2004 and 01/12/2004

 

what if you just set your row properties to text instead of date that could work

 

Greetz

[Denaes]

  Quote

what if you just set your row properties to text instead of date that could work

 

Greetz

Might do that as a test to see what exactly is getting inserted, but he won't be able to search by date or sort by date nearly as easy if they're not dates

[zubie]

Well I need to sort by date

 

What I'm testing now is already in a project and is causing all types of problems

[Jay1b]

It seems to me that it tries to put it in MM/DD/YY anyway, if it fails; ie: mm=25 and dd=1 then it swaps them around. That would explain how its working for one date but not another.

 

Take a look at your table and see if theres an input mask on those fields, secondly check what format the field is in. Thirdly check you machine settings for date time.

 

Personally i would that the SQL thinks your writting it MM/DD/YY and Access thinks its receiving DD/MM/YY. If error then its correcting it.

 

If you cant get it working, change the field to US style in Access, just make sure your program displays it the 'proper' way ;)

[zubie]

Well I checked all the dates ...

 

I have worked a solution that seems to work for all concerned.

I am changing my date/time fields to text so that they display correctly.

For searching I am adding another field with the date in the format of YYYYMMDD

 

Well it seems to work OK so I'm leaving it like that .. any DB admin would kill me for not normalizing it correctly :)

ZuBiE

[Denaes]

  Quote

Well it seems to work OK so I'm leaving it like that .. any DB admin would kill me for not normalizing it correctly :)

ZuBiE

I have yet to see a DB app that that a programmer worked that I could spot a few ways to normalize it further.

 

my DB professor basically told us to strive for whatever normalization was best (high number or low number?), but in anything but the most basic DBs you're probobly going to get 1-2 from the best at best. Especially after you factor in that we have to access it via code.

 

Like I'm sure there is a SQL procedure you could use that would take the date as a string and convert it to date format for your sorts, keeping the two fields down to one. But I'll be damned if I can figure it out and unless you're on Oracle or SQL Server that it would be a fast enough procedure if you had any number of records.

 

Hard drive space isn't as valuable as it once was and I'll take my hair anyday over a few megabytes down the road a few years when we'll be talking like "Gigabyte what? You actually have a gigabyte hard drive!? I didn't know they made those anymore! Point and laugh at him, he has no terabytes! haha!" :D

[zubie]

Well put ... and any way isnt all that stuff only theory that was developed in the eighties ...

 

I seem to remember someone saying "64k should be enough for anyone"

(Bill Gates)

 

:)

[Jay1b]

And at the time it was :)

[Joe Mamma]

  Quote

I have worked a solution that seems to work for all concerned.

I am changing my date/time fields to text so that they display correctly.

For searching I am adding another field with the date in the format of YYYYMMDD

NEVER STORE DATES AS STRINGS!!!!

Dates aren't strings. As I have said before, I wouldn't hire a someone who showed me a portfolio with dates stored in a database as srtings.

It is plain wrong. no opinion. . .

FACT!!!

 

NORMALIZE!!!!

 

Especially if you have to program against it. . .

I strive for DKNF.

[Jay1b]

As i said before, put the whole thing into Access as US date style, see if you can do that! If so then its easy to swap the date around in VB