|
|
02-15-2006, 04:08 PM | #1 |
Member
Join Date: Jan 2005
Location: New York, NY
Posts: 32
|
Date handling query in SQL
Folks:
I've inherited a SQL database in which a date is kept in a datetime-type field. I have no idea how to do a query on it. My first (and only) thought was to try: Code:
select user_name from AHmembers where signup_date > "2006-02-13 23:59:59" |
02-15-2006, 05:09 PM | #2 |
Staff
Join Date: Feb 2005
Location: In the Heart of the English Lake District
Posts: 1,381
|
First thing to check before commenting on the syntax is how the date is stored in the db. If this is MySQL on Unix then it will very likely be as a Unix timestamp. There is an online date converter here.
__________________ Kelvyn Web site design, hosting and marketing, Keswick in the UK Lake District If you are planning a visit to Keswick then try Keswick Tourist Information website |
02-15-2006, 05:25 PM | #3 | |
Member
Join Date: Jan 2005
Location: New York, NY
Posts: 32
|
Hi, Kelvyn:
Quote:
|
|
02-15-2006, 10:51 PM | #4 |
Member
Join Date: Dec 2004
Location: In the heart of Lake Minnetonka
Posts: 337
|
Did you try using SINGLE quotes around the date-time, i.e.
SELECT user_name from AHmembers where signup_date > '2006-02-13 23:59:59' Note that double-quoted strings (i.e., "string") and single-quoted strings ('string') are not the same in SQL. |
02-17-2006, 03:19 PM | #5 |
Member
Join Date: Dec 2004
Location: In the heart of Lake Minnetonka
Posts: 337
|
table 'invoices' has a field named 'start' which is of type 'datetime'. The following SQL statements using either single or double quotes worked successfully...
SELECT * FROM 'invoices' WHERE start > '2006-01-01 00:00:00' SELECT * FROM 'invoices' WHERE start < "2006-01-01 00:00:00" |
02-17-2006, 02:44 PM | #6 |
Member
Join Date: Jan 2005
Location: North of Swindon in the UK on the edge of the Cotswolds
Posts: 997
|
Its along time since i struggled with SQL - can u cut and paste the data from the actual field to make sure it is right
I most likely would try and find a field which is easy and test the query Peter |
07-02-2007, 11:59 PM | #7 | |
Member
Join Date: Jul 2007
Posts: 1
|
Quote:
--+-- You could try this statement which works on my MSDE SQL database: Code:
SELECT * FROM Orders WHERE ([Order Date] < '2006-01-01 00:00:00') Note: The Order Date field name is enclosed within brackets because the field name contains a space. An alternative method, if you have design control over the database, is to introduce an additional field, Order Date Int which contains the date data calculated as follows: Code:
Year([Order Date]) & Month([Order Date]) & Day([Order Date]) |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Getting a good date | Richard Waller | The Corner Pub | 6 | 08-23-2006 07:13 AM |
PowerPoint query | klare | Print Design | 9 | 04-17-2005 07:02 PM |