Searching Character Data Using Like

Jul 20, 2005

Hello All,

SQL 2000, case insensitive database

I have a situation where I need to find abbreviations in the rows in a
table. The rule i came up is, get all the rows from the table where
there is more than one character is capitalized consequtively eg.
"USA", "TIMS", "AIR"

Here is the sample data:

create table test (mystring varchar(100))
go
insert into test (mystring) values ('I live in USA')
insert into test (mystring) values ('this is a test row. usa
(abbreviated wrongly).')
go

--expected result set
mystring
----------------------
I live in USA

Here is the query which I tried.
select * from test where mystring collate SQL_Latin1_General_CP1_CS_AS
like '%[A-Z][A-Z]%'

But the above query returns both the records. Any help?

Thanks

View 2 Replies


ADVERTISEMENT

Searching Encrypted Data; Using MAC Secret Data

Aug 10, 2006

I just finished reading an article on how to search encrypted data efficiently and they suggested creating a new column with a Message Auhtentication Code. To be honest, reading the aritcle makes my head hurt. I can hardly understand what they were doing myself and I can't begin to explain it to a developer.

Are there any easier ways to search encrypted columns for a speciifc match? If not, does any have some stored procs that implement this messy MAC stuff?



TIA,



Barkingdog

View 5 Replies View Related

Searching HTML Data

Mar 29, 2004

Hi,

I have question, sorry if it is very basic, as SQL is not my thing!

Iam allowing visitors on my IBS site to (lets say) create HTML post. This is enabled by allowing the user to use WYISWG text editor component. This means that users can create all sorts of HTML tags.

Before storing this HTML in the SQL Server, I encode it.

I also need to provide users with searching ability. So what is the best way in achieving this? Can I write search SQL normally, as in, with LIKE operators or do I need to something special?

Thanks

View 6 Replies View Related

Searching For Any Data String In Database

Jul 23, 2005

hihere is a problem:i have a databes with many, many tablesthe problem is that i dont know where 'abcd' string is (but it is for surein one of that table)is there any SELECT that could help me with finding this string in database?--greets

View 1 Replies View Related

Searching/Estracting Numerical Data

Feb 6, 2006

KRFeb 6, 1:48 pm show optionsNewsgroups: microsoft.public.access.formsFrom: "KR" <kra...@bastyr.edu> - Find messages by this authorDate: 6 Feb 2006 13:48:00 -0800Subject: Extract Number from Fields - SQLReply | Reply to Author | Forward | Print | Individual Message | Showoriginal | Remove | Report AbuseI am new to the SQL world, and I am trying to come up with a scriptthat will extract only the numerical data from a column of varchardata type . There is not a pattern to the data entered, except thatthe data thatI am looking to extract is a three digit number. If someone couldpoint me in the right direction that would be great.Thanks in advanceKR

View 5 Replies View Related

Searching Historical Data For Patterns

Feb 17, 2008



I have a database which contains time series data (historical stock prices) which I have to search for patterns on a day to day basis. But searching this historical data for patterns is very time consuming not only in writing the complex t-sql scripts but also executing them.

Table structure for one min data:
[Date] [Time] [Open] [High], [Low], [Close], [Adjusted_Close], [MA], [DI].....
Tick Data:
[Date] [Time] [Trade]
Most time consuming queries are with lots of inner joins. So for example if I have to compare first few mins data then I have to do inner join like:
With IntervalData AS
(
SELECT [Date], Sum(CASE WHEN 1430 = [Time] THEN [PriceRange] END) AS '1430',
Sum(CASE WHEN 1431 = [Time] THEN [PriceRange] END) AS '1431',
Sum(CASE WHEN 1432 = [Time] THEN [PriceRange] END) AS '1432'
FROM [INDU_1] GROUP BY [Date]
)
SELECT [Date] ,[1430], [1431], [1432], [1431] - [1430] As 'Range' from IntervalData
WHERE ([1430] > 0 AND [1431] < 0 AND [1432] < 0) OR ([1430] < 0 AND [1431] > 0 AND [1430] > 0)
------------------------------------------------------------------------
select ind1.[Time], ind1.PriceRange,ind2.[Time], ind2.PriceRange from INDU_1 ind1
INNER JOIN INDU_1 ind2 ON ind1.[Time] = ind2.[Time] - 1 AND ind1.[Date] = ind2.[Date]
where (ind1.[Time] = 2058) AND ((ind1.PriceRange > 0 AND ind2.PriceRange >0) OR (ind2.PriceRange < 0 AND ind1.PriceRange < 0))
ORDER BY ind1.[Date] DESC;
Is there anyway I can use Sql 2005 Data mining models to make this searching faster?

View 1 Replies View Related

Strategy For Data Storage/searching

Dec 16, 2007

Hello there,

Don't know if this is the right forum to be asking this, but I'll give it a try...

I'm relativelly a beginner in SQL Server and T-SQL in general. The problem I'm trying to solve is the following:

The big picture is that I have data coming from different data sources which I need to store on a database for later reference. Each data source might have a different set of measurements. For example, data source 1 might log Pressure and Humidity while data source 2 logs Pressure and Temperature. Once the data is present on the DB, the users can go ahead and retrieve data for a given [datasource/measurement/time interval] to generate reports or charts.

My implementation so far consists of two tables: series_info and series_data. series_info holds general information for a given series of measurements for a given data source (Pressure for data source 1, Pressure for data source 2, Humidity for data source 1 and Temperature for data source 2, in our example). Each series has a bigint index as primary key.

The table series_data contains all data relative to the series from series_info. Each piece of data has a bigint as a primary key, an associate time (which is always crescent) and a foreign key to the series it represents (in series_info).

Alright, everything is cool so far. However, whenever a user wants to retrieve data for given [data source/measurement/time interval], this takes very long, since all data is interposed in series_data and for every search it's necessary to find where the desired data actually lies.

One obvious solution for this would be to dynamically create a new table to hold the data for each series, but that would just make my database disorganized, since there would be thousands and thousands of tables.

Another thing that comes to my mind is to create a table with information of where lies the data for a given [data source / measurement] for given dates. So when the user requested data for a given [data source/measurement] between, say, january and february, we would first look at this intermediate table and find out that the data lies between indexes 1000 and 2000 on the series_data table, so the next SELECT command to series_data would already contain a restriction like WHERE index>=1000 and index<=2000. This should probably improve the speed of retrieval.

What do you guys (or girls) think? Maybe there's simply a classical solution for such a case.


Thanks in advance!

View 6 Replies View Related

Data Access :: Validation For Length Of The Character Data Types

Jun 10, 2015

I Have a table with #Sample like below

=================================
#Sample
id int,
SSN varchar(20),
State varchar(2)
 
Sample Data:

ID SSN STATE
1 999-000-000 AB
2 979-000-000 BC
3 995-000-000 CD
=================================

We used filter logic based on the SSN & State.

We are passing these values through variables like

Declare @State varchar(2)
Declare @SSN varchar(20)

While run time these values are lets suppose @SSN = '999-000-000' & @State='ABC'

Now the Result is displayed with the state data Like 'AB' only.

Output: 1 999-000-000 AB

instead it should give system generated error.

Here I have 2 Questions:
1. Why it is taking 1st 2 Charecters?
2. Why it does not have any system generated for length?

I can do validation with Length function for these 2 variables however if have 100 variables then it should not feasible case. So, what is the reason behind? 

View 5 Replies View Related

Searching For Encrypted Fields In Data Columns

Jul 20, 2005

I am new to database programming and was curious how others solve theproblem of storing encrypted in data in db table columns and thensubsequently searching for these records.The particular problem that I am facing is in dealing with (privacy)critical information like credit-card #s and SSNs or business criticalinformation like sales opportunity size or revenue in the database. Therequirement is that this data be stored encrypted (and not in theclear). Just limiting access to tables with this data isn't sufficient.Does any database provide native facilities to store specific columns asencrypted data ? The other option I have is to use something like RC4 toencrypt the data before storing them in the database.However, the subsequent problem is how do I search/sort on these columns? Its not a big deal if I have a few hundred records; I couldpotentially retrieve all the records, decrypt the specific fields andthen do in process searches/sorts. But what happens when I have (say) amillion records - I really don't want to suck in all that data and workon it but instead use the native db search/sort capabilities.Any suggestions and past experiences would be greatly appreciated.much thanks,~s

View 10 Replies View Related

T-SQL (SS2K8) :: Find String Before Character When Character Appears Multiple Times

May 17, 2015

I have a table that contains file paths as

ServernamefolderAfilenameA
ServernameFolderBFilenameB

and I need a query to return

ServernamefolderA
ServernameFolderB

I tried

SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table

But that removes everything after the first and I need it to return all data before the last

View 5 Replies View Related

T-SQL (SS2K8) :: Replace Multiple Occurrences Of Same Character With Single Character

Aug 6, 2015

I have the following scenario, The contents of main file are like :

ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it

And I need the output in the following form:

ServerCentral=forum=is=the=best
so=be=on=it

The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.

View 5 Replies View Related

Character Thirteen Data

Nov 6, 2001

Hi,

Does anybody have an idea about this? I have a store procedure that extracts data from two different tables. There is a text field in one of the table. We use an application called Wisdom as a front end where user can enter data and it goes to the database. When a user enter data in that text field if they press the enter key in the computer then that note shows in two lines in the query analyzer when it's supposed to be in only one lines. Can anybody help me with how to strip the character thirteen data from entering into the databse table?

Thanks.

View 1 Replies View Related

Need New Line Character In Data

Jan 11, 2008

I have a need to have new line character in some data. I am trying to insert and retrieve the data with the new lines char in it, but it does work. I need the new line char in the data as I need to do some further processing whenever the new line starts.
Any ideas on how I can achieve this?
Thanks for the help.

This are the ways I have already tried, but does not work

create table mytest (col1 varchar(max))

insert into mytest values (' line1 ' + CHAR(13) + ' line2')

update mytest set col1 = (' line1 ' + CHAR(13) + CHAR(10) + ' line2')

update mytest set col1 = 'line1
line2
aa
line3 '

update user03.amitatest set col1 = 'line1 ' + CHAR(13) + CHAR(10) +
' line2 ' + CHAR(13) + CHAR(10) + 'aa ' + CHAR(13) + CHAR(10) + 'line3'

select col1 from mytest

The result set from the above select is -
line1 line2 aa line3

View 2 Replies View Related

Import Xml Data With &&amp;amp; Character

Jun 5, 2006

Hello,

I'm trying to import data from xml file into sql 2005 (through data flow task), which contains the string '&amp;' inside some document nodes, such as

Ej: <DESC>Black &amp; White</DESC> ---> Black & White

This character doesn't import correctly (as &), but I obtain freaky characters. I've tried with different collations of sql server but there's no way...

Someone can help me with this?

Thanks a lot,

Gema

View 6 Replies View Related

Data Set Towards A Chain Of Character

Aug 6, 2007



Hello :



I have a request which me dismissal 10 lines (normal select), I want to have quite lines in a single chain of character, separated one in the others by a comma:

A
B
C
D ------> A,B,C,D

What is that you have an idea for ca.

Thank you.

View 3 Replies View Related

Select Part Of Character String Based On A Character

Apr 15, 2004

I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).

How do I select everything to the right of the dash when the number of characters to the left of the dash varies?

View 3 Replies View Related

Converting Character Data To DATETIME

Aug 2, 1999

For our DataWarehouse, we get several date fields from our mainframe system in a character format. When this data was loaded into SQL Server 6.5 using the CONVERT(DATETIME...) function, any dates containing 'bad' data would simply be replaced by a NULL automatically by the DBMS. We are now going to SQL 7.0, and I have found that when it hits a bad date it terminates the stored procedure, resulting with no update.

Is there a straightforward way around this? Possibly a script that will scrub the data, replacing bad data with NULLs? I'm trying to avoid writing something that will take the number of days per month and determine if it is valid.

Thanks

Michael

View 1 Replies View Related

Convert Character Data To Datetime

Aug 16, 2006

Hi! I need to insert character data into a SQL 2005 table in the datetime format. The strings I need to convert are in the following format: ddmmyyyy

I tried using case and convert:

cast('08162006' as datetime))
convert(datetime,'08162006',101))

Both attempts fail with the following error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Any advice is greatly appreciated. Thanks.

View 10 Replies View Related

Character Set Problem Data Loss

Oct 4, 2007



Hi,

I am transferring data from SQL Server 2005 to SQLAnywhere 9. Dates and numbers are sucessfully transferred but chars and varchars are not coppied. Though there are values in SQL Server 2005 table they are shown as blank on SQL anywhere. I cannot undestand data loss

