1

Currently, I have an Excel sheet in which I input a Start-date and End-date which will go to the VBA code and input it on a SQL query. The problem I am currently having is that I received an error:

ORA-01843 not a valid month because the Excel is importing the date as 05/01/2016 and the query needs to be 05-may-2016.

How do I change that??

Here is the code for the start and end date:

startDate = Worksheets("Sheet1").Range("B4").Text
endDate = Worksheets("Sheet1").Range("B6").Text

dbConnectStr = "Provider=msdaora;User Id=" & Uname
dbConnectStr1 = "Provider=msdaora;User Id=xxendur ;Data Source=" & DSN

Set Sql.ActiveConnection = objmyconn
Sql.CommandText = "select system_date from syit_act_log where system_date between`enter code here` 'startDate' AND 'endDate' and action_id = 15 and log_desc not like '%svc_openlink_p%' order by system_date"
Sql.CommandType = adCmdText
Sql.Execute
Improve this question
3
  • 1
    use a yyyymmdd date format. Commented Jun 14, 2016 at 22:09
  • 1
    To clarify the answer provided by @Jeeped the where clause should be something like this: where system_date between '20160505' and '20160505' Commented Jun 14, 2016 at 22:16
  • I am still receiving Run-time error saying "not a valid month" I believe it is because in Excel the date is still 03/01/2016. I am trying to change the format of the date in Excel but when I do it just changes the display but not the number that is shown in fx when I click the cell. @Jeeped Commented Jun 15, 2016 at 14:33

1 Answer 1

Reset to default
4

You need to set your date variables using this formatting :

startDate = Format(Worksheets("Sheet1").Range("B4").Value2, "dd-mmm-yyyy")
endDate = Format(Worksheets("Sheet1").Range("B6").Value2, "dd-mmm-yyyy")
'startDate and endDate will be look like "05-May-2016"

The triple mmm will give you the month name as you requested. Then use those variables in your query.

Edit:

As Ralph and Jeeped suggested it is recommended to use the yyyymmdd standard

startDate = Format(Worksheets("Sheet1").Range("B4").Value2, "yyyymmdd")
endDate = Format(Worksheets("Sheet1").Range("B6").Value2, "yyyymmdd")
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I would like to propose two improvements to your answer: (1) you are using .Text instead of .Value2. Hence, the code might not work if the cell is formatted with Hindi date (for example). Also, the date might get misinterpreted if you are showing it in UK format but have the Excel running with USA locale. (2) you are suggesting dd-mmmm-yyyy which is what the OP requested. Yet, the SQL server could misinterpret it again if another user with a different language set logs onto the server. ISO 8601 is better as suggested by @Jeeped.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.