Geek Answers Handbook

DateTime query for year only in SQL Server

I want to select multiple entries on the basis of the relevant year, for example, on the tab of the table where the columns [id] int, [name] varchar,[bookyear] datetime

I want to select all entries where year 2009 is.

The following query yields 0 results:

    SELECT [ACCNO]
      ,[Roll No]
      ,[IssueDate]
      ,[DueDate]
  FROM [test1].[dbo].[IssueHis$] 
  where [IssueDate]  between 12-12-2004 and 1-01-2010
+5
source share
6 answers
select id,name,bookyear from tab1 where year(bookyear) = 2009
+19
source 
SELECT [ACCNO]
    ,[Roll No]
    ,[IssueDate]
    ,[DueDate]
FROM [test1].[dbo].[IssueHis$] 
WHERE [IssueDate] >= '20090101' AND
      [IssueDate] < '20100101'
+4
source
  • datetime,
  • yyyymmdd SQL Server ( yyyy-mm-dd, )
  • ,

, "12 2004 " "31 2009 " , :

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

, , - " = 2009"

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

One of the most important things to keep in mind regarding SQL Server and dates is that the best format to use is: 'YYYYMMDD'(or 'YYYYMMDD HH:MI:SS'). This format will always be correctly interpreted by SQL Server, regardless of regional date settings.

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

You can create a view with 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 can request a view like this

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

You can do

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

More articles:


All Articles