CODEWITHSUNDEEP
sqlsql-serversql-server-2008datetime
user1074474
user1074474

Reputation: 461

DateTime query on only year in SQL Server

I want to select multiple records on the basis of a matching year. For example, in table tab, where columns are [id] int, [name] varchar, and [bookyear] datetime, I want to select all records with a year of 2009.

The following query gives 0 results:

SELECT 
    [ACCNO], 
    [Roll No],
    [IssueDate],
    [DueDate]
FROM 
    [test1].[dbo].[IssueHis$] 
WHERE 
    [IssueDate]  between 12-12-2004 and 1-01-2010

Upvotes: 11

Views: 71127

Answers (8)

Laura Marianella
Laura Marianella

Reputation: 1

Yes, if you try this query:

SELECT to_date ('2008','yyyy') AS my_date, SYSDATE FROM dual;

The date will be the year (2008) and the current month (today is November), and the day is 1 so your query do not includes all months in 2008.

Upvotes: 0

Saeid Ostad
Saeid Ostad

Reputation: 727

where YEAR(IssueDate) = '2009'

Reference

Upvotes: 0

iYazee6
iYazee6

Reputation: 866

You can do 

....
WHERE [IssueDate] > '2004' AND [IssueDate] < 2010

Upvotes: 0

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

You could create a view that has the IssueYear as a separate column

CREATE VIEW vIssueHis
AS
SELECT
[ACCNO],
[Roll No],
[IssueDate],
[DueDate],
DATEPART(yyyy,IssueDate) as IssueYear,
DATEPART(yyyy,DueDate) as DueYear
FROM [test1].[dbo].[IssueHis$]

Then you could query the view like this

SELECT [ACCNO]
      ,[Roll No]
      ,[IssueDate]
      ,[DueDate]
FROM vIssueHis
WHERE IssueYear = 2009

Upvotes: 2

user806549
user806549

Reputation:

One of the most important things to remember with regards to SQL Server and dates is that the best format to use is this: 'YYYYMMDD' (or 'YYYYMMDD HH:MI:SS'). This format will always be interpreted properly by SQL Server, regardless of regional date settings.

SELECT [ACCNO] 
      ,[Roll No] 
      ,[IssueDate] 
      ,[DueDate] 
  FROM [test1].[dbo].[IssueHis$]  
 WHERE [IssueDate] >= '20090101' 
   AND [IssueDate] < '20100101'

Upvotes: 2

gbn
gbn

Reputation: 432662

  1. You need to delimit datetime constants like you would a string
  2. Use yyyymmdd pattern for SQL Server (not yyyy-mm-dd, it is unsafe generally)
  3. Use proper range queries to allow for time value

So, to find values between "12 Dec 2004" and "31 Dec 2009" inclusive, allowing for time:

...
where [IssueDate] >= '20041212' AND [IssueDate] < '20100101'

Edit, or this, because of the ambiguity in the question for "year = 2009"

...
where [IssueDate] >= '20090101' AND [IssueDate] < '20100101'

Upvotes: 4

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

SELECT [ACCNO]
    ,[Roll No]
    ,[IssueDate]
    ,[DueDate]
FROM [test1].[dbo].[IssueHis$] 
WHERE [IssueDate] >= '20090101' AND
      [IssueDate] < '20100101'

Upvotes: 4

dotnetstep
dotnetstep

Reputation: 17485

select id,name,bookyear from tab1 where year(bookyear) = 2009

Upvotes: 34

Related Questions