Is this a code page problem or data size? for example a varchar 20 data type is in both sql server and sql anywhere Sybase. SQL server is in 1252 and sql anywhere 1253


Thanks.

View 6 Replies View Related

Return Left-most Character From 8 Character String

Oct 1, 2014

I'd like to return the left-most character from an 8 character string & the third from the left character too.

Like this ABC00123 returns AC

$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());

echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){

[Code] ....

View 5 Replies View Related

Unicode Character Data Types && Collation

Dec 18, 2006

Hi there,Trying to work with SQL2005 with Hebrew characters. If i use the Unicode character data types do i need to change the database collation???. regards   

View 5 Replies View Related

How To Store Large Character Data In MS SQL Server ???

Sep 11, 1998

I am developing a simple ASP based form, that stores user info in MS SQL server. I have created a table in the SQL server to store the data and defined the body field with this line: `body char(255)`. The problem is that if the user inputs a string longer then 255 characters it gets choped off. How would you suggest solving this problem? Should I use `text` datatype instead?? Any comments are very appreciated !!!

View 2 Replies View Related

Script To Strip Data From First Non-numeric Character

Jul 5, 2002

I need a script to find the position of the first non-numeric in a telno field and delete from that point onwards.

Example: 01208 12345 (Work) would become 01208 12345

Has anybody come across this before ?

TIA

Neil.

View 1 Replies View Related

How Do I Post The ' Character Into A LongText Data Field?

Nov 22, 2005

I need to update the string in a longtext field. NewString = "This is a 'test'"

I can't figure out how to use an escape character to identify the string with " ' " and then also include the " ' " character inside the string.

Can anybody help me?

Thanks,

Alex

View 5 Replies View Related

Data Access :: Special Character In Password

Jul 9, 2015

There is a ">" character (right-angle bracket) inside my SQL Server password. When I supplied this password to the bcp utility, the ">" character was treated as an output redirection symbol. So the password was truncated at ">" and the bcp output got sent to a file with a name consisting of the rest of the password after ">". I'm using SQL Server 2012. I cannot use Windows authentication due to company policy. Is there a way to resolve this without changing the password?

View 7 Replies View Related

How To Export 'text' Data Having &> 8192 Character Into File

Sep 17, 2002

Hi,

I have a table with text data with more than 8192 character in it. When I tried to select or into a file I could only see the first 8192 after setting 'Max character = 8192' properties. Please tell me how to select the entire data.

Thanks
John Jayaseelan

View 2 Replies View Related

Only One Character Added To Database Instread Of Full Data String??

Aug 18, 2007

When I execute this, it works ok but only one the first character of the request.form["d"] is stored to the db.I checked the sproc with another routine and it adds full data, and I've verified that value of request.form["d"] is longer than one chaacter by printing it to the page. Anyone got any ideas why only the first char is getting added to the db??  SqlConnection SqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString);
SqlCommand SqlCommand1 = new SqlCommand("addRoute", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter SqlParameter1 = SqlCommand1.Parameters.Add("@ReturnValue", SqlDbType.Int);
SqlParameter1.Direction = ParameterDirection.ReturnValue;
SqlCommand1.Parameters.Add("@xmlData", Request.Form["d"]);
SqlConnection1.Open();
SqlCommand1.ExecuteNonQuery();
Response.Write(SqlCommand1.Parameters["@ReturnValue"].Value);
//Response.Write(Request.Form["d"]);  

View 2 Replies View Related

SQL Server 2012 :: CSV Import With Non-character Data (Date And Decimal)

Mar 29, 2015

I'm using OpenRowSet to import about 30 columns from a csv file with 190 columns with a format file. Ultimately, I want to put this in an SSIS Package. I am receiving the following error when trying to import date and decimal info.

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 990, column 64 (TOTALSALES). There are several similar errors. I looked at this line and it is 17873.34 so I am not seeing the problem. Every value in the column is either 0 or a 2 digit decimal value. If I change the SQL Column and format file to to NVARCHAR, it imports fine.

The existing format file and SQL Column looks as follows. There are multiple errors referring to different columns and all of them seem to be valid decimals. I am having the same issue with date fields that exist in the csv as 20130521. If I bring it in as text, it is fine.

<FIELD COLLATION="SQL_Latin1_General_CP1_CI_AS" MAX_LENGTH="12" TERMINATOR="," xsi:type="CharTerm" ID="64"/>
<COLUMN xsi:type="SQLDECIMAL" SCALE="2" PRECISION="15" NAME="TOTALSALES" SOURCE="64"/>

The SQL Column is defined as Decimal ((15,2), NULL))

I created a small csv file with representative decimal, date, integer and NVarchar fields and it imports into SQL fine as decimal and date info. The SQL Query used is pretty simple. Ultimately, I am planning to create a package that imports this data and joins to a production table based on values in the csv file. It will either update existing values in a Production Table or insert New Values

INSERT INTO Import.dbo.test1
SELECT *
FROM OPENROWSET(BULK 'C:ShareImport.csv',
FirstRow=2,
FORMATFILE='C:ShareImport.xml'
) AS t1;

I am assuming there is bad data in the csv file but I'm not sure how to identify it as my test file seems to bring in date values with a format of 20140923 and 2 digit decimal values and that is what exists in the line numbers being referenced. I've not used OpenRow Set for this purpose before. The only workaround I've found is to bring it all in as text and create additional fields so I can cast or convert the date values which I'd rather not do as this process seems to work in my small sample file.

View 1 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Oct 12, 2006

I am newbie in asp and sql, and I am using VS & SQL expresswhen I try to submit I get following error"Conversion failed when converting character string to smalldatetime data type"Following is my insert statement<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@DropDownList1','@DropDownList2','@DropDownList3','@TextBox6','@DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3','@TextBox1','@TextBox2','@TextBox3','@TextBox4','@TextBox5','@TextBox7')"></asp:SqlDataSource>These are @DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3' defined as datetime in database.I would appriciate if somebody could help.

View 7 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type.

Mar 25, 2007

Hello, I have problem with this code.(This program presents - there is GridView tied to a SQL database that will sort the data selected by a dropdownList at time categories. There are  2  time categories in DropDownList - this day, this week.
 Problem: when I choose one categorie in dropDownlist for examle this week and submit data on the server I got this error.
Conversion failed when converting character string to smalldatetime data type.
Here is code:
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
 
 
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
 
string datePatt = @"yyyymmdd";
// Get start and end of day
DateTime StartDate = DateTime.Today;
 
string @StartDate1 = StartDate.ToString(datePatt);
 
string @EndDate = StartDate.AddDays(1).ToString(datePatt);
// Get start and end of week
string @startOfWeek = StartDate.AddDays(0 - (int)StartDate.DayOfWeek).ToString(datePatt);
string @startOfNextWeek = StartDate.AddDays(7 - (int)StartDate.DayOfWeek).ToString(datePatt);
 
 
switch (DropDownList1.SelectedValue)
{
 
case "1":
// day
SqlDataSource1.SelectCommand = "SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" + "WHERE" + "[DATE] >=" + "'@StartDate1'" + " AND [DATE] < " + "'@EndDate'";
break;
case "2":
//week
SqlDataSource1.SelectCommand = "SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" + "WHERE" + "[DATE] >=" + "'@startOfWeek'" + "AND [DATE] <" + "'@startOfNextWeek'";
break;
 
}
 
}
 
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
<style type="text/css">
body {
font: 1em Verdana;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
&nbsp;&nbsp;
 
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"
Style="z-index: 100; left: 414px; position: absolute; top: 22px">
<asp:ListItem Selected="True" Value="1">jeden den</asp:ListItem>
<asp:ListItem Value="2">jeden tyden</asp:ListItem>
</asp:DropDownList>
 
<asp:GridView ID="GridView1" runat="server" Style="z-index: 102; left: 228px; position: absolute;
top: 107px" DataSourceID="SqlDataSource1" AutoGenerateColumns="True">
 
</asp:GridView>
&nbsp; &nbsp;<br/> <br/>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=CRSQLEXPRESS;
Initial Catalog=MyConn;Integrated Security=True"
ProviderName="System.Data.SqlClient"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
 

View 4 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Oct 3, 2007

I am running the following query:
 select distinct hqvend, hqvprd,fprod,idesc,ilead,sum(cast(fqty as int) )as fqty, (cast(hqvend as varchar(5))+'/'+ltrim(rtrim(fprod))+'/'+cast(ilead as varchar(3))) as Keydata,'L1/'+rtrim(fprod) as LocPartno from tblkfpl01 inner join hqtl01 on (fprod=hqprod) inner join iiml01 on (hqvend=ivend and hqprod=iprod) where  cast(cast(hqeff as varchar(8)) as smalldatetime) <= (Select CONVERT(varchar(8), getdate(), 1)) and  cast(HQDIS as varchar(8)) >= (Select CONVERT(varchar(8), getdate(), 1))  and hqvend like '134%' group by hqvend,fprod,hqvprd,idesc,ilead   order by hqvend,fprod
The bold sections are giving me the error message. The query works as written above, butis only evaluating on one date, contained in hqeff. However, when I try to modify the second date, HQDIS, by casting it the same way as the one before it:
 select distinct hqvend, hqvprd,fprod,idesc,ilead,sum(cast(fqty as int) )as fqty, (cast(hqvend as varchar(5))+'/'+ltrim(rtrim(fprod))+'/'+cast(ilead as varchar(3))) as Keydata,'L1/'+rtrim(fprod) as LocPartno from tblkfpl01 inner join hqtl01 on (fprod=hqprod) inner join iiml01 on (hqvend=ivend and hqprod=iprod) where  cast(cast(hqeff as varchar(8)) as smalldatetime) <= (Select CONVERT(varchar(8), getdate(), 1)) and  cast(cast(HQDIS as varchar(8)) as smalldatetime) >= (Select CONVERT(varchar(8), getdate(), 1))  and hqvend like '134%' group by hqvend,fprod,hqvprd,idesc,ilead   order by hqvend,fprod
I get the error message. I need to select based on both dates (hqeff AND HQDIS), but cannot seem to be able to it.... Both fields are of type Dedimal, with a length of 8, and dates stored in them look like: 20071003 (YYYYMMDD).
Any suggestions?

View 2 Replies View Related

Syntax Error Converting Character String To Smalldatetime Data Type.

Aug 8, 2005

Hi guys and gals,I have the following code:CREATE PROCEDURE searchRecords(     @searchFor  NVarchar (25)  = NULL,     @fromDate  NVarchar (25)  = NULL,     @toDate  NVarchar (25)  = NULL)
AS
IF @fromDate IS NOT NULL     DECLARE @fromDateString VarChar(200)     SET @fromDateString = '  CONVERT(smalldatetime, ''' + @fromDate + ''') '
IF @toDate IS NOT NULL     DECLARE @toDateString VarChar(200)     SET @toDateString = '  CONVERT(smalldatetime, ''' + @toDate + ''') '
SELECT * FROM MIPR WHERE      ID = @searchFor      OR DESC_QTY = @searchFor      OR REQ_ACTIVITY = @searchFor      OR ACC_ACTIVITY = @searchFor      OR MANYEARS = @searchFor      OR TECH_POC = @searchFor      OR RESOURCE_POC = @searchFor      OR SI_DATE BETWEEN @fromDateString   AND  @toDateString;GOit creates an error that I can't seem to fix. the error I get is:Syntax error converting character string to smalldatetime data type.How can I fix this? Thanks in advance!

View 1 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Oct 21, 2007

Hi,

I have SQL Server 2005 database table with the following data definition as follows:

ID int
LST_TIME varchar(5)
LST_DATE varchar(21)
LST_WEEK int
SUBJECT varchar(100)


Date format (Month,day YEAR) eg.- October, 21 2007
Time format, 00 - 23 hours, HH:MM eg. - 18:58

, and a VB code as below:

Dim Command As New SqlClient.SqlCommand("SELECT * FROM dbo.LISTS WHERE (LST_WK = DATEPART(wk, GETDATE())) AND (CONVERT(datetime, LST_DATE) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 110)))
AND(CONVERT(smalldatetime, LST_TIME) = CONVERT(smalldatetime, CONVERT(varchar(5), GETDATE(), 108)))", conn)

Dim dataReader As SqlClient.SqlDataReader = Command.ExecuteReader(CommandBehavior.CloseConnection)
While dataReader.Read()

Dim NewAlert As New Alert(dataReader("SUBJECT").ToString(), DateTime.Parse(dataReader("LST_DATE").ToString(),
DateTime.Parse(dataReader("LST_TIME").ToString())))

:
:
:
I am encountering error: System.Data.SqlClient.SqlException: Conversion failed when converting character string to smalldatetime data type, which points to the codes in bold. Just wondering anyone out there has got a solution to my problem. Thank you.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved