Solved

your entry can't be converted to a valid date time value

Posted on 2009-07-05
4
2,054 Views
Last Modified: 2012-05-07
Dear all,


Previously i can change dates between in where clause... but this month when i am trying to

change i am getting an error "your entry can't be converted to a valid date time value"

This is my query....
SELECT     TOP 100 PERCENT dbo.Sales_09.*, dbo.IV00101.ITEMDESC, CASE WHEN serltnum IN ('epc101', 'epc102', 'epc103', 'ae0002')
                      THEN 'STRATEGIC_SPARES' WHEN serltnum IN ('epc001', 'epc002', 'epc003') THEN 'EPC_CONSUMABLES' WHEN serltnum IN ('ae0001', 'ae0003',
                      'ae0004', 'ae0005', 'ae0006') THEN 'AEOM_CONSUMABLES' WHEN serltnum IN ('IS0001', 'IS0002', 'IS0003', 'IS0004', 'IS0005', 'IS0006', 'IS0007',
                      'IS0008', 'IS0009', 'IS0010', 'IS0011', 'IS0012', 'IS0013', 'IS0014', 'IS0015') THEN 'INITIAL_SPARES' END AS itemtype,
                      dbo.Sales_09.SERLTQTY * dbo.Sales_09.UNITCOST AS total_itemsoldvalue, dbo.IV00101.UOMSCHDL
FROM         dbo.Sales_09 INNER JOIN
                      dbo.IV00101 ON dbo.Sales_09.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE     (dbo.IV00101.ITEMTYPE <> 5) AND (dbo.Sales_09.daterecd BETWEEN '06/01/2009' AND '06/31/2009')
ORDER BY dbo.Sales_09.ITEMNMBR

why i am getting error this month only, i didn't change anything in settings....this settings i need to change everymonth first week. (according to current month).
Please solve my problem.
0
Comment
Question by:nivasnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 1

Accepted Solution

by:
elkhawajah earned 125 total points
ID: 24779123
The Month June doesn't Have A DAY NUMBER 31.. Last day in this month is 30,
So you would write :

BETWEEN '06/01/2009' AND '06/30/2009'
0
 
LVL 5

Assisted Solution

by:rizwanidrees
rizwanidrees earned 125 total points
ID: 24779124
it is because you have 06/31/2009 is in-correct date, June have only 30 Days :)

Replace it with

DateAdd("m",1,'06/01/2009')
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 125 total points
ID: 24779126
The error is because June(06) month doesn't have the 31st day..

Replace
(dbo.Sales_09.daterecd BETWEEN '06/01/2009' AND '06/31/2009')

with

(dbo.Sales_09.daterecd BETWEEN '06/01/2009' AND '06/30/2009')

to make it a valid datetime value and this should solve you out..
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points
ID: 24779127
presuming your daterecd is datetime field:
 AND dbo.Sales_09.daterecd >= convert(datetime, '06/01/2009', 101) 
 AND dbo.Sales_09.daterecd < convert(datetime, '07/01/2009', 101) 

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 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