0
votes

Can anyone tell me what wrong i am doing while inserting date in into ms access? I am inserting date using below sql statement and date format is d-M-yyyy

insert into tbl_date(date_value) values(#8/7/2014#)

It's not showing me any error but store in access 7-Aug-2014 instead of 8-Jul-2014

what is solution for this?

Thanks in advance.

2
where is that date format defined? In C# or Access? - T_D
@huMptyduMpty i have fired this sql statement in c# - Devz
Do you have to insert a fixed date or this is just a test code? Because the correct approach is to use a parameterized query where dates are correctly handled by the db engine without resorting to a formatting rules - Steve
@Steve Actually i have date in this format. now i want to insert it in access. - Devz

2 Answers

5
votes

In database date format is MM/DD/YYYY it's insert 8 as month and 7 as day

please check it as

insert into tbl_date(date_value) values(#7/8/2014#)
1
votes

Access, whether you like it or not the Date format is Month-Date-Year. Not the "normal" Date-Month-Year. So you are in need of a Format function that will parse the normal date to the format of MM/DD/YYYY. I am not sure what C# functions are, but in VBA one would use the Format function like.

INSERT INTO tbl_date(date_value) VALUES (Format(Date(), "\#mm\/dd\/yyyy\#"))