Solved

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

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

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 105
How to import SQL 2000 database to SQL 2014 5 115
Grid querry results 41 78
Table create permissions on SQL Server 2005 9 42
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 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