Click here to Skip to main content
15,938,446 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,

i want to retrieve data between two dates but i stored many data into my database. it`s datatype is varchar and stored the data between below format:

01/06/2012 4:33:04 PM ( dd/MM/yyyy time)

now when i convert into datetime in query but gives error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


select name,email from empl where fairmate_user='yes' and email not in
(SELECT distinct(empl.email)  FROM    empl INNER JOIN  User_log ON empl.name
= User_log.user_name   where  convert(datetime,sign_in,103)>='01/01/2013'
and convert(datetime,sign_in,103)<='01/01/2013') (mm/dd/yyyy) formate


so, how can i retrive the data between two dates which i want to be.

some data are stored in format like

01/01/2013 17:36:03
02/01/2013 10:10:33 AM
02/01/2013 10:10:57 AM



please help me.


Thanks and Regards
Mitesh
Posted
Updated 4-Feb-13 18:55pm
v3
Comments
bbirajdar 4-Feb-13 7:04am    
What is your SQL query.. I may help you to correct it..
Irbaz Haider Hashmi 4-Feb-13 7:05am    
Can you update the query here too?
RedDk 5-Feb-13 12:36pm    
MM,

I think you want an exact answer to your question and ... well the answer is all there in my solution (No_2). You can even copy&paste the code! Try it you'll like it.
[no name] 6-Feb-13 0:33am    
yes dear thank you.i got the solution from your side and it is perfact. there is small mistake in one record of mine db. i solved it. and run perfact your query.
Thanks again......
RedDk 8-Feb-13 12:57pm    
Glad it helped!

Try this

SQL
create table test
(datecol varchar(30))

INSERT INTO test VALUES ('01/06/2012 4:33:04 PM')

SELECT * from test where Convert(datetime,datecol,103) BETWEEN '2012-01-01' and '2012-05-31'
SELECT * from test where Convert(datetime,datecol,103) BETWEEN '2012-01-01' and '2012-06-02'


For your query:

SQL
select name,email from empl where fairmate_user='yes' and email not in
(SELECT distinct(empl.email)  FROM    empl INNER JOIN  User_log ON empl.name
= User_log.user_name   where  Convert(datetime,sign_in,103) BETWEEN '2013-01-01' and '2013-01-01'
 
Share this answer
 
v3
Comments
[no name] 4-Feb-13 7:11am    
sorry but gives the same error.
Santhosh Kumar Jayaraman 4-Feb-13 7:12am    
Have you tried my table, insert and query?
Santhosh Kumar Jayaraman 4-Feb-13 7:13am    
just change the date format in your query.

Instead of 01/01/2013 try '2013-01-01'
Santhosh Kumar Jayaraman 4-Feb-13 7:14am    
updated the solution with answer for your query.try that
[no name] 4-Feb-13 7:16am    
yes. use it but gives me error from my db.
By replacement then ...
SELECT CONVERT(DATETIME,GETDATE(),103)		-- "(now)"

Ok, let's create a table to match this wishlist of a SELECT ... with datafile(td).txt
Zany, Assignment	quisling.tito.com	yes	2013-02-04 11:38:01.760
Captain, Theodorus	mike.twentysixteen.com	yes	2012-12-14 11:59:02.110
Wynn, Cruikshank	ss.nautpott.com	no	2011-07-26 07:52:15.007

CREATE SCHEMA cpqa

USE [cpqaMM]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cpqa].[tblSomeTime]') AND type in (N'U'))
DROP TABLE [cpqa].[tblSomeTime]
GO

CREATE TABLE [cpqaMM].[cpqa].[tblSomeTime](
	[name][nvarchar](73),
		[email][nvarchar](42),
			[fmUser][nvarchar](5),
				[sIn][datetime]
				)

BULK INSERT [cpqaMM].[cpqa].[tblSomeTime] FROM 'C:\cpqaMM\datafile(td).txt'				

SELECT [name]
		,[email]
			,[fmUser]
				,[sIn]
  FROM [cpqaMM].[cpqa].[tblSomeTime]
GO

/*
	name				email					fmUser	sIn
	~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	Zany, Assignment	quisling.tito.com		yes		2013-02-04 11:38:01.760
	Captain, Theodorus	mike.twentysixteen.com	yes		2012-12-14 11:59:02.110
	Wynn, Cruikshank	ss.nautpott.com			no		2011-07-26 07:52:15.007
*/

Barest of assignment ... should pull the middle record ...
SELECT * FROM [cpqaMM].[cpqa].[tblSomeTime] WHERE CONVERT(datetime,[sIn],103)>='01/01/2012' AND CONVERT(datetime,[sIn],103)<='12/31/2012'

-- Captain, Theodorus	mike.twentysixteen.com	yes		2012-12-14 11:59:02.110				

So, the ideal situation is above, that's what you want. And this is what you have instead:
USE [cpqaMM]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cpqa].[tblBadTime]') AND type in (N'U'))
DROP TABLE [cpqa].[tblBadTime]
GO

CREATE TABLE [cpqaMM].[cpqa].[tblBadTime](
	[name][nvarchar](73),
		[email][nvarchar](42),
			[fmUser][nvarchar](5),
				[sIn][nvarchar](101)
				)
BULK INSERT [cpqaMM].[cpqa].[tblBadTime] FROM 'C:\cpqaMM\datafile(td).txt'

SELECT [name]
		,[email]
			,[fmUser]
				,[sIn]
  FROM [cpqaMM].[cpqa].[tblBadTime]
GO

SELECT * FROM [cpqaMM].[cpqa].[tblBadTime] WHERE CAST(CONVERT(nvarchar,[sIn],103) AS [datetime])>='01/01/2012' AND CAST(CONVERT(nvarchar,[sIn],103) AS [datetime])<='12/31/2012'	

Oh, wait a minute ... that's not what I have! I have CAST(CONVERT(datetime,[sIn],103) AS [datetime]) ... which is yielding the tragic:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900