CODEWITHSUNDEEP
sqlsql-serveroracle-database
user49126
user49126

Reputation: 1863

Filtering by date

I have a Date type column where are values in this format

1.1.2012 10:10:11

I need to create a filter which would filter these values by day, month and year.

I've tried 

where like '% 1.1.2012 %'

but this seems to not working.

Upvotes: 0

Views: 332

Answers (3)

chris
chris

Reputation: 565

In MSSQL, you can use date-functions, that are easy to handle. One way would be like this:

where Year (date) = 2012
and Month(date) = 1
and Day (date) = 1

But there are other solutions. Take a look at the following page for mor information: http://msdn.microsoft.com/en-us/library/ms186724.aspx

I worked recently with string-representations of datetime-values. I recommend not do it and to work always with the dates, because of compatibility, speaking of the MSSQL-Server.

If you use string-representations of datetime-values you need to be very careful with formats on different language-settings than the one on your own server. Strings can be interpreted different on other servers (ISO-format vs us-format).

Upvotes: 2

user800014
user800014

Reputation:

Oracle not store your date field formatted, but you can format the output with to_char function. For example:

select to_char(date_field,'dd/mm/yyyy hh24:mi:ss')

If you query a date without formatting, the output format will depend on the tool that you are using and your NLS_DATE parameter too.

To filter dates in Oracle you can use the to_date function, that receives an string and parse to date with some specific format. You can see all options of to_date here

Options to filter your date field:

where date_field between to_date('1.1.2012 00:00','d.m.yyyy hh24:mi') and to_date('1.1.2012 23:59','d.m.yyyy hh24:mi')

-- you possibly will lost some performance with this second one 
where trunc(date_field) =  to_date('1.1.2012','d.m.yyyy')

Upvotes: 2

Dominik G
Dominik G

Reputation: 1479

One possibility would be to do something like this:

WHERE date_and_time >=to_date( '01.01.2012','dd.mm.yyyy') and date_and_time <= to_date('01.01.2012','dd.mm.yyyy');

date_and_time is the name of your Date column.

edit: This is for Oracle

Upvotes: 1

Related Questions