Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-18 : 10:46:19
|
| Kit writes "I am trying to create a search for rows in a SQL 7.0 database that match an inputted date criteria, from an ASP page. Here is my code:Set objCommand = Server.CreateObject("ADODB.Command")objCommand.CommandType = 1objCommand.ActiveConnection = Application("connectionString")sql = "SELECT * FROM " & tablesAndIds(i, 0)sql = sql & " WHERE " & tableField & " LIKE '01/01/1970'"objCommand.CommandText = sqlSet objRS = objCommand.ExecuteI never get anything returned. I have tried hardcoding the date, using a string, and a date type (CDate()). What gives?" |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-18 : 13:40:49
|
| Is the field of datetime or smalldatetime data type (as opposed to varchar)? If so, I think you're running into a formatting conflict. The default display (for U.S.) is 1970-01-01 00:00:00. I assume that your date field has a time element other than 0, and you are trying to get all records for that date, regardless of time. If your time element is always 0, then you can just do a field = value instead of LIKE.If you have non-zero times, then you can either convert the date field like this:select * FROM table WHERE convert(varchar(11), DateField, 101) LIKE '1/1/1970'OR, do a select >= '1/1/1970' and < '1/2/1970'By the way, you might want to get in the habit of using ISO standard date format YYYYMMDD just to avoid potential problems with international date formats.--------------------------------There's a new General in town...Edited by - AjarnMark on 01/18/2002 13:42:43 |
 |
|
|
|
|
|
|
|
Searching For A Date