converting date field from Informix to SQL nvarchar

I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)

My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.

The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
I would like to have it -> 11/29/00 (datetime)

Any help is greatly appreciated!

JT

The goodness we share is rewarded twice!try to find the record that cause the failure:
select * From <your table name> where isdate(<date varchar column name>)=0

varchars in yyyy-mm-dd format usually converted to dates data type without problems...|||Thank you for the reply.

So your saying, if the table name is findet (financial detail) and the column name is fdate (nvarchar, 50).

Run the select against the varchar column?|||You've got data that can't be converted to date...

To see the offending rows...

SELECT * FROM findet WHERE ISDATE(fdate) = 0|||OK. I am testing it now. When I attempted to convert the column in Enterprise Mgr, I got the following error:

/*

Tuesday, April 13, 2004 13:20:17

User: sa

Server: NYCRPSTOR01

Database: Mysis

Application: MS SQLEM - Data Tools

*/

'findet' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.|||Originally posted by Brett Kaiser
You've got data that can't be converted to date...

To see the offending rows...

SELECT * FROM findet WHERE ISDATE(fdate) = 0

Did you run this?

What did you get?

$1,000 bucks you've got non date data in that column|||That worked!!! Thank you for your help!!!|||What worked?

Did you find non date data?|||before you change the column type find what cause the conversion error!|||There was data mis-entered 0004-04-10 (example). Since the year is out of range, converting it to datetime was not possible.

Now, how do I script my DTS package to exclude records that are out of range and put them in a separate table for exception reporting?

Again, thank you for your help! It is greatly appreciated!!!|||just load the entire data to table with varchar column then copy the wrong date format to errors table and delete these records from the original table, convert the good data from the original table into target table drop the original table fix the failed record and enter them into the target table

Converting CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT!

Hi,
We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.

After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.

PLEASE HELP ME!!!

Thanks,

Firoz AnsariIf you are, in batch, converting all types, you couls just script the database using Enterprise Manager, and then do a search and replace for each of the types. Be aware that converting from varchar to nvarchar could cause problems if rows already contain over 4000 characters.|||I have just created a small VB utility program which can take list of files (.sql files) and uses regular expression to replace data types in SPs files. I am still looking for some automated tool.

Regards

Converting a smallint to an nvarchar

For a SQL statement in an Alias column I am am combing several
columns.
But I am having problems with one column as it is a smallint.

I get this error
Syntax error converting the nvarchar value to a column of data type
smallint

My Sql statement "
Select Stilngcol1 + stringcol2 + intcol1 + stringcol3 As NewColName
from Table1

I was wondering is there anyway to format/convert the smallint to
nvarchar, without changing the database.On 29 Sep 2004 23:06:12 -0700, ree32 wrote:

>I was wondering is there anyway to format/convert the smallint to
>nvarchar, without changing the database.

Hi ree32,

Use CAST(intcol1 AS varchar(3)).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>
> Use CAST(intcol1 AS varchar(3)).
> Best, Hugo

Thanks

I found another way of getting around this
convert(char,intcol1 )

Where would you place your CAST(intcol1 AS varchar(3)) in the context
of the SQL statement|||On 30 Sep 2004 15:31:50 -0700, ree32 wrote:

>I found another way of getting around this
>convert(char,intcol1 )

Hi ree32,

CONVERT and CAST are pretty much equivalent. Except that CONVERT allows
for more control when you're converting date and time data or fractions
and CAST is ANSI standard (ie more portable to other databases).

I use CAST, except when I need the added control CONVERT gives me.

>Where would you place your CAST(intcol1 AS varchar(3)) in the context
>of the SQL statement

It should replace "intcol1".

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql

Converting a nvarchar parameter to datetime

i've got a stored procedure that gets a parameter as nvarchar, and
executes another procedure that needs the same parameter , but as
datetime
i've tried this: (where @.plantingDate is the parameter i get as
nvarchar)
CAST(@.PlantingDate as datetime)
and this:
CONVERT(datetime,@.PlantingDate)
but i get an error message....
what is the right way to do it'What values are you passing in for @.plantingDate? Can you give a couple of
examples of @.plantingDate values?
<friedman30@.gmail.com> wrote in message
news:1148223008.723472.278160@.j73g2000cwa.googlegroups.com...
> i've got a stored procedure that gets a parameter as nvarchar, and
> executes another procedure that needs the same parameter , but as
> datetime
> i've tried this: (where @.plantingDate is the parameter i get as
> nvarchar)
> CAST(@.PlantingDate as datetime)
> and this:
> CONVERT(datetime,@.PlantingDate)
> but i get an error message....
> what is the right way to do it'
>|||On 21 May 2006 07:50:08 -0700, friedman30@.gmail.com wrote:
>i've got a stored procedure that gets a parameter as nvarchar, and
>executes another procedure that needs the same parameter , but as
>datetime
>i've tried this: (where @.plantingDate is the parameter i get as
>nvarchar)
>CAST(@.PlantingDate as datetime)
>and this:
>CONVERT(datetime,@.PlantingDate)
>but i get an error message....
>what is the right way to do it'
Hi friedman30,
If you use CAST(@.PlantingDate AS datetime), the format @.PlantingDate has
to be one of the following:
* yyyymmdd (date only - no dashes, slashes, dots or other punctuation!!)
* yyyy-mm-ddThh:mm:ss (date and time - note the punctuation and the
uppercase "T" between date and time part)
* yyyy-mm-ddThh:mm:ss.mmm (date and time, including milliseconds).
With other formats (such as 03/04/05), it's up to the wisdom of SQL
Server te guess if this is inteded to be mm/ddd/yy, dd/mm/yy, or even
yy/mm/dd. Due to Murphy's Law, SQL Server will probably guess right in
development and wrong in production. <g>
If your nvarchar date is not in one of the above formats, then you can
use CONVERT with a style parameter to force SQL Server to use the chosen
style. E.g. CONVERT(datetime, '03/04/05', 11) will always be evaluated
to April 3rd, 2005. For a full list of style paramters and the
associated date and time formats, see CONVERT in Books Online.
--
Hugo Kornelis, SQL Server MVP|||THANKSsqlsql

Converting a nvarchar parameter to datetime

i've got a stored procedure that gets a parameter as nvarchar, and
executes another procedure that needs the same parameter , but as
datetime
i've tried this: (where @.plantingDate is the parameter i get as
nvarchar)
CAST(@.PlantingDate as datetime)
and this:
CONVERT(datetime,@.PlantingDate)
but i get an error message....
what is the right way to do it'What values are you passing in for @.plantingDate? Can you give a couple of
examples of @.plantingDate values?
<friedman30@.gmail.com> wrote in message
news:1148223008.723472.278160@.j73g2000cwa.googlegroups.com...
> i've got a stored procedure that gets a parameter as nvarchar, and
> executes another procedure that needs the same parameter , but as
> datetime
> i've tried this: (where @.plantingDate is the parameter i get as
> nvarchar)
> CAST(@.PlantingDate as datetime)
> and this:
> CONVERT(datetime,@.PlantingDate)
> but i get an error message....
> what is the right way to do it'
>|||On 21 May 2006 07:50:08 -0700, friedman30@.gmail.com wrote:

>i've got a stored procedure that gets a parameter as nvarchar, and
>executes another procedure that needs the same parameter , but as
>datetime
>i've tried this: (where @.plantingDate is the parameter i get as
>nvarchar)
>CAST(@.PlantingDate as datetime)
>and this:
>CONVERT(datetime,@.PlantingDate)
>but i get an error message....
>what is the right way to do it'
Hi friedman30,
If you use CAST(@.PlantingDate AS datetime), the format @.PlantingDate has
to be one of the following:
* yyyymmdd (date only - no dashes, slashes, dots or other punctuation!!)
* yyyy-mm-ddThh:mm:ss (date and time - note the punctuation and the
uppercase "T" between date and time part)
* yyyy-mm-ddThh:mm:ss.mmm (date and time, including milliseconds).
With other formats (such as 03/04/05), it's up to the wisdom of SQL
Server te guess if this is inteded to be mm/ddd/yy, dd/mm/yy, or even
yy/mm/dd. Due to Murphy's Law, SQL Server will probably guess right in
development and wrong in production. <g>
If your nvarchar date is not in one of the above formats, then you can
use CONVERT with a style parameter to force SQL Server to use the chosen
style. E.g. CONVERT(datetime, '03/04/05', 11) will always be evaluated
to April 3rd, 2005. For a full list of style paramters and the
associated date and time formats, see CONVERT in Books Online.
Hugo Kornelis, SQL Server MVP|||THANKS

Convert/Cast

Hi,
I am trying to convert a nvarchar type date of 01112005 to a datetime type,
I have tired various cast and convert statements and the results come back
but do not let me run any analysis, something as simple as ordering the date
s
would be a start but I cant seem to get that to work. Anyone any ideas or a
m
I doing something really daft which will probably come to me in time.
Thanks!!Try putting in the '/' between month, day and year.
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:50B539C5-2096-4A6A-AB76-AC91B0E14523@.microsoft.com...
> Hi,
> I am trying to convert a nvarchar type date of 01112005 to a datetime
type,
> I have tired various cast and convert statements and the results come back
> but do not let me run any analysis, something as simple as ordering the
dates
> would be a start but I cant seem to get that to work. Anyone any ideas or
am
> I doing something really daft which will probably come to me in time.
> Thanks!!|||"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:50B539C5-2096-4A6A-AB76-AC91B0E14523@.microsoft.com...
> Hi,
> I am trying to convert a nvarchar type date of 01112005 to a datetime
> type,
> I have tired various cast and convert statements and the results come back
> but do not let me run any analysis, something as simple as ordering the
> dates
> would be a start but I cant seem to get that to work. Anyone any ideas or
> am
> I doing something really daft which will probably come to me in time.
> Thanks!!
First of all, is this November 1st or January 11th?
If it's November 1st... Convert it to a yyyymmdd format and Cast to Date.
Cast(right('01112005', 4) + mid('01112005', 3,2) + left('01112005', 2) as
smalldatetime)|||When converting from a date string in character format to the datetime data
type, the string has to be in the right format. The format 01112005 does not
work, but
20050111 will.
Assuming that the column that had the string you wanted to convert named
date_col, the following select would do the trick:
SELECT CAST(SUBSTRING(date_col,5,8) + SUBSTRING(date_col,1,2) +
SUBSTRING(date_col,3,2) AS datetime)
Other formats, such as 01/11/2005 will work as well.
"Phil" wrote:

