By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,545 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

SQL insert into with Hijri date

P: 18
I have a problem with Insert into SQL statement. When I use the below SQL to insert a Hijri date , the date will be stored in the table as Gregorian date.

How can I specify the date that I am inserting into the table is Hijri date not Gregorian date.

Expand|Select|Wrap|Line Numbers
  1. dbsCurrent.Execute "INSERT INTO Rent ([RentDueDate]) VALUES" & "(#" & [Text_RentDueDate] & "#)"
Sep 14 '07 #1
Share this Question
Share on Google+
9 Replies


Scott Price
Expert 100+
P: 1,384
Try:

Expand|Select|Wrap|Line Numbers
  1. Application.SetOption "Use Hijri Calendar"
Regards,
Scott
Sep 14 '07 #2

P: 18
Thanks Scott for your replay, but still its not working

I will explain the problem with more details and examples as follows:

When I use the below code to insert the Hijri date “2 / 9 / 1428” which is equivalent to “14 / 9 / 2007” , MS Access will recognize and insert the date “2 / 9 / 1428” as Gregorian date and since I have already selected the option to use “Hijri Calendar”, MS Access will convert “2 / 9 / 1428” as Gregorian to “14 / 04 /831” as Hijri date , which is not correct as the original Hijri date is “2 / 9 / 1428”

The final result that will be stored in the table is “14 / 04 /831” as Hijri date and as you can see it is totally different from the date that I have used in the code “2 / 9 / 1428”.

Your help is highly appreciated
----------------------------------------------------------------------------------------
Dim dbsCurrent As Database
Set dbsCurrent = CurrentDb
Application.SetOption "Use Hijri Calendar", True
Calendar = vbCalHijri

dbsCurrent.Execute "Insert Into Rent ([RentDueDate]) VALUES (#" 2 / 9 / 1428 "#)"
dbsCurrent.Close
Sep 14 '07 #3

Scott Price
Expert 100+
P: 1,384
Have you changed the regional settings in the Windows control panel to make sure they are using Hijri date also?

Regards,
Scott
Sep 14 '07 #4

P: 18
Yes, I have already changed the regional settings to use Hijri date, but no luck
Sep 14 '07 #5

Scott Price
Expert 100+
P: 1,384
Yes, I have already changed the regional settings to use Hijri date, but no luck

Well, after a little digging around I found this in the Access/VBA help file:
Use International Date Formats in SQL Statements
See AlsoSpecificsYou must use English (United States) date formats in SQL statements in Visual Basic. However, you can use international date formats in the query design grid.
Try setting your query up in the query design grid, and then opening it from vb code.

Regards,
Scott
Sep 14 '07 #6

P: 18
Thanks Scott for your efforts

I will check the query design grid and see the results
Sep 14 '07 #7

P: 1
I know this a pretty old thread, but here is the solution in case it benefits anyone:

Add
calendar=vbcalgreg
before
dbsCurrent.Execute "INSERT INTO Rent ([RentDueDate]) VALUES" & "(#" & [Text_RentDueDate] & "#)"
and then at the end
add
calendar=vbcalhijri

this way date is converted to gregorian and stored, so when it is retrieved it is then converted back into hijri

( It works in ms access 2007)
May 25 '09 #8

P: 1
SQL/MS Access dont support hijri calender you just add 1000 years into hijri years and save it to sql and while retrieving just minus 1000 years everything will be ok... ;) like if your date is 01/05/1429 this will be 01/05/2429 and will be save easily.. and while retrieve minus 1000years willl be on its original stat..
Oct 25 '11 #9

NeoPa
Expert Mod 15k+
P: 31,186
Access supports the Hijri calendar. This has no effect on how dates are stored though. Today doesn't stop being today because I think of it in Hijri or Gregorian. SQL doesn't support Hijri literal dates (which I think is what this is actually about). Using literal dates in SQL is the same for any type of calendar and is done using m/d/yyyy format (as has been mentioned already in this thread). See Literal DateTimes and Their Delimiters (#) for more on this.
Oct 25 '11 #10

Post your reply

Sign in to post your reply or Sign up for a free account.