Solved

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

Posted on 2009-07-05
4
1,946 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
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how many extra RAM for SQL server is needed 22 43
Delete from table 6 47
create insert script based on records in a table 4 24
kill process lock Sql server 9 63
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…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

791 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