> Hi,
> I am trying to convert a nvarchar type date of 01112005 to a datetime type
,
> I have tired various cast and convert statements and the results come back
> but do not let me run any analysis, something as simple as ordering the da
tes
> would be a start but I cant seem to get that to work. Anyone any ideas or
am
> I doing something really daft which will probably come to me in time.
> Thanks!!|||
Mark Williams wrote:
> When converting from a date string in character format to the datetime dat
a
> type, the string has to be in the right format. The format 01112005 does n
ot
> work, but
> 20050111 will.
> Assuming that the column that had the string you wanted to convert named
> date_col, the following select would do the trick:
> SELECT CAST(SUBSTRING(date_col,5,8) + SUBSTRING(date_col,1,2) +
> SUBSTRING(date_col,3,2) AS datetime)
> Other formats, such as 01/11/2005 will work as well.
... if you don't mind confusing November 1, 2005 with January 11, 2005,
or having 01/20/2006 rejected as invalid, even though you think it means
January 20, 2006. ;)
When convering a string to datetime in T-SQL, do one of the following:
1. Use the 'YYYYMMDD' or 'YYYYMMDD HH:MM:SS.fff' format, which is
interpreted consistently.
2. Use the 'YYYY-MM-DDTHH:MM:SS.fff' format, which is also interpreted
consistently, so long as the T is in the string. (To express a date-
only in this format, you must supply a time of midnight.)
3. Use CONVERT with the appropriate format code.
Otherwise, you risk misinterpretation or rejection of your data.
Steve Kass
Drew University
> "Phil" wrote:
>|||In 2005, at least, you can do:
declare @.dts nvarchar(50)
declare @.dt datetime
set @.dts = '01/12/2005'
set @.dt = '01/11/2005'
select @.dt
set @.dt = @.dts
select @.dt
William Stacey [MVP]
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:50B539C5-2096-4A6A-AB76-AC91B0E14523@.microsoft.com...
> Hi,
> I am trying to convert a nvarchar type date of 01112005 to a datetime
> type,
> I have tired various cast and convert statements and the results come back
> but do not let me run any analysis, something as simple as ordering the
> dates
> would be a start but I cant seem to get that to work. Anyone any ideas or
> am
> I doing something really daft which will probably come to me in time.
> Thanks!!|||Thanks for all the comments, sorry I should of said what order the date was
in, but just incase anyone was curious which I am sure you wouidn't be :-) i
r
was the 1st of November 2005, thanks againe to one and all!
Phil
"Phil" wrote:

> Hi,
> I am trying to convert a nvarchar type date of 01112005 to a datetime type
,
> I have tired various cast and convert statements and the results come back
> but do not let me run any analysis, something as simple as ordering the da
tes
> would be a start but I cant seem to get that to work. Anyone any ideas or
am
> I doing something really daft which will probably come to me in time.
> Thanks!!

Convert varchar to date on a column

I have a table with a column defined as a nvarchar. The strings contained in the columns are in the form of YYYYMMDD. I searched the forums here and believe that if I have a date as a string then YYYYMMDD is the correct format for a date stored as string. However, I think I need to store the date as a Date type for selecting, sorting, searching, and indexing. What is the best method of converting the entire column to a Date type from a nvarchar type with the assumption that all the string dates are in the same YYYYMMDD format? When I tried to modify the table using the Managment Studio Express, I get a warning: "Data might be lost converting column 'Date' from 'nvarchar(50)'.

Thanks!

DeBug

Update: I just noticed that all the text fields have double quotes at the start and end of each text string so my so called YYYYMMDD is really "YYYYMMDD". So I guess the answer would need to include how to scrub the " with an UPDATE command. I used the DTS that came with Express to import the data from a flat file.

Moving to TSQL forum. There are many ways i can think of doing this, one of the easiest would be to create a new column, call it DateCol2 with datetime datatype, and then do an update statement on the table to set this column properly. it might involve some parsing, but if you make a mistake, you can always try it again since this is just a new column. Once you've updated it correctly, you can drop the original column and rename the new column appropriately.

Someone here can probably give you the proper update statement.

|||

With the help of Google, a little trial and error, and two cups of coffee here is what worked:

I used the DTS Wizard (Hey! SQL Express has a DTS wizard!) to select the flat file with the source data but entered the "Text Qualifier" as a single double quote character. I had previously left that field blank on the first 14 attempts :) Once the column was populated with the string as YYYYMMDD and not "YYYYMMDD" with the quotes, the modify table colulmn converted the strings to dates. I still got the warning message but a quick glance at the table showed a successful conversion. I will format the dates to mm/dd/yyyy for reports and the end user application on the fly (I think...).

I would still like to know what are the advantages, if any, of using that column as a Date type versus a Varchar type. Anyone?

Doug DeBug

|||

