When I try to select records based on a date I get nothing back from the db. For example when I run the query:
SELECT * FROM trainingCalendar2 WHERE startDate > #04/03/2005# AND courseType = '1' AND numOfPlaces > 0 AND inuse = 1;
using the SQL editor in access and then view it in design view, the date is changed to 03/04/2005.
I have built an online booking system for a hotel and all is working fine except for the fact that the ‘From’ and ‘To’ dates are saved in the MS Access database in US format. The user selects from a date picker pop, which places the in the textfield as English format e.g. 02/12/2007.
However when I try and save this value into the date column in my DB I obviously receive an error because the formats are different. Please could someone advise me on the best way to convert an English date to a US in order to insert this into the DB using ASP & MS Access.
At the moment I am using plain text as the column type, but this is not ideal as I want to start to run queries against the 2 dates.
I have an access database which has a date field. I've entered all of the dates in valid UK format (dd/mm/yyyy). An example of the table looks like this:
Id| SeminarName | SeminarDate
1 | Introduction | 18/02/2006
2 | Other stuff | 19/02/2006
When I do:
SELECT * FROM tblSeminar WHERE SeminarDate=#18/02/2006#;
It doesn't return any records. If I remove the WHERE SeminarDate... it works fine, so something is causing the date not to match.
I'm using <%Session.lcid = 2057%> at the top of all of my pages.
Before everyone says go to this page read this page, I am new to the asp side of development and so still in the learning stage.
The Problem.
I have a website using asp and a ms access database. I have a couple of forms that get entered into the database and then update. but with this the date is added automatically into the database.
So when someone does a search it comes up what date it was posted. The trouble is it comes out in american format, where as i want it in british format. I have done what it says in some of the suggestions on the site but I either get:
nothing,
and error
or a date saying 30/12/1899.
i am trying to do a search on a database within a date range but the query pulls in details from before the time submitted.
For example :
records : 01/05/2006 Test
06/06/2006 Test 2
06/06/2006 Test 2
If i do a search between 06/06/2006 and 06/06/2006 i will get the 01/05/2006 record as well as the 06/06/2006 records, my code is as follows. Code:
I have a list of events (MAXDOB)I have a persons date of birth (DOB)Some events are only open to people under a certain age.How do i say:
if DOB > MAXDOB Then
response.write("You are too old for this event")
end if
Also what format does the date need to be in for this to work? I have been using dd/mm/yyyy.
I have created a DB in access that has the 2005 NFL season in it. I would like to display the current weeks games. I have been trying things along these lines.
<% if now() = SELECT * FROM NFLsched WHERE (scheddte BETWEEN #01-Jun-2005# AND #12-aug-2005#) then response.write(SELECT NFLSched.Game, NFLSched.EST, NFLSched.scheddte, NFLSched.Week FROM NFLSched WHERE (((NFLSched.Week)="week 1"))
Not having much luck. Can anyone steer me in the right direction?
I have an Access database with a date field The format that we store values is dd/mm/yyyy (greek)The problem is that i can't retreive the correct dates because the queries
return only the english format dates. How to format the query to english date format (mm/dd/yyyy)?
I have a db that has a field 'enddate' and I want to UPDATE all the records there 'enddate' has passed... i.e. the record has expired.
The trouble I get is that no matter what the date, it marks all the records as expired, even though some have not... It's nothing to do with the 'impressions' or 'maximps' fields as I have taken those out and the problem still happens. I have a good testing database set up, and it should be showing some results...
This is my code:.....
I am storing dates in a database. WHen i come to do sql queries on the dates access is interpreting them as US format (mm/dd/yyyy). When the date is stored in the db it is in the correct uk format (dd/mm/yyyy) but when asp passes the queries to access the date format changes if the day is above 13.
For example if the date being searched is 10/2/2006 (10th feb 2006) it is searching the database for 2nd October 2006. If the i pass is 13/02/2006 (13th feb 2006) it searches it correctly.
I have tried setting the session.lcid to 2057 but this makes no difference. I read somewhere that access automatically treates dates as US format and only tries another format if the us format doesnt make sense. Is there any way around this?
I have more than one date field on my ASP forms (and in the sister MS Access table) I can not write (created the record in Access table) to the table UNLESS there is date data in ALL the date fields on the ASP form.
I need to be able to have the user enter only one date, then do back at a later time and enter the other date.I am using the short date format in Access table (MM/DD/YYYY) When there is only one date field (on ASP form and in Access table) the date is written and the record is created.
the date format in my database(ms access) used to be mm/dd/yyyy and i've changed to dd/mm/yyyy. Now the date appears fine in the db. however wen i retrieve the data and displays in on a form, it is back to it's default format(mm/dd/yyyy). is there anyway whereby i can change it?maybe something like FormatNumber(rs("FromDate")) or whatsoever?
View Replies View RelatedI have a date field which I use var1 = new Date() to save under MS-Access field with "text" to be the data type (date/time is NOT accepting). I have tried using
var2 = new Date(oRS("field1"))
var2.getMonth() does not get the right saved month
how to retrieve the date and time using Javascript? Or I should use var2 = new String(oRS("field1")) The result of var2 would be "Thu Mar 31 11:11:11 EST 2005". Is checking the location of spaces the only way to get date and time?
I'm trying to take a date from a form and insert it into an Access 2000 database with ASP
Here's the code:
frmClubId = Request.Form("Names")
frmDate = Request.Form("Date")
sqlStatement = "INSERT INTO testDates(ClubId,Date) VALUES(" & frmClubId & ", #" & frmDate & "#)"
Set rs = conn.Execute(sqlStatement)
I've already Response.Write the sql, so I know that frmClubId is and integer and frmDate is mm/dd/yyyy.
Basically I'm trying to save and retrieve a UK date (dd/mm/yyy) to an Access database using asp. The database resides on a server located in the US
Things I've tried so far:
-- Specifiying Session.LCID both in Global.asa and individual ASP pages
-- Using Custom formatting to format date before entering into database (using SPLIT function)
-- Using custom formatting to format date AFTER retreiveing from database
The problem is that the date seems to change how it's stored in the database. If I entered "16/02/1982", it would be saved in teh database as '02/16/1982".. but if I entered "03/04/2005" it would save it as this.. So fomatting after retreiveing the date from the DB just messes everything up!
Does anyone have any ideas how to solve this other than moving to a UK server?
I try using the following sql statement..
strSQL = "UPDATE Messages(TABLE) SET Date=now() where MessageID=25"
The error given to me:
Syntax error in UPDATE statement.
I'm doing an update of the date already stored in my Access. How do i
resolve this problem??
I have an access db called boards with a table called notes and I want to add a time and date data to the table. The code for adding data to the db is: Code:
View Replies View RelatedI am pulling legislation information and expiry dates from an access database and posting to the web. The expiry dates in access are formatted as Month Day, Year (EG.. January 1, 2006) But when they are pulled from the database to the web, they come out as 01/01/06. I know that the format we have in access is simply a mask, however we require that format be carried through.
This is likely a simple solution, however I am new to ASP and can't seem to locate any information on this. I have searched the forum for related info on this but can't find anything that relates to my specific situation.
Here's the code we are using so far...
Code:
<%
Do While NOT Recordset.Eof 'i.e. carry on looping through while there are records
Response.write "<tr><td>"'open row and first cell
Response.write Recordset("Legislation")
Response.write "</td>"'close first cell
Response.write "<td>"'open second cell
Response.write Recordset("Repealed")
Response.write " "
Response.write Recordset("Expiry_Date")
Response.write "</td></tr>" 'close row
Recordset.MoveNext 'move on to the next record
Loop
%>
I have also found and implemented the following code:
Code:
dt=recordset("Expiry_date")
dt=Month(dt)&"/"&day(dt)&"/"&year(dt)
response.write(dt)
This lets me change the positions of the numbers, but I can't figure out how to make it display the full month name and full year.
having issues submitting dates to a mysql db, ive converted the database from access to mysql and im having issues submitting dates. this is what i have when submitting to access
Code:
I have a created a cost reporting system which shows a level-breakdown
report. There are three levels in the report the lowest level holds the cost
items that are entered by the user these records also have an Entry Date.
The level above that is a Sum of the lowest level and the top level is the
Sum of the second level. I created the solution using three queries in
Access. I then use the queries in ASP to generate the report. But now I need
to use the Entry Date at the lowest level to re-generate the report with
values from the StartDate to the EntryDate (which is entered by the user).
Question is, can I do this using the construction I use, or do I have to
pull my queries to the ASP code and include the dynamic EntryDate? How do I
solve this?
I just recently created a html form and one of the fields required a date. I want to make it so it will insert the date when the form is being filled out. For example, if I fill out the form today I want today's date on it.
I am going to connect this form to a MS access database, the database also has a field "date". How would I like all these together? Also, I want to send a notice to a specific email whenever someone submits the form.
I have a database and i want to work out the sum of the discount field where the id is lets say 1 and the date is greater than 01/02/2006
The database hase records older than 01/02/2006 but I dont want them just the 1s dated 01/02/2006 to now
set RS3 = conn3.execute("select SUM(discount) from restaurantaccounts WHERE restaurantid=1 AND datepaid >=01/02/2006").
I have an ASP form that has a field that automatically places the current date into it. It uses the function Date().
The problem I am having is when the form is submitted into my Access database the date is coming out wrong in the Access field. The datatype for the field is Date/Time but it still is not coming out right.
Example: the date in the ASP form shows as 12/19/2006 and the entry in the Access form shows 12:00:27 AM. If I change the format of the Date/Time field to Short Date, the result is: 12/30/1899.
Does anyone know how I can get the data that is posted to the Access database to come out the same as the ASP form?
I am developing a simple booking system where I had a booking date. The problem is I want to make sure that the user must book 3 days in advanced for certain room.
I have tried to use DateDiff but it seems doesn't work. Actually i just 1 to make sure that the user must book 3 days in advanced from the current date. I am wondering whether there is anything goes wrong in my datediff function. Code:
i'm new here and i'd like to ask if there's someone who knows how to compare date() function and date from database with date/time type inside select statement so i don't have to do if..then..else
View Replies View Relatedsql = "UPDATE Marknad SET" _
& " Typ ='" & Typ & "'" _
& ", Modell ='" & Modell & "'" _
& ", Arsmodell ='" & Arsmodell & "'" _
& ", Beskrivning ='" & Beskrivning & "'" _
& ", Stad ='" & Stad & "'" _
& ", Prisidé ='" & Priside & "'" _
& ", Fornamn ='" & Fornamn & "'" _
& ", Efternamn ='" & Efternamn & "'" _
& ", Telefon ='" & Telefon & "'" _
& ", Epost ='" & Epost & "'" _
& ", Datum ='" & Datum & "'" _
& " WHERE (((Marknad.Annonsnr)=" & Request.QueryString("id") & "))"
DatabaseConnection.Execute sql
---
Above does work as I want it to do BUT it inserts 1/8/2006 instead of 8/1/2006 as it should!
The best thing would be to exclude the date from updating at all, but when taking away the line:
& ", Datum ='" & Datum & "'" _
It gives an error... how do I solve this?
I forgot, you might want to see this:
Datum = date
In my page i have two combobox. one for year and one for week numbers.Now my prob is
1.when i select a year from combo box 1, second combox box will display the number of weeks in that year.eg if i select 2005 it display me list from 1 to 53 as there are 53 weeks in 2005.
2.when i select a weeknumber from combobox 2 and press submit button it ll display start date and end date of entered week.
I am trying to compare two dates. My problem is no matter what the value of mydate, it is still fall in the first IF statement condition (even if mydate is 10/10/2004). Any advise for this matter?
mydate = "6/6/"&year(date)
IF date() <= mydate THEN
response.write "first half of the year"
ELSE
response.write "second half of the year"
END IF
I am trying to construct an SQL statement that can grab all of the entries out of a table, where the Date field consists of today's date. The Date field is called 'DateTime' - and it is in a General Date format (DD/MM/YYYY HH:MM:SS). I obviusly want to use an SQL statement like this:
SELECT * FROM tblExample WHERE (DateTime = Date());
However, that wont work because Date() will only return a short date (DD/MM/YYYY).
I would like to modify this so that the first available date that can be selected is five BUSINESS days out. The script only shows Mon-Fri so if the date is Tue Jul 13, 2004, Tue Jul 20, 2004 needs to be displayed. Code:
View Replies View RelatedI am trying to compare a date in my database to the current date. MySQL statement is:
sqltemp5="select * from users where dateclosed <= '" & date & "'"
What I am trying to accomplish is exclude records where the dateclosed field is older than todays date. Such as if dateclosed = 6/30/2004 then today being 08/25/2004 that record should be excluded.
In a connection string, I have the following:
sql3 = "SELECT DISTINCT State FROM Property Where TypeID = '" & srch_cat & "'"
In the database, is a field called expiredDate I am trying to show only records whose expiredDates are greater than todays date, but I am messing up somewhere. The values in the fields are as this: 3/7/03 (or something like that...no time is added to it)
"SELECT id, title FROM Businesses WHERE Date_Added >= " &
Request.Form("Since_Date")
This SQL string returns all records from the table Businesses and does
not seem to be able to recognise Request.Form("Since_Date") as a valid
date. The Since_Date value is entered into a form field as
"01-Jan-2005" and appears to be treated as a date; when viewing the
SQL statement(with a response.write) it shows the date as 1/1/2005
even if it is entered into the form as 2005/1/1
I've also tried CDate(Request.Form("Since_Date")) with no luck,
although this was the treatment that worked when the SQL statement was
running in an .idc file.
Date_Added is a regular DateTime field in an Access 2000 database
Any suggestions please ?????