Converting Data Types

I have a table with a field of Char(10) Data type

this field contains records of work time Attendance in a decimal format

Ex. I attend today for 8.30 this means eighthours and thirty mintutes

the main problem faced me to make some calculations on those records (sum, subtract, etc)

so I want to convert the data from char type to decimal or real using the next code but it doesn't work

select (cast (satreg, decimal) + cast (satot, decimal)) from timecard

can u please help me in the main issue how to sum char type records or at least how to convert them to decimal

Regards

create table test

(

time char(10)

)

insert into test (time) values ('8.30')

insert into test (time) values ('9.30')

insert into test (time) values ('10.30')

selectconvert(decimal(10,2), time )

fromtest|||

this can help:

it breaks your 8.30 to 8 and 30

SELECT substring(test,0, (patindex('%.%',test))),substring(test,(patindex('%.%',test)+1),len(test)) from test

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 binary to string

is there a way to query a varbinary column and print the readable char in query analyzer?

thanks in advance!

Ryan

No way to do this, because SQL only store the binary data and has no sense for character encoding and structure of the doc.|||

There is a way, here it is...

declare @.pass varchar(30)
declare @.binPass varbinary(8)
set @.binPass = (select password from tblEMP where empid = '123')
set @.pass = (select cast(@.binPass as varchar(20)))
print reverse(@.pass)

I get characters like squares after the text, but I know to disregard them and only look at the text. Anyone know how to get rid of the squares and weird characters? thanks

Ryan

|||

SQL Server will convert varbinary to varchar what it will not convert is varbinary to text. Try the link below for the complete conversion chart and code samples. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

sqlsql

CONVERT with char string... again ! :)

Folks
what's wrong with
CONVERT(DATETIME, '20060405 14:45:43', 102)
I get the error "Syntax error converting datetime from character
string."
I was under the impression that the syntax YYYYMMDD HH:MM:SS was not
SET DATEFORMAT dependent, not SET LANGUAGE dependent and language
neutral... or, in other words, safe to use... ;)
Probably missing something obvious but can't figure it out.
Any info welcome
Regards
--alexTI have no problem running this whole block of code below
set dateformat mdy
select CONVERT(DATETIME, '20060405 14:45:43', 102)
go
set dateformat dmy
select CONVERT(DATETIME, '20060405 14:45:43', 102)
go
set dateformat ymd
select CONVERT(DATETIME, '20060405 14:45:43', 102)
go
set dateformat ydm
select CONVERT(DATETIME, '20060405 14:45:43', 102)
go
set dateformat myd
select CONVERT(DATETIME, '20060405 14:45:43', 102)
go
set dateformat dym
select CONVERT(DATETIME, '20060405 14:45:43', 102)
go
2006-04-05 14:45:43.000 is returned for every select
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I also tried SET LANGUAGE with various languages, Francais, Arabic, Svenska,
Espaol, Norsk, '?, British, latvieu, Suomi, etc. etc. I could not
get it to fail.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1144242672.204929.291270@.i39g2000cwa.googlegroups.com...
>I have no problem running this whole block of code below
> set dateformat mdy
> select CONVERT(DATETIME, '20060405 14:45:43', 102)
> go
> set dateformat dmy
> select CONVERT(DATETIME, '20060405 14:45:43', 102)
> go
> set dateformat ymd
> select CONVERT(DATETIME, '20060405 14:45:43', 102)
> go
> set dateformat ydm
> select CONVERT(DATETIME, '20060405 14:45:43', 102)
> go
> set dateformat myd
> select CONVERT(DATETIME, '20060405 14:45:43', 102)
> go
> set dateformat dym
> select CONVERT(DATETIME, '20060405 14:45:43', 102)
> go
> 2006-04-05 14:45:43.000 is returned for every select
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Hmm... Thanks !
Must be something else... let me investigate.
--alexT|||both of these will work
select CONVERT(DATETIME, '20060405 14:45:43', 102)
select CONVERT(DATETIME, '20060405 14:45:43')
Madhivanan|||Hi again
It wasmy mistake - apparently some embeded control char :(
Thanks for your help !
--alexTsqlsql

convert varchar or char to float

Hello,
Is there a way to convert varchar or char to float?
Thank youSELECT Convert(float, '1e3')-PatP|||Thank you, what is '1e3' and how to apply it my field?|||1e3 is a float constant. I picked it because it can't be a decimal, money, or any other numeric value, either CHAR or FLOAT and that's it.

-PatP|||I have a field in my table "SellTo" datatype set to varchar
value in the field 000021
how can I convert this field to a float(8)

Thank you|||Hello,
I have a query:
Select
cast(convert(char, cast (FielsNameas money(8)),1) as money ) as FieldName

which has to return data in the format like this:.0000
.0000
44.0000
39.0000
.0000
169.0000
.0000
.0000
85.0000
.0000
288.0000
.0000
.0000
.0000
36.0000
36.0000
36.0000
294.0000

What I have is Incorrect:
.0000
.0000
440.0000
390.0000
.0000
.1690.0000
.0000
.0000
850.0000
.0000
.2880.0000
.0000
.0000
.0000
360.0000
360.0000
360.0000
2940.0000

Please help to solv it.

Thank you|||Just curious, but what are you really trying to do? You've posted several questions, all of which "miss the mark" in some important way. I think if you back off just a bit from the technical details and focus on the business task at hand and explain that for us, then we can probably give you much better (both easier to implement/understand and more useful) answers.

-PatP

Convert Varchar is inconsistent

Hi All,
I have a source table which contains a customer code in a CHAR(8) column.
The customer codes are only ever 5 or 6 characters long, so in the reporting
tables I am extracting data out to, I have been specifying the customer code
column as a VARCHAR(8). [I am aware that for such a small variance its
debatable whether its worth the overhead of having a VARCHAR column, but jus
t
run with me on this one].
In each of the stored procs I have explicitly stated CONVERT(VARCHAR(8),
CustomerCode) but some of my tables still contain a padded 8 character
customer code, despite the datatype being VARCHAR(8) and the sp explicitly
stating convert this code to a varchar. I can verify this with the LEN and
DATALENGTH commands. All the tables are in the same database and I havent
been playing with the SET ANSI_PADDING setting either.
I'm thinking that SQL is evaluating whether its worth doing the conversion,
and sometimes it thinks its worthwhile, and other times it doesnt (maybe on
whether it comes accross a 5 or a 6 character customer first?). However, I
cant find anything about this 'machine learning' feature in BOL.
Has anyone come accross this before? and if so, what did you do to force SQL
into storing the data in the column as a VARCHAR?
TIA,
Bill PColumns of type CHAR are padded with space (See ANSI_PADDING in BOL), so whe
n
you convert to varchar you are converting also the spaces and they are not
trimmed in the convertion. You need to rtrim the converted value.
Example:
create table t (
colA char(8) not null
)
insert into t values('a')
insert into t values('bb')
insert into t values('ccc')
insert into t values('dddd')
insert into t values('eeeee')
insert into t values('gggggg')
insert into t values('hhhhhhhh')
select
convert(varchar(8), colA),
datalength(convert(varchar(8), colA)),
rtrim(convert(varchar(8), colA)),
datalength(rtrim(convert(varchar(8), colA)))
from
t
select
convert(varchar(1), space(1)),
datalength(convert(varchar(1), space(1))),
len(convert(varchar(1), space(1)))
drop table t
go
AMB
"Bill P" wrote:

> Hi All,
> I have a source table which contains a customer code in a CHAR(8) column.
> The customer codes are only ever 5 or 6 characters long, so in the reporti
ng
> tables I am extracting data out to, I have been specifying the customer co
de
> column as a VARCHAR(8). [I am aware that for such a small variance its
> debatable whether its worth the overhead of having a VARCHAR column, but j
ust
> run with me on this one].
> In each of the stored procs I have explicitly stated CONVERT(VARCHAR(8),
> CustomerCode) but some of my tables still contain a padded 8 character
> customer code, despite the datatype being VARCHAR(8) and the sp explicitly
> stating convert this code to a varchar. I can verify this with the LEN an
d
> DATALENGTH commands. All the tables are in the same database and I havent
> been playing with the SET ANSI_PADDING setting either.
> I'm thinking that SQL is evaluating whether its worth doing the conversion
,
> and sometimes it thinks its worthwhile, and other times it doesnt (maybe o
n
> whether it comes accross a 5 or a 6 character customer first?). However,
I
> cant find anything about this 'machine learning' feature in BOL.
> Has anyone come accross this before? and if so, what did you do to force S
QL
> into storing the data in the column as a VARCHAR?
> TIA,
> Bill P|||On Tue, 8 Mar 2005 06:19:05 -0800, Bill P wrote:
(snip)
> [I am aware that for such a small variance its
>debatable whether its worth the overhead of having a VARCHAR column, but ju
st
>run with me on this one].
Hi Bill,
It's not even debatable. CHAR(6) (not CHAR(8)!!) will always take 6
bytes, VARCHAR(6) (or more than 6) will take 7 or 8 bytes for 5 or 6
characters.
But okay - I'll run with you.

>In each of the stored procs I have explicitly stated CONVERT(VARCHAR(8),
>CustomerCode) but some of my tables still contain a padded 8 character
>customer code, despite the datatype being VARCHAR(8) and the sp explicitly
>stating convert this code to a varchar.
That's correct. As CHAR(8), the data got padded with spaces. The
conversion to VARCHAR won't remove the trailing spaces.
Use CONVERT(varchar(8), RTRIM(CustomerCode)) to remove the trailing
spaces and really reduce the length.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Alejandro,
Many thanks for that. Thats something that I hadnt considered, but it makes
total sense. I will ensure that I perform RTRIMs in all the SPs to ensure
consistency.
I doesnt answer why some of my tables were RTRIMing themselves and some
werent though? Thats not something I want you to answer as I am more than
happy with the solution, but its still something that I dont at this point
fully understand. If I find out, I will post it to the group.
Thanks once again,
Bill P
"Alejandro Mesa" wrote:
> Columns of type CHAR are padded with space (See ANSI_PADDING in BOL), so w
hen
> you convert to varchar you are converting also the spaces and they are not
> trimmed in the convertion. You need to rtrim the converted value.
> Example:
> create table t (
> colA char(8) not null
> )
> insert into t values('a')
> insert into t values('bb')
> insert into t values('ccc')
> insert into t values('dddd')
> insert into t values('eeeee')
> insert into t values('gggggg')
> insert into t values('hhhhhhhh')
> select
> convert(varchar(8), colA),
> datalength(convert(varchar(8), colA)),
> rtrim(convert(varchar(8), colA)),
> datalength(rtrim(convert(varchar(8), colA)))
> from
> t
> select
> convert(varchar(1), space(1)),
> datalength(convert(varchar(1), space(1))),
> len(convert(varchar(1), space(1)))
> drop table t
> go
>
> AMB
>
> "Bill P" wrote:
>|||Hi Hugo,
If only life were that simple. If I set these up as CHAR(6) or VARCHAR(6)
you can bet your bottom dollar that the some bright spark will create a new
customer with an 8 character code, simply because the ERP system lets them.
But I take your point.
Bill :-)
"Hugo Kornelis" wrote:

> On Tue, 8 Mar 2005 06:19:05 -0800, Bill P wrote:
> (snip)
> Hi Bill,
> It's not even debatable. CHAR(6) (not CHAR(8)!!) will always take 6
> bytes, VARCHAR(6) (or more than 6) will take 7 or 8 bytes for 5 or 6
> characters.
> But okay - I'll run with you.
>
> That's correct. As CHAR(8), the data got padded with spaces. The
> conversion to VARCHAR won't remove the trailing spaces.
> Use CONVERT(varchar(8), RTRIM(CustomerCode)) to remove the trailing
> spaces and really reduce the length.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Tue, 8 Mar 2005 07:07:04 -0800, Bill P wrote:
(snip)
> the ERP system lets them.
Ah, I see how that changes things. :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Convert string to numeric

I have a field which in the db is is char, and I want to sort in the report
numerically. Tried changing it in many areas but it still sorts as if alpha.
Is there an expression I can use on the report field to convert it to
numeric? Will cast or convert work in a field level experssion?Edit Field and make it like Cint(Fields!MyField.Value)
Then sort on this field.
- Suneet Mohan
"Chris Patten" wrote:
> I have a field which in the db is is char, and I want to sort in the report
> numerically. Tried changing it in many areas but it still sorts as if alpha.
> Is there an expression I can use on the report field to convert it to
> numeric? Will cast or convert work in a field level experssion?
>

Convert string to DateTime

Hi.
I get dates from a text file on this format: 200604281050. What would be the
easiest way to convert it to DateTime format, from char(12)? The example
should be as in: 04.28.2006 10:50 AM (or 28.04.2006. 10:50).
Do I have to build the DateTime field manually through PL/SQL ?
Any hints appreciated
Kind regards
Mr. SmithCAST(LEFT(YourCol,8) + ' ' + RIGHT(YourCol,4) AS DATETIME)
Andrew J. Kelly SQL MVP
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:%23a%23OiT5kGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Hi.
> I get dates from a text file on this format: 200604281050. What would be
> the easiest way to convert it to DateTime format, from char(12)? The
> example should be as in: 04.28.2006 10:50 AM (or 28.04.2006. 10:50).
> Do I have to build the DateTime field manually through PL/SQL ?
> Any hints appreciated
> Kind regards
> Mr. Smith
>|||Andrew, it failed
DECLARE @.st VARCHAR (20)
SET @.st='200604281050'
--select CAST(LEFT(@.st,8) + ' ' + RIGHT(@.st,4) AS DATETIME)
--Server: Msg 242, Level 16, State 3, Line 4
--The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
SELECT CAST(LEFT(@.st,8)+' ' +SUBSTRING(@.st,9,2)+':'+RIGHT(@.st,2) AS
DATETIME)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ecRpjY5kGHA.1264@.TK2MSFTNGP05.phx.gbl...
> CAST(LEFT(YourCol,8) + ' ' + RIGHT(YourCol,4) AS DATETIME)
> --
> Andrew J. Kelly SQL MVP
> "Mr. Smith" <nospam@.blindfolded.gone> wrote in message
> news:%23a%23OiT5kGHA.1640@.TK2MSFTNGP02.phx.gbl...
>|||Try this:
DECLARE @.st VARCHAR (20)
SET @.st='200604281050'
SELECT CONVERT(DATETIME, LEFT(@.st, 8), 112)+CONVERT(DATETIME, SUBSTRING(@.st,
9, 2) + ':' + SUBSTRING(@.st, 11, 2), 114)|||Sorry you need to add the :
Andrew J. Kelly SQL MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eaEycg5kGHA.3512@.TK2MSFTNGP03.phx.gbl...
> Andrew, it failed
> DECLARE @.st VARCHAR (20)
> SET @.st='200604281050'
> --select CAST(LEFT(@.st,8) + ' ' + RIGHT(@.st,4) AS DATETIME)
> --Server: Msg 242, Level 16, State 3, Line 4
> --The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> SELECT CAST(LEFT(@.st,8)+' ' +SUBSTRING(@.st,9,2)+':'+RIGHT(@.st,2) AS
> DATETIME)
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ecRpjY5kGHA.1264@.TK2MSFTNGP05.phx.gbl...
>|||Mr Smith
Why are you using PL/SQL? Isn't that Oracle's SQL dialect?
HTH
Kalen Delaney, SQL Server MVP
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:%23a%23OiT5kGHA.1640@.TK2MSFTNGP02.phx.gbl...
> Hi.
> I get dates from a text file on this format: 200604281050. What would be
> the easiest way to convert it to DateTime format, from char(12)? The
> example should be as in: 04.28.2006 10:50 AM (or 28.04.2006. 10:50).
> Do I have to build the DateTime field manually through PL/SQL ?
> Any hints appreciated
> Kind regards
> Mr. Smith
>|||hehe - I was waiting for someone to ask that question. ;)
*mike hodgson*
http://sqlnerd.blogspot.com
Kalen Delaney wrote:

>Mr Smith
>Why are you using PL/SQL? Isn't that Oracle's SQL dialect?
>
>|||So why didn't you?
--
HTH
Kalen Delaney, SQL Server MVP
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:%23C92PRClGHA.145
6@.TK2MSFTNGP04.phx.gbl...
hehe - I was waiting for someone to ask that question. ;)
mike hodgson
http://sqlnerd.blogspot.com
Kalen Delaney wrote:
Mr Smith
Why are you using PL/SQL? Isn't that Oracle's SQL dialect?|||What I meant was when I was reading through the thread I was thinking
"did anyone notice that the original poster mentioned PL/SQL" and then I
got to your post (Mozilla Thunderbird displays yours last in the thread
when the thread is collapsed). As such I didn't need to ask because you
already had.
*mike hodgson*
http://sqlnerd.blogspot.com
Kalen Delaney wrote:
> So why didn't you?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:%23C92PRClGHA.1456@.TK2MSFTNGP04.phx.gbl...
> hehe - I was waiting for someone to ask that question. ;)
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Kalen Delaney wrote:
>|||:-)
HTH
Kalen Delaney, SQL Server MVP
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:uUF31dClGHA.4776@.TK2MSFTNGP05.phx.gbl...
What I meant was when I was reading through the thread I was thinking "did
anyone notice that the original poster mentioned PL/SQL" and then I got to
your post (Mozilla Thunderbird displays yours last in the thread when the
thread is collapsed). As such I didn't need to ask because you already had.
mike hodgson
http://sqlnerd.blogspot.com
Kalen Delaney wrote:
So why didn't you?
HTH
Kalen Delaney, SQL Server MVP
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:%23C92PRClGHA.1456@.TK2MSFTNGP04.phx.gbl...
hehe - I was waiting for someone to ask that question. ;)
mike hodgson
http://sqlnerd.blogspot.com
Kalen Delaney wrote:
Mr Smith
Why are you using PL/SQL? Isn't that Oracle's SQL dialect?

Convert on Oracle

All
How do I perform select convert(char(4), 'abcdefg') on
Oracle...?
ThanksAre you trying to get the first 4 characters of a string? If so, try looking
for functions like SUBSTRING or LEFT in PL/SQL
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:1f3301c3fc37$8fd4e1c0$a401280a@.phx.gbl...
All
How do I perform select convert(char(4), 'abcdefg') on
Oracle...?
Thanks|||Hi,
You can use the below query in Oracle,
select substr('sqlserver',1,4) from dual;
output
--
sqls
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OrumP5E$DHA.2180@.TK2MSFTNGP09.phx.gbl...
> Are you trying to get the first 4 characters of a string? If so, try
looking
> for functions like SUBSTRING or LEFT in PL/SQL
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:1f3301c3fc37$8fd4e1c0$a401280a@.phx.gbl...
> All
> How do I perform select convert(char(4), 'abcdefg') on
> Oracle...?
> Thanks
>

Convert on Oracle

All
How do I perform select convert(char(4), 'abcdefg') on
Oracle...?
ThanksAre you trying to get the first 4 characters of a string? If so, try looking
for functions like SUBSTRING or LEFT in PL/SQL
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
<anonymous@.discussions.microsoft.com> wrote in message
news:1f3301c3fc37$8fd4e1c0$a401280a@.phx.gbl...
All
How do I perform select convert(char(4), 'abcdefg') on
Oracle...?
Thanks|||Hi,
You can use the below query in Oracle,
select substr('sqlserver',1,4) from dual;
output
--
sqls
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OrumP5E$DHA.2180@.TK2MSFTNGP09.phx.gbl...
> Are you trying to get the first 4 characters of a string? If so, try
looking
> for functions like SUBSTRING or LEFT in PL/SQL
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:1f3301c3fc37$8fd4e1c0$a401280a@.phx.gbl...
> All
> How do I perform select convert(char(4), 'abcdefg') on
> Oracle...?
> Thanks
>

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 Money to Char

I have a sql query listed below, I would like to count the number of values
that the first 5 characters are '80438' from iNum field.
Please help me correct the sql query listed below.
Thank You,
SELECT count(iNum) from Call_Movement
where substring(cast(iNum as money) as char(20),1,5) like '80438'
iNum Money Format
iNum Data In Call_Movement Table
803482000146220.0000
803482000147143.0000
803482000153805.0000You are very close. You're mistake is mostly with the "like."
Select count(iNum)
from Call_Movement
where left(cast(iNum as varchar(30)),5) = '80348'
I do not know your data as well as you, but I can see a day when there
will be more than twenty characters available and this code failing.
Joe K. wrote:
> I have a sql query listed below, I would like to count the number of value
s
> that the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000|||Dear joe,
instead of cast and substring, u can use convert()
e.g.,
SELECT count(iNum) from Call_Movement
where convert(varchar(50), iNum,0) like '80438%'
Thanks & Regards
Ravi
"Joe K." wrote:

> I have a sql query listed below, I would like to count the number of value
s
> that the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>
>

CONVERT Money Char(20)

I have a sql query listed below that I would like to only output values like
'6024', the Bonus values are converted to char(20) from Call_Movement table.
SELECT cast(cast(Bonus as money) as char(20)) like = '6024' from
Call_Movement
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
Please help me complete this task.Joe K. wrote:
> I have a sql query listed below that I would like to only output values like
> '6024', the Bonus values are converted to char(20) from Call_Movement table.
>
> SELECT cast(cast(Bonus as money) as char(20)) like = '6024' from
> Call_Movement
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> Please help me complete this task.
Please post with table structure and simple data and desired output.
if you are not using wildcard then no need to use like in the query
just use =.
Also you can not put = after like and you have to put like operator in
where clause.
Regards
Amish Shah

CONVERT Money Char(20)

I have a sql query listed below that I would like to only output values like
'6024', the Bonus values are converted to char(20) from Call_Movement table.
SELECT cast(cast(Bonus as money) as char(20)) like = '6024' from
Call_Movement
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
Please help me complete this task.Joe K. wrote:

> I have a sql query listed below that I would like to only output values li
ke
> '6024', the Bonus values are converted to char(20) from Call_Movement tabl
e.
>
> SELECT cast(cast(Bonus as money) as char(20)) like = '6024' from
> Call_Movement
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> Please help me complete this task.
Please post with table structure and simple data and desired output.
if you are not using wildcard then no need to use like in the query
just use =.
Also you can not put = after like and you have to put like operator in
where clause.
Regards
Amish Shah

convert from char(6) to datetime

Hi there
I receive a very much unstructured flat file, which I import into a SQL
Server database using DTS. After the import, one of the flat file columns is
in the format char(6) rather than datetime. The problem seems to be that the
text file column is in the format 'ddmmyy'. When I use the CONVERT statement
(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
"The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value".
Any ideas?
Best regards
Loane
Loane,
Try this (untested), using one of the format codes
from the Books Online article CAST and CONVERT:
CONVERT(datetime,RIGHT(WEDate,2)+SUBSTRING(WEDate, 3,2)+LEFT(WEDate,2),12)
Loane Sharp wrote:

>Hi there
>I receive a very much unstructured flat file, which I import into a SQL
>Server database using DTS. After the import, one of the flat file columns is
>in the format char(6) rather than datetime. The problem seems to be that the
>text file column is in the format 'ddmmyy'. When I use the CONVERT statement
>(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
>"The conversion of char data type to smalldatetime data type resulted in an
>out-of-range smalldatetime value".
>Any ideas?
>Best regards
>Loane
>
>

convert from char(6) to datetime

Hi there
I receive a very much unstructured flat file, which I import into a SQL
Server database using DTS. After the import, one of the flat file columns is
in the format char(6) rather than datetime. The problem seems to be that the
text file column is in the format 'ddmmyy'. When I use the CONVERT statement
(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
"The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value".
Any ideas?
Best regards
LoaneLoane,
Try this (untested), using one of the format codes
from the Books Online article CAST and CONVERT:
CONVERT(datetime,RIGHT(WEDate,2)+SUBSTRI
NG(WEDate,3,2)+LEFT(WEDate,2),12)
Loane Sharp wrote:

>Hi there
>I receive a very much unstructured flat file, which I import into a SQL
>Server database using DTS. After the import, one of the flat file columns i
s
>in the format char(6) rather than datetime. The problem seems to be that th
e
>text file column is in the format 'ddmmyy'. When I use the CONVERT statemen
t
>(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message tha
t
>"The conversion of char data type to smalldatetime data type resulted in an
>out-of-range smalldatetime value".
>Any ideas?
>Best regards
>Loane
>
>

Convert float to char

Select Cast('100.1234' as float)
give me the result 100.1234
Now when I convert it back to char I want exactly 100.1234
Select Convert(char(100),Cast('100.1234' as float))
Gives me 100.123 (Here I was expecting 100.1234)

When I do
Select STR(Cast('100.1234' as float),25,4)
I get back the result as 100.1234

However here I am not sure how many digits do I have after the decimal
point. If I put some value like
Select STR(Cast('100.1234' as float),25,8)
I get 0's appended to it, which is again not desired.

Thanks in advance,
Jai
jaijai_kumar@.hotmail.com wrote:

> Select Cast('100.1234' as float)
> give me the result 100.1234
> Now when I convert it back to char I want exactly 100.1234
> Select Convert(char(100),Cast('100.1234' as float))
> Gives me 100.123 (Here I was expecting 100.1234)
> When I do
> Select STR(Cast('100.1234' as float),25,4)
> I get back the result as 100.1234
> However here I am not sure how many digits do I have after the decimal
> point. If I put some value like
> Select STR(Cast('100.1234' as float),25,8)
> I get 0's appended to it, which is again not desired.
>
> Thanks in advance,
> Jai

Hi Jai. I want to warn you about going down this path.
You will never get exactly what you want all the time,
because there is a base converson from base 10 to binary,
and there are lots of distressingly simple-seeming
decimal numbers that do not convert exactly to a binary
float. Then converting back will show the loss by giving
a different value. I suggest using a DECIMAL datatype
if you want to retain exactitude...

Joe Weinstein at BEA Systems|||(jaijai_kumar@.hotmail.com) writes:
> Select Cast('100.1234' as float)
> give me the result 100.1234
> Now when I convert it back to char I want exactly 100.1234
> Select Convert(char(100),Cast('100.1234' as float))
> Gives me 100.123 (Here I was expecting 100.1234)
> When I do
> Select STR(Cast('100.1234' as float),25,4)
> I get back the result as 100.1234
> However here I am not sure how many digits do I have after the decimal
> point. If I put some value like
> Select STR(Cast('100.1234' as float),25,8)
> I get 0's appended to it, which is again not desired.

Since a float is an approximate number, this is not any exact science.
Consider:

SELECT cast('100.1235' as float)

this gives in Query Analyzer:

100.12350000000001

So when you convert it to string, which value do you want?

Anyway, here is a horrible expression that achieves what you are looking
for. But note the caveate above, and be aware that you may not always
get what you want.

Select reverse(substring(x, patindex('%[^0]%', x), 25))
from (Select x = reverse(ltrim(str(Cast('100.1235' as float), 25, 8)))) y

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is another horrible expression, that does the same thing:

select replace(rtrim(replace(ltrim(str(Cast('100.1235' as float),
25, 8)),'0',' ')),' ','0')

Razvan|||Hello all,

Thanks a lot for all your messages.
Erland your work around gave me a good hint.
I have created a small function to do the same.

Jai
jaijai_kumar@.hotmail.com wrote:
> Select Cast('100.1234' as float)
> give me the result 100.1234
> Now when I convert it back to char I want exactly 100.1234
> Select Convert(char(100),Cast('100.1234' as float))
> Gives me 100.123 (Here I was expecting 100.1234)
> When I do
> Select STR(Cast('100.1234' as float),25,4)
> I get back the result as 100.1234
> However here I am not sure how many digits do I have after the decimal
> point. If I put some value like
> Select STR(Cast('100.1234' as float),25,8)
> I get 0's appended to it, which is again not desired.
>
> Thanks in advance,
> Jai

CONVERT Float to char

We have a table that was imported from Access to SQL 2000. For some reason
the old data had phone numbers set as Float. ? If anyone can explain that
I'd love to hear it.
Anyway, I have a SQL 2000 table that has a 10 character column called
Phone_No and when I perform the INSERT I get 7.12345+009. What can I do to
just get the 10 character phone converted from Float to char?
Thank you,
AnthonyUse the STR Function STR(F, N, R) ,
Where F is any numeric value,
N is integer total number of characters you want output, and
R is integer Number of characters to right of decimal point
as example
Declare @.F Float Set @.F = 8052080080
Select @.F, Str(@.F, 10, 0)
"Anthony W DiGrigoli" wrote:

> We have a table that was imported from Access to SQL 2000. For some reason
> the old data had phone numbers set as Float. ? If anyone can explain tha
t
> I'd love to hear it.
> Anyway, I have a SQL 2000 table that has a 10 character column called
> Phone_No and when I perform the INSERT I get 7.12345+009. What can I do to
> just get the 10 character phone converted from Float to char?
> Thank you,
> Anthony