For starters, with datetime datatype you can do arithmatic operations using built-in functions (dateadd, datediff, datefirst, etc.), you can also reference it in multiple ways - "December 27, 2006" or "12/27/2006", or using convert(). You can read more about this datatype in Books Online topic "Using Date and Time Data", http://msdn2.microsoft.com/en-us/library/ms180878.aspx.

|||

Create Table #test(mydate varchar(25))

Alter Table #test

Alter column mydate datetime

drop table #test

Adamus

|||

Doug DeBug wrote:

With the help of Google, a little trial and error, and two cups of coffee here is what worked:

I used the DTS Wizard (Hey! SQL Express has a DTS wizard!) to select the flat file with the source data but entered the "Text Qualifier" as a single double quote character. I had previously left that field blank on the first 14 attempts :) Once the column was populated with the string as YYYYMMDD and not "YYYYMMDD" with the quotes, the modify table colulmn converted the strings to dates. I still got the warning message but a quick glance at the table showed a successful conversion. I will format the dates to mm/dd/yyyy for reports and the end user application on the fly (I think...).

I would still like to know what are the advantages, if any, of using that column as a Date type versus a Varchar type. Anyone?

Doug DeBug

Using the field as a datetime vs. varchar() is the logical thing to do. There's no advantage but only a disadvantage. You can't use operators as comparison and you remove the possibility of using BETWEEN.

Adamus

|||

You can do the convert using UPDATE statement and then do ALTER like:

update tbl

set your_col = replace(your_col, '"', '')

go

alter tbl alter column your_col smalldatetime

go

As for the benefits of using datetime/smalldatetime vs varchar type, the obvious ones are:

1. Appropriate type checking and domain enforcement

2. Better performance

3. Compatibility with other built-in date functions (although they accept strings it depends on the format)

4. Storage depending on how you store the date value in character format

5. Ordering semantics that follow the date or datetime rules

There are cases where you may want to store datetime values in more compact form than using datetime/smalldatetime but those need to be done with care & careful consideration.

|||

Thanks for the example. I will have not used T-SQL as much I should but have relied on the GUIs in the past. I will need a solution to update the customer's database at least once per week. Getting the things like quoted strings out of the way now is a plus.

Regards,

DeBug

Convert varbinary(16) to nvarchar

Hi,

Basically. I need to convert a varbinary(16) to a varchar in a sorted procedure (to update a table). [in query analyser key looks like 0x00000000000000000001]

Background:
I'm having to output replication commands from the distributor DB and want to store the sequence I'm upto to not have to repeat the same commands on the next export.

The stored proc for performing the export accepts an nvarchar(22) for the min key A(and this is how I've stored it). The records are then exported with varbinary(16) datatyped keys

I need to then store this key back but cannot seem to convert it.

Any thoughts are much appreciated.
Gaz

I've run into similar issues.

Try converting to a BigInt and storing that way. If the datatype is VarBin(16) and you shove in a value of 16, it will convert it. Usually, if an external application is expected a binary type, and you feed it 16 (without the 0x0 in the front) it will do it's own conversion.

So, you have varbin(16) type, value: 0x00000000000000000001

convert Hex value to Bigint (which, in this case would be = 1)

convert the bigint to a string and store the string (in this case "1"). Later when your app gets the string "1", it might just convert it appropriately. In any event, it's pretty easy to test.

Code Snippet

insert into MyTable (MyStringField)

select convert(varchar(32),convert(bigint,<MyHexValue>))

convert to a valid datetime

have a field that has date value, but the datatype is nvarchar(50)The value is 08/04/2006 23:58:51. When I use the convert to datetime statement, it errors our with the foll error:

How do I convert the datatype to a valid datetime?

select convert(datetime ,08/04/2006 23:58:51)- this errors out with the foll err:

Arithmetic overflow error converting expression to data type datetime.

Both of these work:

DECLARE @.MyDate nvarchar(50)

SET @.MyDate = '08/04/2006 23:58:51'

SELECT CONVERT(datetime ,@.MyDate)

SELECT CONVERT(datetime ,'08/04/2006 23:58:51')

|||

i already use the second and when come to record 500000 this error apper

i check the row nothing change

|||Does that record only contain this exact value '08/04/2006 23:58:51'?...no special characters?|||Use this:

UPDATE

SET DateField = CASE WHEN ISDATE(CharField) THEN CONVERT(datetime ,CharField) ELSE NULL END

Then:

SELECT * FROM TABLE WHERE DateField IS NULL AND CharField IS NOT NULL

And see what is not working and handle them by hand.

|||Thanks Tom it works

convert to a valid datetime

have a field that has date value, but the datatype is nvarchar(50)The value is 08/04/2006 23:58:51. When I use the convert to datetime statement, it errors our with the foll error:

How do I convert the datatype to a valid datetime?

select convert(datetime ,08/04/2006 23:58:51)- this errors out with the foll err:

Arithmetic overflow error converting expression to data type datetime.

Both of these work:

DECLARE @.MyDate nvarchar(50)

SET @.MyDate = '08/04/2006 23:58:51'

SELECT CONVERT(datetime ,@.MyDate)

SELECT CONVERT(datetime ,'08/04/2006 23:58:51')

|||

i already use the second and when come to record 500000 this error apper

i check the row nothing change

|||Does that record only contain this exact value '08/04/2006 23:58:51'?...no special characters?|||Use this:

UPDATE

SET DateField = CASE WHEN ISDATE(CharField) THEN CONVERT(datetime ,CharField) ELSE NULL END

Then:

SELECT * FROM TABLE WHERE DateField IS NULL AND CharField IS NOT NULL

And see what is not working and handle them by hand.

|||Thanks Tom it works

Convert Time?

How can I get the current time in 12H format?
I tried convert(nvarchar,GetDate(),108) changing the 108 to other things but
I cant seem to find the right one. Changing Regional Options in control
panel had no affect..
Thnaks
BUCbuc wrote:
> How can I get the current time in 12H format?
> I tried convert(nvarchar,GetDate(),108) changing the 108 to other
> things but I cant seem to find the right one. Changing Regional
> Options in control panel had no affect..
> Thnaks
> BUC
select convert(varchar(20), getdate(), 100)
David Gugick
Imceda Software
www.imceda.com|||109 should do it... mon dd yyyy hh:mi:ss:mmmAM (or PM)
Check Books Online under CONVERT for details.
Paul
"buc" wrote:

> How can I get the current time in 12H format?
> I tried convert(nvarchar,GetDate(),108) changing the 108 to other things b
ut
> I cant seem to find the right one. Changing Regional Options in control
> panel had no affect..
> Thnaks
> BUC
>
>

Convert nvarchar values to integer

I have imported a text file with various data into sql table. all these values have been imported as nvarchar. I need to convert these into Integer. the format of the values is 10length i.e. 0000000.00.

example of data:
0001028.99 - needs to be shown as 1028.99
222.00 - needs to be shown as 222.00
0000190.89 - needs to be shown as 190.89
2708.99 - needs to be shown as 2708.99
00000-50.99 - needs to be shown as -50.99
-109.79 - needs to be shown as -109.70

as you can see some of the values have leading zeros and some don't.
i have tried converting from nvarchar to int and i get the error cannot convert nvarchar to int, i believe it may be because the data contains negative values as well as positive values.

Is there a split function or position function which i can use to extract the data? or any other methods which i can use would be really helpful.

Thanks

Cast the values as decimal, i.e.: (You don't want integers, that would lose the portion after the decimal.)

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyValue varchar(20)
)

INSERT INTO @.MyTable VALUES ( 0001028.99 )
INSERT INTO @.MyTable VALUES ( 222.00 )
INSERT INTO @.MyTable VALUES ( 0000190.89 )
INSERT INTO @.MyTable VALUES ( 2708.99 )
INSERT INTO @.MyTable VALUES ( 00000-50.99 )
INSERT INTO @.MyTable VALUES ( -109.79 )

SELECT MyValues = cast( MyValue AS decimal(10,2))
FROM @.MyTable

MyValues

1028.99
222.00
190.89
2708.99
-50.99
-109.79

convert nvarchar to int

i have got a table

id name pagenumber(nvarchar)

1 gas pg:231-123

2 dff pg:323-123

i need to copy data from this table to another with page number as

id name pagenumber(int)

1 gas 231

2 dff 323

help me

here it is,

Create Table #data (

[id] Varchar(100) ,

[name] Varchar(100) ,

[pagenumber] Varchar(100)

);

Insert Into #data Values( '1','gas','pg:231-123');

Insert Into #data Values( '2','dff','pg:323-123');

select id,name,substring(pagenumber,charindex(':',pagenumber) +1, charindex('-',pagenumber)-charindex(':',pagenumber)-1)

From

#data

|||

Question to 'nasrene',

Are ALL PageNumber values in the form of 'pg:n%-n%'

No space after the colon, alway a dash separating two sets of characters, no other variations?|||

yes all values n form pg.n%-n%

eg:

pg.23-123.

pg.1-23.

pg. 2-34.

for some there are spacesafter pg.

|||

gets error

Invalid length parameter passed to the substring function.
values eg:

pg.23-123.

pg.2-123.

pg. 2-12.

|||

It now appears that the character after 'pg' is a period. Previously it was a colon.

If it is a period, replace the colon in Manivannan's with a period.

select id,name,substring(pagenumber,charindex(':',pagenumber) +1, charindex('-',pagenumber)-charindex(':',pagenumber)-1)

From

#data

[/quote/

|||i replaced but same error|||

Contrary to your earlier assertion, some of your data does NOT follow the pattern [ pg.n%-n% ].

The error is due to one or more rows NOT containing a dash [-].

Try this query to find the rows that do not have a dash.

SELECT *

FROM MyTable

WHERE MyColumn NOT LIKE '%-%'

|||

THERE are three rows that dont contain a '-'

so what to do

|||

This variation 'should' handle your situation.

All that is required is that the Page Number contain the characters 'pg'. It doesn't matter if there is a colon, a period, a dash, or only the first group of numbers.

Code Snippet

DECLARE @.Data table
( [ID] varchar(20) ,
[Name] varchar(20) ,
[PageNumber] varchar(20)
);

INSERT INTO @.Data VALUES ( '1', 'Gas', 'pg.231-123' );
INSERT INTO @.Data VALUES ( '2', 'Oil', 'pg.323-123' );
INSERT INTO @.Data VALUES ( '3', 'Benzene', 'pg.323' );
INSERT INTO @.Data VALUES ( '4', 'Oxygen', 'pg 323-123' );
INSERT INTO @.Data VALUES ( '5', 'Lubricant', 'pg:323-123' );
INSERT INTO @.Data VALUES ( '6', 'Friction', 'pg. 23' );

SELECT
ID,
Name,
Page = ltrim(substring(PageNumber,(charindex('pg',PageNumber)+3), (isnull(len(PageNumber)-(charindex('pg',PageNumber)+2),0))))
FROM @.Data;

ID Name Page
-- -- --
1 Gas 231-123
2 Oil 323-123
3 Benzene 323
4 Oxygen 323-123
5 Lubricant 323-123
6 Friction 23

|||

need the answer as

id name page

1 gas 231

2 oil 323

etc

|||

This should correct that:

DECLARE @.Data table
( [ID] varchar(20) ,
[Name] varchar(20) ,
[PageNumber] varchar(20)
);

INSERT INTO @.Data VALUES ( '1', 'Gas', 'pg.231-123' );
INSERT INTO @.Data VALUES ( '2', 'Oil', 'pg.323-123' );
INSERT INTO @.Data VALUES ( '3', 'Benzene', 'pg.323' );
INSERT INTO @.Data VALUES ( '4', 'Oxygen', 'pg 323-123' );
INSERT INTO @.Data VALUES ( '5', 'Lubricant', 'pg:323-123' );
INSERT INTO @.Data VALUES ( '6', 'Friction', 'pg. 23' );

SELECT
ID,
Name,
Page = ltrim(substring(PageNumber,(charindex('pg',PageNumber)+3), (isnull(len(PageNumber)-(charindex('-',PageNumber)),0))))

FROM @.Data;

/*

ID Name Page
-- -- --
1 Gas 231
2 Oil 323
3 Benzene 323
4 Oxygen 323
5 Lubricant 323
6 Friction 23

*/

|||

I DONT GET THE CORRECT ANSWER

THE ANSWER I GOT WAS 231-,323- ETC

NEED THE ANSWER AS 231,323

|||

The code supplied immediately above provides the data in the form you requested -UNLESS there is something about the data that you have not clearly communicated. (Carefully examine the sample data included to find out if there is something different about how your data is stored.)

I suggest that you copy the code above to a new query window, and execute it. You 'should' then see that the resulset is as you requested. Then you may substitute your table/column names and 'tweak' it until it works for you.

|||

pp.79-103.
pp.63-78.
pp.45-62.
pp.229-254.
pp.147-164.
pp.75-81.
pp.35-56.
pp.23-33.
pp.5-21.
pp.5-13.
pp.268-282.
pp.257-267.

Convert nvarchar to datetime

I have imported a dbf table into mssql in a char format 19720628.
How can I convert nvarchar to datetime?Hi,
Does 19720628 means 1972-06-28 as a date or is it a number?
--
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Devibez" <Devibez@.discussions.microsoft.com> wrote in message
news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>I have imported a dbf table into mssql in a char format 19720628.
>
> How can I convert nvarchar to datetime?|||Hi,
Yes! This is a date in a nvarchar data type format.
"Danijel Novak" wrote:
> Hi,
> Does 19720628 means 1972-06-28 as a date or is it a number?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Devibez" <Devibez@.discussions.microsoft.com> wrote in message
> news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
> >I have imported a dbf table into mssql in a char format 19720628.
> >
> >
> > How can I convert nvarchar to datetime?
>
>|||DECLARE @.str varchar(10)
SET @.str = '19720628'
SELECT CONVERT(datetime, @.str, 101)|||Thanks!
How can I convert all the rows in a column using the method?
"GlennThomas5" wrote:
> DECLARE @.str varchar(10)
> SET @.str = '19720628'
> SELECT CONVERT(datetime, @.str, 101)
>|||You can do an update:
UPDATE table
SET <datefield> = CONVERT(datetime, <datefield>, 101)
"Devibez" wrote:
> Thanks!
> How can I convert all the rows in a column using the method?
>
> "GlennThomas5" wrote:
> > DECLARE @.str varchar(10)
> >
> > SET @.str = '19720628'
> >
> > SELECT CONVERT(datetime, @.str, 101)
> >
> >|||Thank You!
For some reason when I try to run the upadate I receive the following error
message.
Do you you know why?
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
Thanks Again!
"DLS" wrote:
> You can do an update:
> UPDATE table
> SET <datefield> = CONVERT(datetime, <datefield>, 101)
> "Devibez" wrote:
> > Thanks!
> >
> > How can I convert all the rows in a column using the method?
> >
> >
> > "GlennThomas5" wrote:
> >
> > > DECLARE @.str varchar(10)
> > >
> > > SET @.str = '19720628'
> > >
> > > SELECT CONVERT(datetime, @.str, 101)
> > >
> > >|||you should do a search so see if there are dates that are not in the
proper format. it sounds like there is a numeric value its trying to
convert that is out of sql's date range. you can use ISDATE to see
what values return 0 and those values are out of range.

Convert nvarchar to datetime

I have imported a dbf table into mssql in a char format 19720628.
How can I convert nvarchar to datetime?
Hi,
Does 19720628 means 1972-06-28 as a date or is it a number?
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Devibez" <Devibez@.discussions.microsoft.com> wrote in message
news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>I have imported a dbf table into mssql in a char format 19720628.
>
> How can I convert nvarchar to datetime?
|||Hi,
Yes! This is a date in a nvarchar data type format.
"Danijel Novak" wrote:

> Hi,
> Does 19720628 means 1972-06-28 as a date or is it a number?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Devibez" <Devibez@.discussions.microsoft.com> wrote in message
> news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>
>
|||DECLARE @.str varchar(10)
SET @.str = '19720628'
SELECT CONVERT(datetime, @.str, 101)
|||Thanks!
How can I convert all the rows in a column using the method?
"GlennThomas5" wrote:

> DECLARE @.str varchar(10)
> SET @.str = '19720628'
> SELECT CONVERT(datetime, @.str, 101)
>
|||You can do an update:
UPDATE table
SET <datefield> = CONVERT(datetime, <datefield>, 101)
"Devibez" wrote:
[vbcol=seagreen]
> Thanks!
> How can I convert all the rows in a column using the method?
>
> "GlennThomas5" wrote:
|||Thank You!
For some reason when I try to run the upadate I receive the following error
message.
Do you you know why?
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
Thanks Again!
"DLS" wrote:
[vbcol=seagreen]
> You can do an update:
> UPDATE table
> SET <datefield> = CONVERT(datetime, <datefield>, 101)
> "Devibez" wrote:
|||you should do a search so see if there are dates that are not in the
proper format. it sounds like there is a numeric value its trying to
convert that is out of sql's date range. you can use ISDATE to see
what values return 0 and those values are out of range.

Convert nvarchar to datetime

I have imported a dbf table into mssql in a char format 19720628.
How can I convert nvarchar to datetime?Hi,
Does 19720628 means 1972-06-28 as a date or is it a number?
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Devibez" <Devibez@.discussions.microsoft.com> wrote in message
news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>I have imported a dbf table into mssql in a char format 19720628.
>
> How can I convert nvarchar to datetime?|||Hi,
Yes! This is a date in a nvarchar data type format.
"Danijel Novak" wrote:

> Hi,
> Does 19720628 means 1972-06-28 as a date or is it a number?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Devibez" <Devibez@.discussions.microsoft.com> wrote in message
> news:FDB32DD8-9A67-4E27-91B3-09534AAE8CEE@.microsoft.com...
>
>|||DECLARE @.str varchar(10)
SET @.str = '19720628'
SELECT CONVERT(datetime, @.str, 101)|||Thanks!
How can I convert all the rows in a column using the method?
"GlennThomas5" wrote:

> DECLARE @.str varchar(10)
> SET @.str = '19720628'
> SELECT CONVERT(datetime, @.str, 101)
>|||You can do an update:
UPDATE table
SET <datefield> = CONVERT(datetime, <datefield>, 101)
"Devibez" wrote:
[vbcol=seagreen]
> Thanks!
> How can I convert all the rows in a column using the method?
>
> "GlennThomas5" wrote:
>|||Thank You!
For some reason when I try to run the upadate I receive the following error
message.
Do you you know why?
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated."
Thanks Again!
"DLS" wrote:
[vbcol=seagreen]
> You can do an update:
> UPDATE table
> SET <datefield> = CONVERT(datetime, <datefield>, 101)
> "Devibez" wrote:
>|||you should do a search so see if there are dates that are not in the
proper format. it sounds like there is a numeric value its trying to
convert that is out of sql's date range. you can use ISDATE to see
what values return 0 and those values are out of range.

Convert ntext to nvarchar for ORDER BY?

I need to sort by an ntext field, but it won't let me do it.

However, if I cast the field as nvarchar(100), I can use ORDER BY on that.

Is there any reason that this is a bad idea? In my testing, ordering by a converted ntext field was actually *faster* than ordering by an nvarchar (same data in the fields).

Joshntext is for huge chunks of data (up to 2 gigabytes)

if you can easily sort on the first 100 characters, i would suggest that you do so

i can't see that people are going to notice the difference in sort order beyond the 100th character if in fact two 2-gigabyte fields are identical in the first 100 characters

and why sort on 2 gigabytes if you don't have to?|||It doesn't inherently guarantee uniqueness but you might be able to live with that. You wouldn't be able make use of an index on this column.|||100 characters is plenty for my needs - I just need to sort the recordset for display to the user. Only the first 100 characters will be displayed in the list, anyway.

I'm just wondering if there's a performance penalty due to the conversion, or anything like that.

Thanks!
Josh|||ou will be sorting on a non-indexed column which will be a performance issue.|||Ok, thanks. These columns are user-customizable, so I don't think we will be using indexes on them. Are there any other issues that I need to worry about?

Josh

convert input parameter into field

create procedure [dbo].[findtext]
(
@.fieldname nvarchar(50),
@.searchtext nvarchar(50)
)
AS
SELECT * FROM tablename WHERE @.fieldname = @.searchtext
it doesn't work!Hi Joe,
Use dynamic SQL to build the SQL statement, something like this:
DECLARE @.sql nvarchar(200)
Set @.sql = 'SELECT * FROM tablename WHERE ' + @.fieldname + ' = ''' +
@.searchtext + ''''@.searchtext '''
EXEC(@.sql)
Ray
"joe" wrote:

> create procedure [dbo].[findtext]
> (
> @.fieldname nvarchar(50),
> @.searchtext nvarchar(50)
> )
> AS
> SELECT * FROM tablename WHERE @.fieldname = @.searchtext
>
> it doesn't work!|||joe
You will have to use dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
"joe" <joe@.discussions.microsoft.com> wrote in message
news:97C05AE4-5103-4133-816F-5AD53715DC04@.microsoft.com...
> create procedure [dbo].[findtext]
> (
> @.fieldname nvarchar(50),
> @.searchtext nvarchar(50)
> )
> AS
> SELECT * FROM tablename WHERE @.fieldname = @.searchtext
>
> it doesn't work!|||Like rb and Uri said, dynamic SQL. Be careful to validate your data though,
since that could open you up to SQL injection attacks.
"joe" <joe@.discussions.microsoft.com> wrote in message
news:97C05AE4-5103-4133-816F-5AD53715DC04@.microsoft.com...
> create procedure [dbo].[findtext]
> (
> @.fieldname nvarchar(50),
> @.searchtext nvarchar(50)
> )
> AS
> SELECT * FROM tablename WHERE @.fieldname = @.searchtext
>
> it doesn't work!

Convert Datetime.

Hello All,

In following statement in SQL, I'm first converting 'IssueDate' with style 101 into 'nvarchar' then converting to Datatime.

convert(datetime,convert (nvarchar,Cert_WarehouseDetails.IssuedDateX,101)) <= '3/29/2004')

