CODEWITHSUNDEEP
sqlsql-server
noob
noob

Reputation: 3811

SQL Query to extract records specific to a date without specifying timestamp

Query

List the employees who joined on 1 May 1992, 31 Dec 1992, 7 Apr 1992 in asc order of seniority.

Employees Table

EmployeeID  LastName    Title                        BirthDate               HireDate
1            Davolio    Sales Representative        1948-12-08 00:00:00.000 1992-05-01 00:00:00.000
2            Fuller     Vice President, Sales       1952-02-19 00:00:00.000 1992-08-14 00:00:00.000
3            Leverling  Sales Representative        1963-08-30 00:00:00.000 1992-04-01 00:00:00.000

Query Code

Select * 
from employees 
where HireDate like '1992-05-01' or 
      HireDate like '1992-12-31' or 
      HireDate like '1992-04-07' 
order by HireDate asc

I am getting null result. Basically this is because I guess the exact timestamp is not specified. How to get result without using specifying timestamp.

Upvotes: 0

Views: 81

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Do not use like with date/times. Period. SQL Server will convert strings in the format of YYYY-MM-DD and YYYYMMDD to dates, so it can be confusing because values "look" like strings. But they compiler treats them as dates.

If none of your dates have time components, then simple comparisons work:

Select * 
from employees 
where HireDate = '1992-05-01' or 
      HireDate = '1992-12-31' or 
      HireDate = '1992-04-07' 
order by HireDate asc;

As Yogesh points out, you can use IN for this.

If you have non-zero time components -- which the data suggests is possible, then you can convert the values to a date:

where convert(date, HireDate) = '1992-05-01' or 
      convert(date, HireDate) = '1992-12-31' or 
      convert(date, HireDate) = '1992-04-07'

Happily, SQL Server will still use an index with this type conversion.

And, you can use in with this:

where convert(date, HireDate) in ('1992-05-01', '1992-12-31', '1992-04-07')

If you really want to treat these as dates, then I suggest that you remove the time component permanently:

alter table employees alter column HireDate date;

Upvotes: 1

Simon
Simon

Reputation: 537

You simply need to change the dateformat of the data that you are querying to give you only a day.

We can do this by using CONVERT()

like this

CONVERT(VARCHAR(10),hiredate,120)

Also, you will need to replace LIKE for an exact matching system. As you are working with multiple search parameters, I would recommend using IN and a comma-separate list to make your list of hire-dates easier to manage, like this.

WHERE HireDate IN ('1992-05-01','1992-12-31','1992-04-07')

Therefore your end script should be;

SELECT 
* 
FROM employees 
WHERE CONVERT(VARCHAR(10),hiredate,120) IN ('1992-05-01','1992-12-31','1992-04-07')
ORDER BY HireDate ASC

Please note, if you want to list "in asc order of seniority", you will need to create a separate table which lists job titles and some sort of "level" system or just an exact numbering system that detailed which job-titles were what level of seniority. Alternatively, if you are working with a very small number of job titles, like in your sample data, you can use a CASE statement to order your results. e.g.

CASE Title WHEN 'President' THEN 1 'Vice President' THEN 2 ELSE 99 END

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use single quote & IN clause : 

Select * 
from employees 
where HireDate IN ('1992-05-01', '1992-12-31','1992-04-07 ')
order by HireDate asc;

Upvotes: 1

Related Questions