February 13, 2012 at 7:05 am
Hello,
I'm trying to get my query to compare dates, but it gives me an error stating that Text or Varchar can't be used with an equal to or less than operand.
I want to try to convert the dates so I can run a date range.
Here is an example of my query:
Use [EAF]
Select Table_1.HeatNumberID AS Heat_Number,
Table_1.Start_Date,
Table_1.Start_Time,
Table_1.Start_Grade,
Table_1.End_Grade,
Table_1.End_Date,
Table_1.End_Time,
Table_3.Tap_Weight,
Table_3.MWH,
Table_3.KWH_Per_Ton,
From Table_1
INNER JOIN Table_3
ON Table_1.HeatNumberID=Table_3.HeatNumberID
I want to use the Start_Date column.
Ultimately this will be used is an SSRS report, just need to get the Start_date column to come back with data I can use in a date range.
Any suggestions are greatly appreciated,
Thanks
February 13, 2012 at 7:23 am
Can you please post table DDLs for table1 & table3? I just want to crosscheck the data types of columns that are used in join conditions.
I assume you are getting errors in this query and you are not trying to compare any value that is passed from SSRS report.
February 13, 2012 at 7:41 am
Hi, thanks for the quick response.
Here's what I have to work with in these tables,
Table_1
HeatNumberID (PK,varchar(50),not null)
Start_Time (text,null)
Start_Date (text,null)
Start_Grade (text,null)
End_Grade (text,null)
End_Time (text,null)
End_Date (text,null)
Table_3
HeatNumberID (varchar(50),null)
Tap_Weight (varchar(50),null)
MWH(varchar(50),null)
KWH_Per_Ton(varchar(50),null)
Thanks,
Joe
February 13, 2012 at 7:46 am
joe.cahoon (2/13/2012)
Hi, thanks for the quick response.Here's what I have to work with in these tables,
Table_1
HeatNumberID (PK,varchar(50),not null)
Start_Time (text,null)
Start_Date (text,null)
Start_Grade (text,null)
End_Grade (text,null)
End_Time (text,null)
End_Date (text,null)
Table_3
HeatNumberID (varchar(50),null)
Tap_Weight (varchar(50),null)
MWH(varchar(50),null)
KWH_Per_Ton(varchar(50),null)
Thanks,
Joe
You really need to work on using proper datatypes. Use datatime to hold datetime data. You REALLY need to not use text for anything. The text datatype is deprecated and should be replaced with varchar(max), of course in your situation it is totally inappropriate. Not trying to point fingers but the title of your thread is clear indication of the challenges of using incorrect datatypes.
What does the data look like in these rows? Is the date and time separated? Basically you are going to have use convert to get this to datetime in your where clause. The big issue is you won't be able to use any indexes and will be forced to do a full table scan.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 13, 2012 at 7:56 am
Hi,
I agree, I wished who ever initially set these up did so in a different way, but I have to work with it regardless.
The date and time are in 2 separate columns and is displayed as such
for date: example 12/3/2012 Time: example 10:33:31 PM
February 13, 2012 at 7:56 am
Agree with Sean...
However it’s not advised but here is an example to join text & varchar columns. It doesn't throw the error you mentioned.
create table text_table
(
id int,
val text
)
insert into text_table values (1,cast(getdate() as varchar(100)))
select * from text_table
go
create table char_table
(
id int,
val varchar(100)
)
insert into char_table values (1,getdate())
select * from char_table
go
select * from text_table t
inner join char_table c
on c.val = cast(t.val as varchar(100))
--============= Cleanup ===============
drop table text_table
drop table char_table
February 13, 2012 at 8:01 am
joe.cahoon (2/13/2012)
Hi,I agree, I wished who ever initially set these up did so in a different way, but I have to work with it regardless.
The date and time are in 2 separate columns and is displayed as such
for date: example 12/3/2012 Time: example 10:33:31 PM
I know what you mean about being stuck with it. If it is at all possible I would fix as much of that as possible as quickly as possible. At the very least get the datatypes under control a little bit. You have 10 characters of data being stored in text fields.
See if this will help get you pointed towards a solution.
declare @Date varchar(10) = '12/3/2012'
declare @Time varchar(20) = '10:33:31 PM'
select CAST(@Date + ' ' + @Time as datetime)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 13, 2012 at 8:12 am
Thanks for the replies, I really appreciate it.
One question, and I'm still new to using SQL,
declare @Date varchar(10) = '12/3/2012'
declare @Time varchar(20) = '10:33:31 PM'
select CAST(@Date + ' ' + @Time as datetime)
In the select line, not really sure what goes where the ' ' is , also when I declare the date, I'm declaring a format, not a particular date, just to clarify.
Thanks again,
Joe
February 13, 2012 at 8:20 am
joe.cahoon (2/13/2012)
Thanks for the replies, I really appreciate it.One question, and I'm still new to using SQL,
declare @Date varchar(10) = '12/3/2012'
declare @Time varchar(20) = '10:33:31 PM'
select CAST(@Date + ' ' + @Time as datetime)
In the select line, not really sure what goes where the ' ' is
That is a space. The string to cast to your date is 12/3/2012 10:31:31 PM. Without the space it wouldn't work
when I declare the date, I'm declaring a format, not a particular date, just to clarify.
Was there a question here? This certainly didn't clarify anything, it actually made me scratch my head and wonder what you mean.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 13, 2012 at 8:34 am
I guess my question is instead of declaring a specific date, would I declare the format, such as 'mm/dd/yyyy' ?
Joe
February 13, 2012 at 8:44 am
joe.cahoon (2/13/2012)
I guess my question is instead of declaring a specific date, would I declare the format, such as 'mm/dd/yyyy' ?Joe
Formatting should be left in the front end.
See if this will answer your question.
create table #DateTimeExample
(
MyDate varchar(10),
MyTime varchar(20)
)
insert #DateTimeExample
select '12/3/2012', '10:33:31 PM'
select *, CAST(MyDate + ' ' + MyTime as datetime) as MyDateTime, CONVERT(varchar(25), CAST(MyDate + ' ' + MyTime as datetime), 110) as MyDisplayDateTime
from #DateTimeExample
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 13, 2012 at 8:51 am
So, I would have to create a temporary table, and then run my query?
Sorry to be a pain, but SQL is still somewhat new to me.
February 13, 2012 at 8:53 am
No just run a similar query on your table. I am showing you an example. In my case I used a temp table because I don't have your permanent table. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 13, 2012 at 8:55 am
Ah,I see. Well, thanks again for all the help, I'll give it go and see what happens:-)
Joe
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply