SQL Date Querying

finnpark

Hi,

I want to be able to query results in a table by date.

Should I use datetime or should I use something else. I only want to query dates in the format say where date_var < "05/04/2006" and > "01/01/2006";

Something like that. What is the best way to store dates and to query dates?

Thanks for any help:)

Kai

You didnt mention what Database type you use so im assuming SQL Server. Dates would normally be stored as Datetime or smalldatetime.

Behind the scenes SQL server stores dates as numbers, so you can compare dates using >, < , >= , <=. But for adding Dates and comparing the differences between dates you should read up on DateAdd and DateDiff functions.

You should convert a string to a datetime before using it in a query. So your query should look like this:
where date_var < Convert(datetime,'05/04/2006',103) and date_var > Convert(datetime,'01/01/2006',103)

Note the ' rather than " for Sql server and also the 103 addiion to the function which specifys the date format as dd/MM/YYYY, rather than american or other formats.

finnpark

Kai wrote:

You didnt mention what Database type you use so im assuming SQL Server. Dates would normally be stored as Datetime or smalldatetime.

Behind the scenes SQL server stores dates as numbers, so you can compare dates using >, < , >= , <=. But for adding Dates and comparing the differences between dates you should read up on DateAdd and DateDiff functions.

You should convert a string to a datetime before using it in a query. So your query should look like this:
where date_var < Convert(datetime,'05/04/2006',103) and date_var > Convert(datetime,'01/01/2006',103)

Note the ' rather than " for Sql server and also the 103 addiion to the function which specifys the date format as dd/MM/YYYY, rather than american or other formats.

Cheers.

Im using a MYSQL databasse.

At the moment what Im doing is having 0000-00-00 00:00:00 when I display the database contents for the date. Im writing 03/05/2006 in that raw format to the database and it is stored as 0000-00-00 00:00:00.

So instead of storing 03/05/2006 I should store Convert(datetime,'03/05/2006',103)? I think that is what you are saying?

The when Im querying the database I need to use a similar conversion?

Kai

The Convert function is an MS SQL server function. it wont be the same in MySql but there should be an equivalent. I dont work with MySql but im sure some of the other guys in here do and will be able to help.

Devoy

If you are only interested in dates, you should probably use the "DATE" data type for your columns as it'll require less space than a "DATETIME" data type.

As for inserting/updating dates, you can do something like:
... SET MyDateColumn='20060324'
or, to set it to the current date
... SET MyDateColumn=NOW()

Dr Pepper

The default format for inputting dates in mySQL is YYYY/MM/DD

To insert dates use the format: '2006/03/21 01:59:30' You can leave out the time part if you want and it will just be entered as the default time: 00:00:00 or as somebody else suggested, just use the 'date' type instead of datetime

To select dates in a range, you can use 'between':
where date_var between '2006/01/01' and '2006/04/05';

Also, as somebody else suggested, the NOW() function is very handy for inserting the current date/time: insert into blah (date_var) values (NOW());

The date_format method is also very handy to select dates in whatever format you like.. See here for a useful reference of mySQL date functions