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"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 ?????
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)
i got a field named register_date, its input a table named member when a user register in my system, What im trying to do is i want to display daily registration in seperate table, anyway the database im using is mdb file, and data type for register_date is data/time.
so below is my coding, but i can get the table to be displayed, can someone tell me what wrong with my coding? Code:
I would like to grab the earliest date of any of the records that gets drawn from this SQL statement:
datesql = "SELECT top 1 gamedate FROM tblNFLGameData WHERE season = '" & seasonZ & "' and gameweek = '" & gameweekZ & "'"
..this works for some of the simple stuff I'm doing now, but I am going to have more involved calculations as far as dates and such. Does "Select Top (whatever value and fieldname) FROM (table name)" have any rules pertaining to Date data types in SQL?
I'm trying to post a value from a form field into a database field of type Date/Time formated to short date.
Even though the value from the form comes in short date style ie 19/06/2004 I'm getting a type mismatch error
I 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.
I have this code Code:
varDate = Date()
SQL="SELECT id,name,eventtype,location,fromdate,todate FROM events WHERE publish='1' and fromDate > "& varDate &" ORDER BY fromdate asc"
fromDate is a Date/Time field in access. What i want to do is pick out any data which has a fromDate which is greater than today but the sql command isnt picking out any data. does anyone know how i could change my script to work.
I 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.
I do a SELECT * from table command in an ASP page to build a text file
out on our server, but the export is not to allow a field name rows of
records. The first thing I get is a row with all the field names. Why
do these come in if they are not part of the table records? How do I
eliminate this from being produced? Code:
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 am using a Relational database for my site and i have the following tables:
FIXTURES, GAMEDATES
the fields in FIXTURES are:
GameDate
Home
Away
(All of the above are numerical fields)
the fields in GAMEDATES are:
DateID(integer)
GameDate(#Date Field#)
What i have done is in the GAMEDATE field in the FIXTURES table i have used the ID number of the date that i wish to assign to that record - using a LEFT JOIN to display the actual date that is pulled from the GAMEDATES table (That which corresponds to the relevant id used in Fixtures)
The problem is that in some of my code i want to display records using sql and sometimes in the where clause i use the following:
WHERE Fixtures.GameDate<=(Date())
obviously meaning WHERE GAMEDATE is LESS THAN or EQUAL to TODAY.
The thing is when i use the join it does not use the where clause and displays all of the records in the FIXTURES table.
Does any one know how to get around this, i have not received any error messages it just isnt displaying the right data
I have data in my table column called 'theDate' in this format 'dd/mm/yy' and it is set to 'text'. (sqlServer 2005)
what I would like to do is: Code:
select * from tbl_name order by 'theDate' ASC
But I am having difficulty with this. I probably haven't set it up properly and wondering where it has all went wrong!
How can I select period of date form another period of date?
For Example: I wanaa select 14/2/2006 - 17/2/2006 form 1/1/2006 - 18/3/2006.
i got a problem dealing with this sql statement, i data stored in a table in 3/13/2006 format, and i input this data into table by calling date() using asp, but when i try to run an sql statement by using this sql statement,
a="2006/3/5"
b="2006/3/7"
sql="select * from userlog where AccessTime between '"&a&"' and '"&b&"'"
response.write sql
set rt = conn.execute(sql)
if rt.eof then
response.write "no record"
else
response.write "got record"
end if
i got no record display on my page, may i know what to do to get the data from the tabel?do i need to change the structure when i insert this date or do i need to change the structure of my variable a and b in order to perform this search correctly?
Ok I know there are lots of posts out there for this problem but no matter what I try I can not seem to get it to work
What is wrong with this syntax
sSQL = "SELECT * FROM serviceHostingMatrix WHERE start_date = "# & startDate & #";"
startDate is a variable that I filled from another tables date field
getting this error
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
I am trying to retrieve a date field this is the code
strQ ="SELECT * FROM WaterCompanies ORDER BY WaterCompany"
set objRS=objConn.Execute(strQ)
This is where I am using the date
Response.Write "<TD>"
Reponse.Write objRS("JoinDate")
Response.Write "</TD></TR>"
all the other fields populate so I know I have a good connection.
this is the error
Microsoft VBScript runtime error '800a01a8'
Object required: '' /waterbilling.asp, line 76 <--- this is the response.write objRS("JoinDate") line
i need help formating todays date minus one.
i can get this 1212006 but i need 12012006
how to pre populate the date field in an asp form which is using SQL database?
View Replies View RelatedI have a few date fields on my form. The backend is SQL and the fields are set to smalldatetime. Adding records into these fields is not a problem. This is my code:
if Request.form("ScheduledTrainingDates") = "" then
else
objRS("ScheduledTrainingDates")=Request.form("ScheduledTrainingDates")
end if
The problem is updating the records when I got to edit them. Let's say I have a value in for ScheduledTrainingDates textbox, and I want to clear the value and leave it blank and save it to the database. How do I do this?
I have a form field which asks for a date to be entered in the format of dd/mm/yyyy. Now, I've successfully programmed it so that you can only enter a date in the present or future but not in the past. However, as it stands, it also means the user could enter an unrealistic date such as 12/12/2070...I don't want this.
What I'm after is some ASP code which only allows the user to enter a date that occurs between the present day and say, the next month. So if the user was to enter 05/03/06 then the valid dates that would be acceptable would be any date before 05/04/06 i.e. One month.
I'm trying to create a search page with a beginning and ending date, but I can't seem to get it to work. I am using SQL Server. Here is my statement:
SELECT *
FROM dbo.REQUEST2
WHERE REQUEST_DATE BETWEEN ' " & Request.Form("txtBeginDate") & " ' AND ' " & Request.Form("txtEndDate") & " '
This statement is not returning any records when I know there are existing records that fit the criteria that I'm entering.
I have a form where I offer the user to enter a 'fromDate' and a 'toDate' whereby I had hopes that it would help return records where the date was between those two values. My SQL query ends with: Code:
AND Date < " & request("fromDate") & " AND Date > " & request("toDate") & "
and a typical date entry in the table looks like: 2/8/2005 .
Need some help with comparing a date field retrieved from an access date base to the current date. Basically I'm looking to create and if statement like:
If strArticleDate < = Current Date then
else
I have an asp page that uses a calendar.js (pop-up) file to add an
exact date format in the text field (txtDDate). My problem is I need
some javascript that sets an alert that does not allow them to select
today.
example:
var dtToday = Date()
if(document.frmSoftware.txtDDate.value == dtToday)
{
alert("You cannot select same day distributions. Please enter a new
value in the "Delivery Date" field.");
return false
}
But dtToday seems to be blank....How can I get dtToday value? Also,
when I set the dtToday value outside javascript and used a
Response.Write to see what format Date() came up with - it gave me
1/24/2005 - but the calendar pop-up gives me 01/24/2005....could this
also be the issue?
I cannot get the date format correctly in dynamic sql statement, after trying various ways of handling it. I need some help with the date format in the following dynamic sql statement. Any help is appreciated in advance. While running the asp page, I still get an error as Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'Format'
DYNAMIC SQL STATEMENT:
sql = " INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense, "
sql = sql & "TravelExpense, PersonnelExpense)"
sql = sql & " VALUES('" & l_ENO & "', " & Format(l_Date,
"#mm/dd/yyyy#") & ", " & l_contractedserviceexpense & ", "
&l_travelexpense & ", " &l_personnelexpense & ")"
I've got a table with three fields:
ref (Autonumber)
notice (memo)
expdt (Date)
I'm trying to use an SQL statement to delete all records where expdt < the current date (Now). Here is the code I'm using:
<%
If Request.Querystring("act") = "clean" Then
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open ConnStr
strExpdt = cdate(Now())
sql = "DELETE FROM tblNboard WHERE expdt < " & Now()
conn.Execute sql
conn.close
set conn = nothing
strResponse = "All expired notices have successfully been deleted."
End If
%>
However, this just gives me the following error:
Syntax error (missing operator) in query expression 'expdt < 11/12/2003 19:54:25'.
What am I doing wrong??
I have several (three) CSV files each with different columns and dissimilar data. The only common column is transaction date. What I'd like to do is combine the data chronologically. I can't do a join query because the transaction dates do not line up from file to file and no other columns will match either.
I just want all the rows from the three different files put in chronological order in one place understanding that my columns wont line up since the source columns are different. I've exhausted every effort on the SQL side to combine these, but I'm thinking maybe there's a way to do it with FSO or something else.
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.
suggest the correct way to store the date & time in SQL Server? I want to store the date and time a record was created. What should the field type be in SQL Server? How can I insert the current date/time into the table using SQL? I used to have date/time field type in access and use the following code
currTime = Now()
SQL="Insert into <table> (id, recDate) Values (" & sId & ", #" & currTime &
"#)"
conn.execute(SQL)
what the field type I should be using in SQL Server to store this date and also what should I replace the line currTime=Now() to?
In my db, I have a date field wich as the format yyyy-mm-dd but when I receive the value from the DB, I'd like to change it to dd-mm-yyyy... How can I do this?
View Replies View RelatedI've declared a variable for the current date and want to use the UPDATE function to update the date field in a single record.
UPDATE lms_users Set leaver_date ="&MakeDate" WHERE user_id="473"
I know I have the date format in the variable as if I response.write it to the screen is displays correctly (28/08/2004). I must be doing something really silly, but can't see it.
how to validate a DATE field i have written my own javscript method but it's not considering all apects so is there any readymade method to check date.
View Replies View Related