473,320 Members | 1,953 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

change the date format in sql query, mm/dd/yyyy to dd/mm/yyyy (SQL)

Expand|Select|Wrap|Line Numbers
  1. SELECT receivedDate
  2. FROM purchase
  3. WHERE receivedDate Between #1/2/2011# And #1/10/2011#
i hav a query as above, the date i stored in the table was dd/mm/yyyy, but the format for query above is mm/dd/yyyy.
and i only can use "#" operator to search for date.
can pls advice me how to change the format in the query?
thx in advance.
Apr 16 '11 #1
2 4464
Stewart Ross
2,545 Expert Mod 2GB
Assuming you have stored your date as a date/time value, not a text value, there is nothing at all wrong with what you have already posted. Date values are stored as whole numbers counting the number of days from a given date (around 1 Jan 1900). They are formatted differently for display purposes according to the norms for the country in which you live, but this makes no difference at all to how they are stored.

Date literals in SQL queries follow the ANSI standard of date presentatation - #mm/dd/yyyy# - but since this is exactly what you have posted I see nothing wrong at all with the SQL you show!

You might find that Access is trying to be too helpful; #1/2/2011# may be being interpreted by Access (incorrectly) as #2/1/2011# in dd/mm/yyyy form, if dd/mm/yyyy reflects your country settings. You can check this by viewing the query in SQL view - it should be correctly shown in #mm/dd/yyyy# form, even if entered in the query editor in #dd/mm/yyyy# form.

-Stewart
Apr 16 '11 #2
thx steward, dun worry about it anymore, already solved using Format CDate()
Apr 16 '11 #3

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

Similar topics

3
by: Alistair | last post by:
it's the idiot with his query strings again...."yippeee" I hear aaron shout this time the problem is with a date format query I have a query string thus strSQL = "SELECT * FROM users where...
1
by: Miso Hlavac | last post by:
hello, I need to change date format from mm.dd.yyyy to dd.mm.yyyy But only for one database. In other databases I need mm.dd.yyyy is it possible set this format permanently??? thanx... miso
4
by: Claudia Fong | last post by:
Hi Is there a way to change a date format dd/mm/yyyy to m/d/yy? I have a textBox where the user should put a date, but no matter what format the user input dd/mm/yyyy or m/d/yy, I need to...
2
by: Billy | last post by:
This string is supposed to provide all records from an MDB database that match the courier and date specified in the query. I Response.Write the query and I get a date as 1/27/2007. The date...
2
by: siva33 | last post by:
i am getting problem with the date. i will explain my problem detailed. plz solve for me, plzzz send me the code if possible aspx file and aspx.vb file i have taken a text box to enter the date...
2
by: mrteno | last post by:
I'm using mysql, and used field with DATE type the default date format in my database is yyyy/mm/dd how to change the date format to dd/mm/yyyy
3
by: Bface | last post by:
Hi all, Hope everyone had a good holiday. I am having a difficult time changing the date format of a field from Excel. I have never had this problem before. I link the excel spreadsheet to my DB,...
1
by: Yas | last post by:
Hello I've got the following Javascript for a web calendar popup that outputs the date in 4/24/2008 (m/d/yyyy). How can i modify it to output dd/mm/yyyy e.g. 24/04/2008. So all outputs are in...
0
by: modolamara | last post by:
Hi people, im importing a date column from a mysql database to a datagridview but it automatically changes the date format from 'YYYY-MM-DD' (Mysql format) to 'MM/DD/YYYY' since the user has...
5
by: Vasudha Balyan | last post by:
I am using system.datetime.today to get today's date. I want to send this value to a date field in sql server in the mm/dd/yyyy format, but it is sending the date in mm/d/yyyy format. It's not...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.