[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Syntax for Where clause for dates

Posted on 2015-01-23
3
Medium Priority
?
81 Views
Last Modified: 2015-01-23
I have a syntax error in the following and clause.

I am trying to say where OrderDateTime is >= '2014-01-01' and OrderDateTime < '2015-01-01'


(dbo.OeOrders.OrderDateTime >= "'"+YEAR(getdate())-1 + '-01-01' AND      dbo.OeOrders.OrderDateTime < "'"+YEAR(getdate()) +'-01-01') 

Open in new window



thanks

Glen
0
Comment
Question by:GPSPOW
3 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40567145
(dbo.OeOrders.OrderDateTime >= CAST(YEAR(getdate())-1 AS varchar(4)) + '0101' AND
 dbo.OeOrders.OrderDateTime < CAST(YEAR(getdate()) AS varchar(4)) + '0101'

Format 'YYYYMMDD' is always interpreted correctly, whereas YYYY-MM-DD requires specific date/language settings to work properly.
0
 

Author Closing Comment

by:GPSPOW
ID: 40567221
Thanks

Works great!
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 40567222
You could also write:
YEAR(dbo.OeOrders.OrderDateTime) >= YEAR(GETDATE()) - 1
AND YEAR(dbo.OeOrders.OrderDateTime) < YEAR(GETDATE())

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question