DTP


 
Lively discussions on the graphic arts and publishing — in print or on the web


Go Back   Desktop Publishing Forum > General Discussions > Web Site Building & Maintenance

Reply
 
Thread Tools Display Modes
Old 02-15-2006, 04:08 PM   #1
BinkyM
Member
 
BinkyM's Avatar
 
Join Date: Jan 2005
Location: New York, NY
Posts: 32
Default 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"
but that didn't work. I can't find anything in the MySQL 5.0 documentation that tells me how to accomplish what it is I'm looking to do. I'd be way grateful for a pointer!

   
__________________
Bink of Snarkish, intelligent discussion forums for adults with their clothes on
BinkyM is offline   Reply With Quote
Old 02-15-2006, 05:09 PM   #2
Kelvyn
Staff
 
Kelvyn's Avatar
 
Join Date: Feb 2005
Location: In the Heart of the English Lake District
Posts: 1,381
Default

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

Kelvyn is offline   Reply With Quote
Old 02-15-2006, 05:25 PM   #3
BinkyM
Member
 
BinkyM's Avatar
 
Join Date: Jan 2005
Location: New York, NY
Posts: 32
Default

Hi, Kelvyn:

Quote:
First thing to check before commenting on the syntax is how the date is stored in the db
It's just like I said: 2006-02-13 23:59:59. I'm not sure how that converter page will help me; what I wanna do is to pull out of the database everyone whose signup_date field is after 2006-02-13 23:59:59.

   
__________________
Bink of Snarkish, intelligent discussion forums for adults with their clothes on
BinkyM is offline   Reply With Quote
Old 02-15-2006, 10:51 PM   #4
gary
Member
 
Join Date: Dec 2004
Location: In the heart of Lake Minnetonka
Posts: 337
Default

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.
gary is offline   Reply With Quote
Old 02-17-2006, 03:19 PM   #5
gary
Member
 
Join Date: Dec 2004
Location: In the heart of Lake Minnetonka
Posts: 337
Default

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"
gary is offline   Reply With Quote
Old 02-17-2006, 02:44 PM   #6
PeterArnel
Member
 
Join Date: Jan 2005
Location: North of Swindon in the UK on the edge of the Cotswolds
Posts: 997
Default

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
PeterArnel is offline   Reply With Quote
Old 07-02-2007, 11:59 PM   #7
spock
Member
 
Join Date: Jul 2007
Posts: 1
Default

Quote:
Originally Posted by BinkyM View Post
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"
but that didn't work. I can't find anything in the MySQL 5.0 documentation that tells me how to accomplish what it is I'm looking to do. I'd be way grateful for a pointer!
--+--

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')
The field values for [Order Date] are stored in the DD-MM-YYYY format as the smalldatetime data type.

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])
This should create, for 5th June 2007, the number value: 20070605 which can easily be used for date searching and ordering. Then your query would focus on integer entities which are much easier to handle.
spock is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

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


All times are GMT -8. The time now is 11:54 PM.


Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Contents copyright 2004–2019 Desktop Publishing Forum and its members.