Avatar of Graeme McGilvray
Graeme McGilvray
Flag for Australia asked on

Multiple (separated dates) in a Statement

Hi Brains trust, I have an interesting question and I am not sure if it is possible, if so... I may need a little direction on how to do it..


I would like to create a statement where I can select some dates (separated), by this I mean (eg):

SELECT * FROM Products WHERE dep_date=Day(Today) AND dep_date=Month(Today) AND dep_date=Year(Today)

Open in new window


So the reason why I am doing this is because I am in Australia and we use the DD/MM/YY, however the server I am hosting my website has American Dates MM/DD/YY.


On a date like today, 8 November 2021, my current code shows all products from 11 August, tomorrow 11 September, next 11 October, etc, until the 13th arrives and it all goes back to normal.


Is this possible? If so, may I please have a hand in getting this to work?


Thanks all!

* ASP ClassicMicrosoft Access

Avatar of undefined
Last Comment
ste5an

8/22/2022 - Mon
Flyster

Have you tried using the Format function? For America it would be:
Format(Today(),"mm/dd/yy")

Open in new window

Down-under it would be:
Format(Today(),"dd/mm/yy")

Open in new window

Paul
Graeme McGilvray

ASKER
Hi Paul, I have tried this, unfortunately it does not work. Same result
ste5an

When the date is correctly stored as Date/Time, then you must use a date literal instead. A date literal in Access SQL must be US formatted:

SELECT * 
FROM Products 
WHERE dep_date = #mm/dd/yyyy#;

Open in new window

But this is only relevant, when using manual crafted, complex statements. For such simple statements, use a a parameterized query. Then the correct handling is done by ADO.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Graeme McGilvray

ASKER
Hi Ste5an,

I have my date stored in Access as DD/MM/YY. However the Windows Machine that I am hosting ASP on is in the US and the local date on it is MM/DD/YY.

I have asked the host to change this, however they have refused because it will upset the rest of the users on this machine.

When I had it hosted on my home machine a couple of years ago (DD/MM/YY), it was fine.
ste5an

I have my date stored in Access as DD/MM/YY.
This makes no sense.

Either it is stored as Date/Time, then the format does not matter, when you work correctly in SQL. The correct way to specify a date literal in Access SQL is only #mm/dd/yyyy#. E.g. this is a valid query:

SELECT * 
FROM Products 
WHERE dep_date = #11/08/2021#;

Open in new window

You also missed one of the most important things in computing: The difference between the storage format of a value and its representation when it is visualized. This is especially important for Date/Time in Access. The actual Date/Time is stored always in the same internal format, basically a epoch counter. What you see and what you set is the formatting (mask) used when this Date/Time is translated into an string to be printed out somewhere.

And finally: There is no Date data type in Access, only Date/Time.

Or it is stored as a string, then you must review your data model and correct that mistake.

[..] it was fine.
Sorry, but this is not a quality measure in software development. It is as bad as "it work's".

To quote Don Knuth in a short form:

Make it run, make it right, make it fast, if necessary.
Thus it simply never left the it runs state.

The correct approach is:

- Store your dates as Date/Time
- Use parameterized queries.

Thus review your data model. Then adjust your code to use parameters in your query. Then you don't have even to think about any date format in your code as it is handled transparently. btw, this is a part i8l or internationalization and localization of an application.

See ASP Classic - Using Parameterized Queries.
Graeme McGilvray

ASKER
This makes no sense. 
When I have manually entered the dates into Access it has gone in as DD/MM/YY - as Date/Time
SELECT *  FROM Products  WHERE dep_date = #11/08/2021#;
This is what I am currently using and I am having the issues that I have explained.
It will select the 1st number as the month number, until that clicks over to 13, then it becomes the date number.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ste5an

Entering the date in a certain format in an UI has nothing do to with what relational database system stores. Here only the data type of the column matters.

And, sorry, but read my post: USE PARAMETERIZED QUERIES!

This is what I am currently using and its causing the issues.

Open in new window

Read my posts: USE PARAMETERIZED QUERIES!

Using parameterized queries is the correct approach. Not string concatenation to craft SQL statements.
Graeme McGilvray

ASKER
I did have a read of it and unfortunately I do not understand it as I am still learning. So I do not know what I am looking for to change as I did not see anything with dates

Also thanks for clearing up that how i put it into the DB, doesnt matter (I thought it did)
ste5an

Thus I've added the link to Wayne's excellent article: ASP Classic - Using Parameterized Queries.

The key to understanding is to take this article as outline. Create a separate test project to exercise and understand the principles. Then implement it in your existing project.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Graeme McGilvray

ASKER
I have read and looked at Wayne's article (as I mentioned previously), all I can see is code for a login page... I cant see anything that looks to be relevant to me on it for issues with recalling dates from a DB
ste5an

E.g,

Capture.PNG
shows you how to use parameters.

[,,] all I can see is code for a login page [,,]
This is the reason, why should setup a test project to exercise working with parameters to get a grasp on it.
Graeme McGilvray

ASKER
Sorry I am not grasping how this relates at all...

All I want to do is call records that are after today's date, but when it is within the first 12 days of the month, it reverses date and month, calling past records.

I am using similar code that you have suggested before:
SELECT *  FROM products  WHERE dep_date > #"&Date()&"#
I just want to fix this.

Is my suggestion possible? And would it work?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ste5an

Yes, by using a parameterized query. Otherwise as already posted, you need to create date literal of the structure #mm/dd/yyyy#.

Your code calls a function which returns the current date as string. Thus the content given by "" & Date() can be any arbitrary format. This is the cause why does not work, cause it is, even when it is working in some scenarios, incorrect code.

The correct code for SQL statement concatenation as already posted is to format your date as US format. (*living in a loop today*).

Thus format it. This is simple and basic working with dates in VBScript;

Option Explicit

Dim SelectStatement

SelectStatement = "SELECT * FROM products WHERE dep_date > " & SqlDateLiteral(Date()) & ";"
WScript.Echo SelectStatement

Function SqlDateLiteral(ADate)
' Return date as date literal (string) in the US-Format mm/dd/yyyy.
' Input expects a Date/Time value.

  Dim DayPadded
  Dim MonthPadded
  Dim YearPadded

  DayPadded = LeftPad(Day(ADate), "0", 2)
  MonthPadded = LeftPad(Month(ADate), "0", 2)
  YearPadded = LeftPad(Year(ADate), "0", 4)

  SqlDateLiteral = "#" & MonthPadded & "/" & DayPadded & "/" & YearPadded & "#"

End Function

Function LeftPad(AText, APaddingCharacter, AMinimumLength)

  LeftPad = Right(String(AMinimumLength, APaddingCharacter) & AText, Max(AMinimumLength, Len(AText)))

End Function

Function Max(a, b)

  Max = a
  If b > a Then
    Max = b
  End If

End function

Open in new window


p.s. and for posting code use the </> button or [code][/code] tags.
Graeme McGilvray

ASKER
Would that VB script similar to:

TodaysDate=Month(Date())"&/&"Day(Date())&"/"&Year(Date())

Open in new window

ste5an

TodayLiteral = "#"  & Month(Date()) & "/" & Day(Date()) & "/" & Year(Date()) & "#"

Open in new window

would return a valid date literal.

I'm just used to padded literals. Thus you can shorten my sample to

Option Explicit

Dim SelectStatement

SelectStatement = "SELECT * FROM products WHERE dep_date > " & SqlDateLiteral(Date()) & ";"
WScript.Echo SelectStatement

Function SqlDateLiteral(ADate)
' Return ADate as date literal (string) in the US-Format mm/dd/yyyy.
' Input expects a Date/Time value.  

  SqlDateLiteral = "#" & Month(ADate) & "/" & Day(ADate) & "/" & Year(ADate) & "#"

End Function

Open in new window

But using parameterized quires are the better solution. Errors in SQL concat solutions can lead to SQL injection vulnerabilities.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Graeme McGilvray

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
aikimark

Is dep_date a string column or a datetime column?
aikimark

You can use DateSerial() to construct your date values.
ste5an

The marked post won't work to obvious syntax errors.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Graeme McGilvray

ASKER
Hi aikimark,
dep_date is a datetime column
I have never used DateSerial() before, I will have a look into it. Thanks
Graeme McGilvray

ASKER
So checking out DateSerial() here: https://www.w3schools.com/asp/func_dateserial.asp

Seems similar to what i produced, however on my current system is wanted

DateSerial(Year(Date()),Day(Date()),Month(Date()))

Open in new window

And that gave the US date format...
ste5an

DateSerial() returns a Date/Time, not a Jet/Ace date literal.

And that gave the US date format...
No. The format came from the tool to display that value.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck