sql with Access and dates in UK format (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

yogaboy -> sql with Access and dates in UK format (12/29/2004 23:56:57)

Before I kill myself in frustration, someone please please please tell me how to use BETWEEN and get it to work

Here's my much maligned code

myNow = Date()
sDate = sMonth & "-" & 01 & "-" & sYear

WHERE (KeyDocuments.DatePublished) BETWEEN #" & myNow & "# AND #" & sDate & "#)"


The dates are stored as strings in the Access database in the form dd-mmm-yyyy eg 31-12-2004

that stuff with sDate above is me trying to get my dates in the right format for the query, but I have tried so many different formats and sql statements and I either get errors or everything with a date spewing back at me.

---------------------------------------------------
I thought I'd be clever and tried this

(KeyDocuments.DatePublished BETWEEN #" & myNow & "# AND #" & DateAdd("m",-1,myNow)& "#)"

and it appeared to work, bringing back everything since the beginning of Nov until now. But it also brings back 2 other records, one with 11-Mar-2004, and 11-Jan-2004. What's that about?
--------------------------------------------------------------------
my final post before I crawl into bed, badly beaten by Microsoft...

(KeyDocuments.DatePublished BETWEEN #" & myNow & "# AND #" & DateAdd("m",-3,myNow)& "#)"

brings back everything in Nov 2004, Dec 2004, and anything in January from any year as long as it's the first, and a few things from April and March randomly thrown in. How nice.

----------------------

I thought I'd collate my posts or no one will read them - it reads like the log of a man set adrift at sea!!!



Spooky -> RE: sql with Access and dates in UK format (12/30/2004 14:16:39)

What is sDate supposed to represent?
Try and use the international format yyyy/mm/dd via the function below 

Function DateFormat(dt)
	DateFormat = year(dt)&"/"&left("00",2-len(month(dt)))&month(dt)&"/"&left("00",2-len(day(dt)))&day(dt) &" " & formatdatetime(dt,4)
End Function

myNow =DateFormat(Date())



yogaboy -> RE: sql with Access and dates in UK format (12/30/2004 17:59:43)

I've found the source of the error, and surprise surprise, it was my fault!

I was using a function to enter everything into the database as format dd-mm-yyyy, and that worked very nicely. It's just a pity I set the database field as type text, and not type date. As soon as I changed that, it worked!

Thanks for the 'n'th time Spooky.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.046875