Which is right in below one.

1. Do I need to first convert into nvarchar then datetime.
e.g. convert(datetime,convert (nvarchar,Cert_WarehouseDetails.IssuedDateX,101)) <= '3/29/2004')

2. Otherwise can I directly convert into datetime.

convert(datetime,Cert_WarehouseDetails.IssuedDateX ,101) <= '3/29/2004')

Please reply to me asap.

Regards,
M. G.Do this

WHERE DATEDIFF(d,'3/29/2004',IssuedDateX) < 0|||I would use:Convert(CHAR(10), Cert_WarehouseDetails.IssuedDateX, 121)if you intend to convert it back to a datetime (to strip off the time).

-PatP|||Doooh...

Either way it's a scan...

USE Northwind
GO

--Type [CTRL]+K

--Index Scan

SELECT OrderDate FROM Orders
WHERE DATEDIFF(d,'7/11/1996',OrderDate) < 0

--Index Seek

SELECT OrderDate FROM Orders
WHERE OrderDate <= '7/11/1996'
GO

Convert DateTime to NVARCHAR

Hi,
My test table looks like this:
1 Id int 4 0
0 DateTimes datetime 8 1
0 DatoTid nvarchar 14 1
With the following content:
1 15-10-2005 10:22:00 15102006102200
2 16-10-2005 11:16:00 17102005111600
I would like to show the records that are different, but I can't see
how to get rid of the <->, <:> and <space> characters.
It would be enough only to get the records that has differents dates
e=2Eg. 16-10-2005<>17102005. I have tried the following statement in
diffrents ways but...
SELECT [Id], CONVERT(NVARCHAR(10), DateTimes, 113) as DatTid, DatoTid
FROM [SQL_Eksempler].[dbo].[Dato]
WHERE CONVERT(NVARCHAR(10), DateTimes, 113)<>Datotid
/Ren=E9Hi
To do a string comparison try using convert for your datatime and then use
replace to get rid of the unwanted characters:
SELECT id, CAST(REPLACE(CONVERT(CHAR(10), DateTimes, 105),'-',SPACE(0)) +
REPLACE(CONVERT(CHAR(8), DateTimes, 108),':',SPACE(0)) AS NVARCHAR(14)),
DatoTie
FROM #Dates
WHERE CAST(REPLACE(CONVERT(CHAR(10), DateTimes, 105),'-',SPACE(0)) +
REPLACE(CONVERT(CHAR(8), DateTimes, 108),':',SPACE(0)) AS NVARCHAR(14)) <>
DatoTie
John
"refdk" wrote:

> Hi,
> My test table looks like this:
> 1 Id int 4 0
> 0 DateTimes datetime 8 1
> 0 DatoTid nvarchar 14 1
> With the following content:
> 1 15-10-2005 10:22:00 15102006102200
> 2 16-10-2005 11:16:00 17102005111600
> I would like to show the records that are different, but I can't see
> how to get rid of the <->, <:> and <space> characters.
> It would be enough only to get the records that has differents dates
> e.g. 16-10-2005<>17102005. I have tried the following statement in
> diffrents ways but...
>
> SELECT [Id], CONVERT(NVARCHAR(10), DateTimes, 113) as DatTid, DatoTid
> FROM [SQL_Eksempler].[dbo].[Dato]
> WHERE CONVERT(NVARCHAR(10), DateTimes, 113)<>Datotid
> /René
>|||Thanks a lot for your help this was exactly what I needed.
Ren=E9