converting fat32 to ntfs

Hi,
I have microsoft sql server installed on fat32 partition and I want to convert it to ntfs.
Should I expect any problems with my DB while/after doing that ???? You aren't going to try to covert it with database files on it are you?sqlsql

converting diagram to word

i want to convert my diagram that I have created in SQL
server 2000 to word an editable format e.g. jpg.
(not using print screen)
thanks to anyone who can helpYou can use a third-party product like Paint Shop Pro to capture the
image, edit it, and save it as a jpg.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 6 Nov 2003 05:19:27 -0800, "koby"
<anonymous@.discussions.microsoft.com> wrote:
>i want to convert my diagram that I have created in SQL
>server 2000 to word an editable format e.g. jpg.
>(not using print screen)
>thanks to anyone who can help|||I believe that screen capture utilities like SnagIt capture to jpg. =Perhaps that method would work for you.
-- Keith
"koby" <anonymous@.discussions.microsoft.com> wrote in message =news:044401c3a468$9a9f48b0$a001280a@.phx.gbl...
> i want to convert my diagram that I have created in SQL > server 2000 to word an editable format e.g. jpg.
> > (not using print screen)
> > thanks to anyone who can help

converting default check constraint messages to friendly ones

I'm trying to set up a table that will convert default SQL error messages
for check constraints into friendly front end messages. What I'm having
trouble with is how to pick apart the default message so I can do this. Is
the error stored anywhere that I can look at it's parts? Any suggestions on
a good strategy for this sort of thing would be appreciated.
Thanks,
KeithFriendly error msg is always welcome by users. Though, this is the arena of
the presentation level. So, you just intercept the returned error code and
use your custom msg instead of the err.message.
-oj
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:e80rgIRTFHA.1404@.TK2MSFTNGP09.phx.gbl...
> I'm trying to set up a table that will convert default SQL error messages
> for check constraints into friendly front end messages. What I'm having
> trouble with is how to pick apart the default message so I can do this. Is
> the error stored anywhere that I can look at it's parts? Any suggestions
> on
> a good strategy for this sort of thing would be appreciated.
> Thanks,
> Keith
>|||Thanks, OJ. But what code? I know how to do that in stored procedures but in
Check Constraints, where is there a code? I created a constriant that
prevents zero length strings on a column in a table as follows:
Len(LTrim(RTrim(CustName))) > 0
And the message that is returned is:
Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
statement conflicted with COLUMN CHECK constraint
'CK_tblCustomers_CustName'. The conflict occurred in database 'WidgetsInc',
table 'tblCustomers', column 'CustName'.
[Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
terminated.'
If I can always be guaranteed that the message will start with "Database
error: '[Microsoft][ODBC SQL Server Driver][SQL Server]" and end with "
[Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
terminated.'" then I suuppose I can parse it out. But I was hoping there
would be an easier way to do it.
Keith
"oj" <nospam_ojngo@.home.com> wrote in message
news:OKznpORTFHA.2548@.TK2MSFTNGP14.phx.gbl...
Friendly error msg is always welcome by users. Though, this is the arena of
the presentation level. So, you just intercept the returned error code and
use your custom msg instead of the err.message.
-oj|||Keith,
I think you misunderstood me.
What I mean is that you would want to handle the display of the message at
the client side (i.e. VB, Web, etc.). You would just watch for the error
number returned (for this particular one, it's 547) then intercept the error
message before showing it to the user.
No, you do not have the capability to set the error message for constraint
inside sqlserver. Well, you could if you want to override the system ones
(not recommended!). All of the sql error messages you get are stored inside
master.dbo.sysmessages. They're nothing more than just generic strings where
sqlserver inserts the actual object names for the incident before returning
it.
-oj
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%235ONvURTFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Thanks, OJ. But what code? I know how to do that in stored procedures but
> in
> Check Constraints, where is there a code? I created a constriant that
> prevents zero length strings on a column in a table as follows:
> Len(LTrim(RTrim(CustName))) > 0
> And the message that is returned is:
> Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE
> statement conflicted with COLUMN CHECK constraint
> 'CK_tblCustomers_CustName'. The conflict occurred in database
> 'WidgetsInc',
> table 'tblCustomers', column 'CustName'.
> [Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
> terminated.'
> If I can always be guaranteed that the message will start with "Database
> error: '[Microsoft][ODBC SQL Server Driver][SQL Server]" and end with "
> [Microsoft][ODBC SQL Server Driveer][SQL Server]The statement has been
> terminated.'" then I suuppose I can parse it out. But I was hoping there
> would be an easier way to do it.
> Keith
>|||Thanks, OJ. No, I did understand. Just that there's no error number
returned to the client side (using Delphi). Just the text I showed you
below. I'll investigate this further in the Borland NGs. Thanks
again. -keith
"oj" <nospam_ojngo@.home.com> wrote in message
news:egat8fRTFHA.2908@.TK2MSFTNGP10.phx.gbl...
Keith,
I think you misunderstood me.
What I mean is that you would want to handle the display of the message at
the client side (i.e. VB, Web, etc.). You would just watch for the error
number returned (for this particular one, it's 547) then intercept the error
message before showing it to the user.
No, you do not have the capability to set the error message for constraint
inside sqlserver. Well, you could if you want to override the system ones
(not recommended!). All of the sql error messages you get are stored inside
master.dbo.sysmessages. They're nothing more than just generic strings where
sqlserver inserts the actual object names for the incident before returning
it.
-oj

converting decimal to time

how do you convert a numeric to time format if it shows hours but a decimal figure for Minutes. For example if I have hours in decimal format like this

28.5000

but I want to show it in this format:

28:30:00

where 28 = hours, 30 = minutes, 00 = seconds

Thanks.This is a presentation issue. It should be handled at the client, not the server. Dealing with formatting inside the database is just a recipe for problems later.

-PatP|||What Pat said

DECLARE @.hours decimal(15,4)
SELECT @.hours = 28.5
SELECT RIGHT('00' + CONVERT(varchar(2),FLOOR(@.hours)),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60))),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60)-FLOOR(((@.hours-FLOOR(@.hours))*60)))*60),2)|||Displaying it as 28:30:00 is a presentation issue, but converting it to a valid datetime format falls within the scope of the database server:declare @.Hours decimal (6, 4)
set @.Hours = 28.5
select dateadd(minute, @.Hours * 60, 0)|||I was gonna give them that, but I realized it wasn't what they asked for...

Hours of what BTW...sounds like derived data gotta be careful with that

Converting Decimal to String W/O Decimal Point

I'd like to convert a Decimal value into a string so that the entire
original value and length remains intact but there is no decimal point.

For example, the decimal value 6.250 is selected as 06250.

Can this be done?select replace(cast (6.250 as varchar),'.','')

JD wrote:
> I'd like to convert a Decimal value into a string so that the entire
> original value and length remains intact but there is no decimal
point.
> For example, the decimal value 6.250 is selected as 06250.
> Can this be done?|||select replace(cast (6.250 as varchar),'.','')|||but length doesn't remain intact|||but length doesn't remain intact|||select replace(space(1)+replace(cast (6.250 as varchar),'.',''),' ','0')|||Thanks!!!

Converting decimal numbers to Words

Hi there all the Gurus,
I am trying to mave a convert a decimal number ie. 1230.30 to words in my report and i am getting result with the 30/100 at the back of the words.
Is there any way i can get a result of :
'ONE THOUSAND TWO HUNDRED THIRTY AND THIRTY'Well, I guess you could split the number into two parts (at the decimal point) and convert each separately.|||UpperCase(TOWORDS(integer({INVOICE.AMOUNT}))) + Uppercase(TOWORDS(fraction({INVOICE.AMOUNT}))) + 'CENTS ONLY'

i have tried this formula but it keeps prompting error "Missing '('

Please advice|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');|||Cool! Many thanks for the solution.|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');

Hi, if I want to convert a Sum of Amount into English Words? How to do this?|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');

Hi there,

If i am not wrong, you must create a formula or using the SUM maths function to get the total amount then put it replacing the (12345.67)

or we shall hear what's the Guru says :)|||Hi there,

If i am not wrong, you must create a formula or using the SUM maths function to get the total amount then put it replacing the (12345.67)

or we shall hear what's the Guru says :)

Yap, I have already tried in this way, which replace the 12345.67 into sum(@.amount), yet it doesn't work.

local stringvar sNumber := cstr(sum({@.Amount}));
local numbervar whole := truncate(sum({@.Amount}));
local numbervar pos := instrrev(sNumber, '.');

local numbervar decimal := 0;
if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Ringgit and ' + ToWords(decimal, 0) + ' Sen Sahaja');|||What error do you get? Presumably 'This field cannot be summarised' when you run the report as you can't sum a formula {@.amount}.
Try summing something that can be summed like the underlying database value, or maybe {@.amount} is already your summed value?sqlsql

Converting DBF to SQL Server

Hi,
I am working with a 3rd party application that uses DBF files. We are in the
process of discontinuing our contract with them and we need to convert the
data to SQL Server. I setup a Linked Server to access the data, but when I
try to select from it I get an error. Details below.
Any assistance you can provide is greatly appreciated.
Thank you,
Michael
SELECT * From
OpenQuery(DBF, 'select * from Person')
Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from Person'. The OLE DB provider
'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to proc
ess
object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0
',
Query=select * from Person'].
Message posted via http://www.droptable.comHi Michael,
First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
FoxPro OLE DB data provider, downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
follows:
-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC file
is present in the directory) use only the path to the directory and put
double quotes around it if it has spaces. Something like '"C:\My
Directory\"'
-- If a DBC files is present the @.DataSrc value needs to point to the DBC
file as below.
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND', -- Your linked server name here
@.srvproduct=N'Visual FoxPro 9', -- can be anything
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
Then, you can query your tables like:
Select * From YourLinkedServer...YourTable
-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
if your data source points to a DBC file just use the table name you want to
select from.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelg via droptable.com" <u13012@.uwe> wrote in message
news:63d4247ce59e5@.uwe...
> ... DBF files. ...I setup a Linked Server to access the data, but when I
> try to select from it I get an error. Details below.

> SELECT * From
> OpenQuery(DBF, 'select * from Person')
> Error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'select * from Person'. The OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' ....|||Hi Cindy,
Thanks for the feedback. The setup we have is a DBF and a DBT file in the
folder. The options below do not work. When I run Select * from LinkedServer
.
Table, I get the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Cannot locate the requested Xbase mem
o
file.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBSchemaR
owset:
:GetRowset returned 0x80004005: ].
Hopefully this sheds some light on my issue?
Thanks again,
Michael
Cindy Winegarden wrote:[vbcol=seagreen]
>Hi Michael,
>First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
>FoxPro OLE DB data provider, downloadable from
>msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
>follows:
>-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC fil
e
>is present in the directory) use only the path to the directory and put
>double quotes around it if it has spaces. Something like '"C:\My
>Directory\"'
>-- If a DBC files is present the @.DataSrc value needs to point to the DBC
>file as below.
>EXEC master.dbo.sp_addlinkedserver
> @.server = N'VFP_NORTHWIND', -- Your linked server name here
> @.srvproduct=N'Visual FoxPro 9', -- can be anything
> @.provider=N'VFPOLEDB',
> @.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
>9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
>Then, you can query your tables like:
>Select * From YourLinkedServer...YourTable
>-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
>if your data source points to a DBC file just use the table name you want t
o
>select from.
>
>
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||Hi Michael,
The contents of FoxPro Memo fields are stored in FPT files with names
matching the name of the DBF they go with. Some other DBF file formats,
perhaps Clipper, use DBT files for their Memo fields.
If this is a one-time thing try changing the extension of the DBT file to
FPT. However, if the DBF's table header format is incompatible with Jet (or
FoxPro OLE DB if you use that) then I can't help you further. You'll have to
find out exactly what application created the files and probably use drivers
related to that table format.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelgangeri via droptable.com" <u13012@.uwe> wrote in message
news:63df901b8d1c5@.uwe...

> Thanks for the feedback. The setup we have is a DBF and a DBT ...

> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Cannot locate the requested Xbase m
emo
> file.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBSchemaRowset:
> :GetRowset returned 0x80004005: ].

Converting Date-Time to Date Conundrum

I need to be able to convert a Date-Time Field to a Date Fied in my SQL
Tables.
Using CONVERT (varchar, "Date-Time Field", 103) gives me the correct result
but because it converts the date value to a string I can say goodbye to
localization.... any ideas on how to convert the field but still allow
localization.Try this function...
CREATE FUNCTION [dbo].[fnRemoveTimeFromDateTime] (@.InputDate DATETIME)
RETURNS DATETIME AS
BEGIN
DECLARE @.OUTPUT AS SMALLDATETIME
SET @.OUTPUT = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @.InputDate)))
RETURN @.OUTPUT
END
"SAcanuck" wrote:
> I need to be able to convert a Date-Time Field to a Date Fied in my SQL
> Tables.
> Using CONVERT (varchar, "Date-Time Field", 103) gives me the correct result
> but because it converts the date value to a string I can say goodbye to
> localization.... any ideas on how to convert the field but still allow
> localization.

Converting Datetime to Date

I need to convert a datetime, coming from a database table, to a date. How
do I do this within my report?
--
DonIs this just for display or do you really want to convert to date for further
manipulation as a date?
If it is only for display, use =format(Fields!MyDateField.Value,
"MMM-dd-yyyy") or something like it.
If it is for further manipulation as a date, use
=CDate(Fields!MyDateField.Value)
HTH
Charles Kangai, MCT, MCDBA
"Don" wrote:
> I need to convert a datetime, coming from a database table, to a date. How
> do I do this within my report?
> --
> Don|||This is just for display. I have a related problem. In this report I have
multiple datasets. With the multiple datasets If I drag a dataset field onto
the report designer and look at the default expression for the field it looks
like this:
=First(Fields!DateSigned.Value, "AppDetailDS")
The "AppDetailDS" being the dataset name.
I tryed modifying 2 different ways as follows:
=Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
=Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
I cant seem to get the syntax correct when modifying the expression. I get
errors when I try to preview.
Also, I have tried using immediate if's(iif) and cant get that to work. I
have no problem in a report with only one dataset.
Any thoughts.
"Charles Kangai" wrote:
> Is this just for display or do you really want to convert to date for further
> manipulation as a date?
> If it is only for display, use =format(Fields!MyDateField.Value,
> "MMM-dd-yyyy") or something like it.
> If it is for further manipulation as a date, use
> =CDate(Fields!MyDateField.Value)
> HTH
> Charles Kangai, MCT, MCDBA
>
> "Don" wrote:
> > I need to convert a datetime, coming from a database table, to a date. How
> > do I do this within my report?
> >
> > --
> > Don|||Remove the "AppDetailDS" from your Format formula below, then capitalize the
"mmm" to "MMM". You only need two parameters for the Format function.
You should just have
=Format(Fields!DateSigned.Value,"MM-dd-yyyy")
HTH
Charles Kangai, MCT, MCDBA
"Don" wrote:
> This is just for display. I have a related problem. In this report I have
> multiple datasets. With the multiple datasets If I drag a dataset field onto
> the report designer and look at the default expression for the field it looks
> like this:
> =First(Fields!DateSigned.Value, "AppDetailDS")
> The "AppDetailDS" being the dataset name.
> I tryed modifying 2 different ways as follows:
> =Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
> =Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
> I cant seem to get the syntax correct when modifying the expression. I get
> errors when I try to preview.
> Also, I have tried using immediate if's(iif) and cant get that to work. I
> have no problem in a report with only one dataset.
> Any thoughts.
> "Charles Kangai" wrote:
> > Is this just for display or do you really want to convert to date for further
> > manipulation as a date?
> >
> > If it is only for display, use =format(Fields!MyDateField.Value,
> > "MMM-dd-yyyy") or something like it.
> >
> > If it is for further manipulation as a date, use
> > =CDate(Fields!MyDateField.Value)
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> >
> >
> > "Don" wrote:
> >
> > > I need to convert a datetime, coming from a database table, to a date. How
> > > do I do this within my report?
> > >
> > > --
> > > Don|||If I remove the "AppDetailDS", When I preview, I get an error: "the value
expression for the textbox 'DateSigned' uses an aggregate expression without
a scope. A scope is required for all aggregates use outside of a data region
unless the report contains exactly one data set.
"Charles Kangai" wrote:
> Remove the "AppDetailDS" from your Format formula below, then capitalize the
> "mmm" to "MMM". You only need two parameters for the Format function.
> You should just have
> =Format(Fields!DateSigned.Value,"MM-dd-yyyy")
> HTH
> Charles Kangai, MCT, MCDBA
> "Don" wrote:
> > This is just for display. I have a related problem. In this report I have
> > multiple datasets. With the multiple datasets If I drag a dataset field onto
> > the report designer and look at the default expression for the field it looks
> > like this:
> > =First(Fields!DateSigned.Value, "AppDetailDS")
> > The "AppDetailDS" being the dataset name.
> >
> > I tryed modifying 2 different ways as follows:
> > =Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
> > =Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
> >
> > I cant seem to get the syntax correct when modifying the expression. I get
> > errors when I try to preview.
> >
> > Also, I have tried using immediate if's(iif) and cant get that to work. I
> > have no problem in a report with only one dataset.
> >
> > Any thoughts.
> >
> > "Charles Kangai" wrote:
> >
> > > Is this just for display or do you really want to convert to date for further
> > > manipulation as a date?
> > >
> > > If it is only for display, use =format(Fields!MyDateField.Value,
> > > "MMM-dd-yyyy") or something like it.
> > >
> > > If it is for further manipulation as a date, use
> > > =CDate(Fields!MyDateField.Value)
> > >
> > > HTH
> > >
> > > Charles Kangai, MCT, MCDBA
> > >
> > >
> > >
> > > "Don" wrote:
> > >
> > > > I need to convert a datetime, coming from a database table, to a date. How
> > > > do I do this within my report?
> > > >
> > > > --
> > > > Don|||Try to not put textboxes outside of data regions. My suggestion is that you
use containers such as list or table data region to put your textboxes in.
The Format function does not have a scope parameter, so it should work. The
First function you are using is an aggregate function, so it may need a scope
parameter.
But the first thing you need to do is to place a list or table data region
on your screen. Bind it to a dataset using the Properties dialog, then add
textboxes inside of it.
HTH
Charles Kangai, MCT, MCDBA
"Don" wrote:
> If I remove the "AppDetailDS", When I preview, I get an error: "the value
> expression for the textbox 'DateSigned' uses an aggregate expression without
> a scope. A scope is required for all aggregates use outside of a data region
> unless the report contains exactly one data set.
> "Charles Kangai" wrote:
> > Remove the "AppDetailDS" from your Format formula below, then capitalize the
> > "mmm" to "MMM". You only need two parameters for the Format function.
> > You should just have
> > =Format(Fields!DateSigned.Value,"MM-dd-yyyy")
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Don" wrote:
> >
> > > This is just for display. I have a related problem. In this report I have
> > > multiple datasets. With the multiple datasets If I drag a dataset field onto
> > > the report designer and look at the default expression for the field it looks
> > > like this:
> > > =First(Fields!DateSigned.Value, "AppDetailDS")
> > > The "AppDetailDS" being the dataset name.
> > >
> > > I tryed modifying 2 different ways as follows:
> > > =Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
> > > =Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
> > >
> > > I cant seem to get the syntax correct when modifying the expression. I get
> > > errors when I try to preview.
> > >
> > > Also, I have tried using immediate if's(iif) and cant get that to work. I
> > > have no problem in a report with only one dataset.
> > >
> > > Any thoughts.
> > >
> > > "Charles Kangai" wrote:
> > >
> > > > Is this just for display or do you really want to convert to date for further
> > > > manipulation as a date?
> > > >
> > > > If it is only for display, use =format(Fields!MyDateField.Value,
> > > > "MMM-dd-yyyy") or something like it.
> > > >
> > > > If it is for further manipulation as a date, use
> > > > =CDate(Fields!MyDateField.Value)
> > > >
> > > > HTH
> > > >
> > > > Charles Kangai, MCT, MCDBA
> > > >
> > > >
> > > >
> > > > "Don" wrote:
> > > >
> > > > > I need to convert a datetime, coming from a database table, to a date. How
> > > > > do I do this within my report?
> > > > >
> > > > > --
> > > > > Don

Converting datetime in a string

Hi there,

Can anyone help?

I am trying to convert datetime into a string in the following format


2003-10-09 13:23:15.967



Current syntax:



SELECT '"' + CAST(t.Stamp AS varchar(30)) + '"' AS 'Start'

Any one any ideas?

THANKYOU!!!!

Melanie :)Try this:

select convert(varchar,getdate(),121)sqlsql

converting datetime for comparison

I am passing a datetime to a stored proc and I need to convert this
datetime to the format dd/mm/yyyy and then make a comparison between it
and a field in my select statement. Can somebody tell me what I am
doing wrong in my code?
declare @.Company varchar(50)
declare @.SerialNumber varchar(50)
declare @.ProductGroup varchar(10)
declare @.InstallationDate datetime
set @.Company = ''
set @.SerialNumber = ''
set @.ProductGroup = '9'
set @.InstallationDate = '21/03/2003 00:00:00'
select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
p.InstallationDate,
t.ProductTypeID, t.[Description], convert(datetime,
left(p.InstallationDate, 11), 103),
convert(datetime, left(@.InstallationDate, 11), 103)
from Products p
inner join companysite s
on p.OwnedByCompanyID = s.CompanySiteKey
inner join Competitors c
on c.CompetitorID = p.ProducedByID
inner join CompetitorMachines m
on p.MachineID = m.CompetitorMachineID
inner join ProductTypes t
on t.ProductTypeID = m.ProductTypeID
where s.[name] like '%' + @.Company + '%'
and p.SerialNumber like '%' + @.SerialNumber + '%'
and t.ProductTypeID like '%' + @.ProductGroup + '%'
and convert(char(11), left(p.InstallationDate, 11), 103) =
convert(char(11), left(@.InstallationDate, 11), 103)
*** Sent via Developersdex http://www.examnotes.net ***"Mike P" wrote ...

> Can somebody tell me what I am doing wrong in my code?
A guess only...but..

> declare @.InstallationDate datetime
^^ a datetime
> set @.Company = ''
> set @.SerialNumber = ''
> set @.ProductGroup = '9'
> set @.InstallationDate = '21/03/2003 00:00:00'

> convert(char(11), left(@.InstallationDate, 11), 103)
char(11)
Perhaps the different data types is the problem?
Something else you could try which might help you achieve the same result
would be using the DATEDIFF function, if you are trying to find matches for
the same day, just use DATEDIFF, specify days, and look for where it equals
0 (ie, no days difference)..
Hope this helps..
Rob|||I have some best practices for datetime here: http://www.karaszi.com/SQLServer/in...
o_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike P" <mike.parr@.gmail.com> wrote in message news:e3zsKC2nGHA.3348@.TK2MSFTNGP03.phx.gbl.
.
>I am passing a datetime to a stored proc and I need to convert this
> datetime to the format dd/mm/yyyy and then make a comparison between it
> and a field in my select statement. Can somebody tell me what I am
> doing wrong in my code?
> declare @.Company varchar(50)
> declare @.SerialNumber varchar(50)
> declare @.ProductGroup varchar(10)
> declare @.InstallationDate datetime
> set @.Company = ''
> set @.SerialNumber = ''
> set @.ProductGroup = '9'
> set @.InstallationDate = '21/03/2003 00:00:00'
> select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
> p.InstallationDate,
> t.ProductTypeID, t.[Description], convert(datetime,
> left(p.InstallationDate, 11), 103),
> convert(datetime, left(@.InstallationDate, 11), 103)
> from Products p
> inner join companysite s
> on p.OwnedByCompanyID = s.CompanySiteKey
> inner join Competitors c
> on c.CompetitorID = p.ProducedByID
> inner join CompetitorMachines m
> on p.MachineID = m.CompetitorMachineID
> inner join ProductTypes t
> on t.ProductTypeID = m.ProductTypeID
> where s.[name] like '%' + @.Company + '%'
> and p.SerialNumber like '%' + @.SerialNumber + '%'
> and t.ProductTypeID like '%' + @.ProductGroup + '%'
> and convert(char(11), left(p.InstallationDate, 11), 103) =
> convert(char(11), left(@.InstallationDate, 11), 103)
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>I am passing a datetime to a stored proc and I need to convert this
> datetime to the format dd/mm/yyyy and then make a comparison between it
> and a field in my select statement. Can somebody tell me what I am
> doing wrong in my code?
Well, what is the problem? Do you get an error message, or is it not
returning any rows? Could you give us DDL, sample data, and desired results
as per http://www.aspfaq.com/5006 ?

Converting dates.

Kudos to y'all!!! I have this task of fixing a database table which contains dates but in a VARCHAR type column. Now I wanted to convert them to 103 format. But the problem is, some values were inserted into the database in either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR. Is there an easy way of doing such task?no, there is no easy way

for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?|||no, there is no easy way

for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?

It's in either format. Some dates are in dd/mm/yyyy and some are in mm/dd/yyyy. It's an old table and I don't know for sure which date format was used for it.|||i think you missed the intent of my question :)

i was trying to point out that the answer to your question "Is there an easy way of doing such task?" is no, because there will always be these types of values that you just cannot decide|||What does this give you?

SELECT * FROM Table WHERE ISDATE(DateCol)=0

??

SELECT ISDATE('10/24/1960'), ISDATE('24/10/1960')|||hey brett, i got one for you in return

what do you get for this query --SELECT ISDATE('04/05/2006') as is1
, ISDATE('05/04/2006') as is2

mwua ha ha ha hahahaha !!! :) :) :) :) :)|||Good point, bottom line, you are hosed

unless you have a column that identifies the format|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)sounds like the attitude of a certain large software company which shall remain nameless...

:)|||sounds like the attitude of a certain large software company which shall remain nameless...

yea, they drill it into you, it takes a while to feel clean again. :)

did I say 9? I meant 6. even better!|||either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR.

How much rows are you having in your table..?

Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

Consider the points given below, remember you didn't provide enough information...

1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

3. Inform your higher authority & update your table, this way your new data will not be wrong.|||How much rows are you having in your table..?

Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

Consider the points given below, remember you didn't provide enough information...

1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

3. Inform your higher authority & update your table, this way your new data will not be wrong.

I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format. This means that the SQL syntax used within the application follows this format. Therefore, the dates are inserted in dd/mm/yyyy format. As I said, this table is old and the old application that uses this table inserts date in mm/dd/yyyy format. The old application was stupid 'coz it formats date depending on th system setting and inserting it into the table as is. My only mistake is that I should've fixed the table before I started the application. For one year now, the old and the current application is inerting date values into the table as VARCHAR instead of DATETIME. Now that I'm updating the application ('coz I've managed to create it not to be dependent on the system settings), I want to start inserting date values as DATETIME so that it would work on BETWEEN statements properly as well as using SQL Server's built in functions such as DATEDIFF, DATEADD, etc. as I'll be using SQL Server Agent to execute T-SQL commands which involves dates.|||I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format.
You have to take pain to replace the VARCHAR column to DATETIME column, choose the Server idle time and do it at single shot because you don't have any other option.

There are few ways to update your DATETIME columns...

1. You can create new table & copy all data from old table to new (using DTS).
2. Add new column in the existing table & update it (you can write query for it & after updating remove old column).
3. First update rows which is having 'day' more than 12, then update other rows.
4. Don't forget to check column references.

Note : You will get ambiguous / incorrect dates (which are below 12) because you will not identify dates between 1 to 12 (date or month).

By converting VARCHAR to DATETIME column you can eliminate future incorrect / ambiguous data. You have to take this risk, else I didn't find any other solution...|||Do you have a time stamp on your data that would indicate whether the date was entered under the old system or under the new system? If so, you can update the dates with two separate statements.

Converting Date String to Date

Hi there,

I am trying to convert a date string (YYYYMMDD) to a date (DDMMYY). Using the following formula, I have met with partial success, the day and the year pull through correctly for each entry but the month is always (01).

E.g. (20011031 = 31/01/2001) or (20011201 = 01/01/2001) or (20031208 = 08/01/2003)

My formula is as follows.

//Convert The Status Changed Date

NumberVar xy := tonumber(Left({AUT001.Date Status Last Changed},4));
NumberVar xd := Day(Datevalue (tonumber(Right({AUT001.Date Status Last Changed},2))+1));
NumberVar xm := Month(Datevalue (tonumber(Mid({AUT001.Date Status Last Changed},5,2))+1));

Date(xy,xm,xd)

I am using Crystal 10 and am totally stumped.

Any help would be very much appreciated, thanks in advance.Datevalue of a number ,say x, will return December (30+x), 1899.
So, according to ur formula if the month is 'May' then xm will contain January 5,1900(December (30+5+1),1899).Have added 1 additionally as u have added 1 in ur formula.
So when u take the month fn. it wil return January which is 1(as u have stated).
Hence datevalue of the number(for months at the max. can be 12) will return a date in January only and hence always u get month as January.

tried in crystal 8 and the following worked not sure about Crystal 10

numbervar xy := tonumber(left({@.stringdate},4));
numbervar xd := tonumber(right({@.stringdate},2));
numbervar xm := tonumber(Mid({@.stringdate},5,2));

Date(xy,xm,xd);|||Thank worked fine, thanks very much.

converting date format on for xml query

im trying to convert date format with this with no sucess
http://myserver/?sql=select%20*%20,CONVERT(datetime,DATA,3)
as%20[order!1!date]%20FROM%20NOTIFURBANA%20FOR%20XML%
20AUTO&root=root
it order by date, but dont convert the date format...any
help ?
What is the date formay you want to get? Note that all datetime values will
mapped to an ISO format. If you want your own format, CONVERT to a string in
your select statement.
Michael
"max" <anonymous@.discussions.microsoft.com> wrote in message
news:7df801c43160$6ca153b0$a001280a@.phx.gbl...
> im trying to convert date format with this with no sucess
> http://myserver/?sql=select%20*%20,CONVERT(datetime,DATA,3)
> as%20[order!1!date]%20FROM%20NOTIFURBANA%20FOR%20XML%
> 20AUTO&root=root
> it order by date, but dont convert the date format...any
> help ?
|||hi
thanks
im trying to get the datetime field that on my sql is on
this format
3/5/2004 14:30:18
but on xml it shows me this
2004-05-03T14:30:18
and i want it to return me
DD/MM/YY HH:MM:SS
how could it be done
max

>--Original Message--
>What is the date formay you want to get? Note that all
datetime values will
>mapped to an ISO format. If you want your own format,
CONVERT to a string in
>your select statement.
>Michael
>"max" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:7df801c43160$6ca153b0$a001280a@.phx.gbl...
sucess[vbcol=seagreen]
(datetime,DATA,3)
>
>.
>
|||its impossible or something ?
thanks
max
[vbcol=seagreen]
>--Original Message--
>hi
>thanks
>im trying to get the datetime field that on my sql is on
>this format
>3/5/2004 14:30:18
>but on xml it shows me this
>2004-05-03T14:30:18
>and i want it to return me
>DD/MM/YY HH:MM:SS
>how could it be done
>
>max
>
>datetime values will
>CONVERT to a string in
>message
>sucess
>(datetime,DATA,3)
format...any
>.
>
|||Here you are:
create table t (d datetime)
go
insert into t values ('3/5/2004 14:30:18')
go
select CONVERT(nvarchar(50), d, 101)+' '+CONVERT(nvarchar(50), d, 108) as d
from t for xml auto
HTH
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:7ada01c43164$b6cf3520$a401280a@.phx.gbl...[vbcol=seagreen]
> hi
> thanks
> im trying to get the datetime field that on my sql is on
> this format
> 3/5/2004 14:30:18
> but on xml it shows me this
> 2004-05-03T14:30:18
> and i want it to return me
> DD/MM/YY HH:MM:SS
> how could it be done
>
> max
>
> datetime values will
> CONVERT to a string in
> message
> sucess
> (datetime,DATA,3)
sqlsql

Converting Date

I am unable to convert following date format in seconds (ss). Plz provide me query for the same.
Date Available :
2007-03-27 09:55:00.000select convert(datetime, '2007-03-27 09:55:00.000')

??|||select convert(datetime, '2007-03-27 09:55:00.000')
Yes, and the seconds are extracted by:
select datepart(ss, convert(datetime, '2007-03-27 09:55:00.000'))|||even the convet part of

select datepart(ss, convert(datetime, '2007-03-27 09:55:00.000'))

can be avoided and used as

select datepart(ss,'2007-03-27 09:55:00.000')|||but select datepart(ss,'2007-03-27 09:55:00.000') yields 0

i think perhaps milind wanted the answer to be 3383974800, which is that datetime converted in seconds in SQL Server

note that in unix timestamp format, the value would be 1175003700|||Perhaps, yes. Then the query he asked would be:

SELECT 3383974800|||no, not really, because you don't get that answer without doing a conversion

for example, what is the answer for '2006-09-09 09:37'?|||'2006-09-09 09:37' is not one of the available dates listed in the OP. ;)

Seriously, I think we need Milind to clarify what s/he wants.|||'2006-09-09 09:37' is not one of the available dates listed in the OP. ;)

good one :cool:

converting Datatypes:

Hi All,

how do you convert from a date to an int ? as well as converting from Varchar to and Int

in SQL server 2000 ?

I am retrieving the GetDate() which i store in column as Varchar, i then want to use it within my select statement to calulate data which i want to return i.e

DECLARE @.DateValue AS VARCHAR(20)

SELECT @.DateValue = ApplicationSettingValue FROM ApplicationSettings WHERE ApplicationSettingKey = 'ProcessDate'

print convert(int,@.DateValue) - 5
print GetDate() - 35

SELECT ccy_code, NULL, xrate_date, sterling_xrate
FROM SylvanTrans.dbo.SIADHP_XRate_Hist
WHERE xrate_date >= (CONVERT(int, @.DateValue) - 35 )
ORDER BY xrate_date

now my as you can see in my WHERE CLAUSE i want to calculate what is returned using the GETDATE() stored in my @.DateValue Variable, but the conversion throws a syntax error:

Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'GetDate()' to a column of data type int.

what am idoing wrong? i know it is something simple, unless there is no conversion from varchar to int?, i also tried setting the datatype for my variable as datetime but i got the same sort of error with DATETIME replacing the VARCHAR in the error!!!

thanks

regards

Hi,

why don′t use use the associated function for dealing with datetime like DATEADD ? You can also add negative dateparts to a datetime like
DATEADD(dd,-35,SomeDate)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

thanks for your reply,

yes i did see that function, but i neet to have a seperate table which stores the GetDate() so that in my sotred procs

i can do a select on its location and do things like :

select * from tablename

where @.valueDate - 1 > 5

where @.valueDate has the getDate () from my Applicationsettings Table, if i use the DateAdd() i wont be able to do this you see...

|||I think I did not got your point. COuld you explain this in more detail ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Converting data types

I've inherited a monster database that needs updating. It has a field called Birthdays that has been a text field and I want to convert it to datetime. The data entry people have been sloppy from time to time and some of the values generate errors when you try and change datatypes. Is there a way to change the datatype and have the oddball values left blank without bombing out the conversion process?

Thanksheh. how big is the database?

if it's not that big, then you can simply store them in a temp table, and move the values over one by one in a cursor style fashion. If it's absolutely enormous, I'd actually create another table just to store the data, and the same cursor fasion. Just make sure you put them in transactions so you can rollback accordingly based on any errors generated.

Since it's a one shot deal, you can even do it out of the database, and just manipulate all the values via code, then do the alter so it doesn't cause any problems.

Do you know specifically what the bad dates are? If so, via code, you can convert the dates based on how the errors were inputted, and update those records. :-\

Converting Data Types

I need to be able to convert a numeric data type to a text data type.

I've tried the TO_CHAR and the DBCONVERT functions but I am returned an error stating that they are not recognized functions.

I'm using the Query Analyzer on a Windows 2000 SQL Server.

Any help would be appreciated.

TechRickYou can use the cast or convert functions.|||Thanks,

I was just reading up on the CAST function. However, I've not been able to make it work yet.

In correction to my original post, I need to take a MONEY data type and convert it to a TEXT data type. I hope this possible.

Thanks again,

TechRick|||Try the following from the pubs database and titles table:

SELECT price, cast(price AS varchar(30)) FROM titles|||You can also do it this way:

select cast(cast(price as varchar(30)) as text) from titles

But since the conversion is implicit between varchar and text you don't need to do this.|||Thanks again for the help. Your suggestion works great but when I try to update the data I get this:

Server: Msg 260, Level 16, State 1, Line 31
Disallowed implicit conversion from data type varchar to data type money, table..., column 'PRICE'. Use the CONVERT function to run this query.
Server: Msg 257, Level 16, State 1, Line 31
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.

I'll let you know once I get it worked out.

Best Regards,
TechRick|||The way the error is reading is that you are trying to put a varchar into a money column - is that correct ?|||Yes, I have a large list of items collected from various tables and there are prices associated with these items. A good majority of these items have no price (.0000) and I would like to exchange the .0000 price for a 'CALL' or something similar.

I could not update the records with a "text" substitute so I thought I would convert the data type to TEXT so I can plug in whatever I want.

Thanks again,
TechRick|||Why did you choose text over varchar ? Could you post your update statement as well as the definition of the table you are updating ? You have to explicitly convert varchar to money and vice-versa using either cast or convert. But I am a little confused about the first error you received - why are you trying to put a varchar into a money column ?|||You'll have to excuse me, I've only been working with SQL for less than 3 weeks. I've been learning as I go (with my Dummies and Sam's books and this forum). I've already written a few complex queries and as for this particular situation, this is the last obsticle I need to overcome to put this query to rest.

Based on the error I was getting I assumed that I was doing something wrong but I didn't have the time to completely research it. I'll pick it back up on Monday. I think I need to understand the convert and cast functions better before I can make use of them. I'll be working towards that end.

As for now, I'm away from work and don't have easy access to the code. I'll post it Monday after I tinker a little more.

Thanks for the help.

TechRick|||Ah, by "convert" you mean that you would like to change the data type of a column from money to varchar so that you can store a mix of data in a single column. The easy answer is to just change the data type of the column in Enterprise Manager. If SQL Server can find a reasonable way to preserve the data, it will. Thereafter you can store any character data in the column, e.g. "Call" or "Operators are standing by!".

The "correct" answer is rather different, and a valid subject for debate. If you do not have a price for an item, the correct representation in the database should be different from a free item. For example, you might use the value NULL to indicate "call for price" and $0.00 for a free item. Alternatively, it may make more sense in your application to have a separate means of flagging items for which you don't want to publish a price, qualify for free shipping, have quantity discounts, ... . More columns or tables may be needed.|||Agreed about the 'correct' answer. Fortunately and unfortunately, I didn't write the application so I'm working with what's there.

I managed to get it taken care of by adding the convert on my select statment.

SELECT UPPER(ITEM), DESCRIPT, Q_STK, QTY_RESERVE, CAST (SELLPRICE AS VARCHAR(15)),...

By doing it this way I am able to manipulate the data any way I want.
Add 'CALL', 'FREE', etc.

Thanks all for the help. One last item and this query is behind me.

Best Regards,
TechRick|||Originally posted by rnealejr
Try the following from the pubs database and titles table:

SELECT price, cast(price AS varchar(30)) FROM titles

rnealejr, I noticed as I was going back through all these posts that you were right on the money a long time ago! Thanks for the help. I think I was trying to perform an update on the column using the cast rather than taking the data in converted from the start. Anyhow, just wanted to thank you for your help. Too bad I had to learn the hard way.

Best Regards,
TechRick|||Thanks for the email and compliment as well as a good pun (right on the money) - I enjoyed that.

Good luck.|||Another handy tool for fudging return values within a query is throwing in a CASE, e.g.:

select Description, ServingSize, case when Price<>0.0 then Convert(VarChar,Price) else 'Call' end as 'AdvertisedPrice'
from PiecesParts where Fused=1

Note that there are two slightly different versions of CASE. One lets you test a single expression against multiple values, while the other lets you test multiple expressions.

You can swindle a lot of logic into a CASE or nested CASEs. For example, it could check for quantity price breaks or apply discounts based on data from other tables or variables. The result is just another (computed) column in the recordset returned from the query. (As such, it isn't writable.)

Converting data to UPPERCASE

Setup: SQL2000 Server running on Win2k Server
I can't find any difinitive answer to this. I need to convert the data in a
column to uppercase. Do I simply change the collation from the default of
SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can
I
do this while the database is being replicated?
Thanks in advance for your help.
John SteenNo, changing case sensitivity will no change the casing of the data.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:DBF3F536-9B8A-4426-B03D-C1647EDF170B@.microsoft.com...
> Setup: SQL2000 Server running on Win2k Server
> I can't find any difinitive answer to this. I need to convert the data in
a
> column to uppercase. Do I simply change the collation from the default of
> SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And ca
n I
> do this while the database is being replicated?
> Thanks in advance for your help.
> John Steen
>|||Why can't you just do an update?
UPDATE Table
SET col1 = upper(col1)|||John,
You have to update the column.
update t1
set c1 = upper(c1)
Changing the collation does not change the data.
AMB
"John Steen" wrote:

> Setup: SQL2000 Server running on Win2k Server
> I can't find any difinitive answer to this. I need to convert the data in
a
> column to uppercase. Do I simply change the collation from the default of
> SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And ca
n I
> do this while the database is being replicated?
> Thanks in advance for your help.
> John Steen
>|||Thanks, Alejandro. I was thinking that I not only had to change the
collation, but also reimport the data. This way makes more sense.
But back to the collation -- will changing it to
SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
Thanks,
John Steen
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> John,
> You have to update the column.
> update t1
> set c1 = upper(c1)
> Changing the collation does not change the data.
>
> AMB
> "John Steen" wrote:
>|||> But back to the collation -- will changing it to
> SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
No. Seems you misunderstand what collations are. Where did you learn that an
y type of collation will
force only uppercase data?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:A4B10FFD-8460-4E79-BFB9-4030E87EF618@.microsoft.com...[vbcol=seagreen]
> Thanks, Alejandro. I was thinking that I not only had to change the
> collation, but also reimport the data. This way makes more sense.
> But back to the collation -- will changing it to
> SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
> Thanks,
> John Steen
>
> "Alejandro Mesa" wrote:
>|||No that wopn't force anything, you could however do this thru a instead
of trigger or from the front-end
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I couldn't find a good explanation, so it was an inferrence on my part.
Thanks,
John Steen
"Tibor Karaszi" wrote:

> No. Seems you misunderstand what collations are. Where did you learn that
any type of collation will
> force only uppercase data?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:A4B10FFD-8460-4E79-BFB9-4030E87EF618@.microsoft.com...
>
>|||Thanks, Denis. I'll convert the current data and mention the trigger to our
developer.
John Steen
"SQL" wrote:

> No that wopn't force anything, you could however do this thru a instead
> of trigger or from the front-end
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>sqlsql

Converting data to UPPERCASE

Setup: SQL2000 Server running on Win2k Server
I can't find any difinitive answer to this. I need to convert the data in a
column to uppercase. Do I simply change the collation from the default of
SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can I
do this while the database is being replicated?
Thanks in advance for your help.
John Steen
No, changing case sensitivity will no change the casing of the data.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:DBF3F536-9B8A-4426-B03D-C1647EDF170B@.microsoft.com...
> Setup: SQL2000 Server running on Win2k Server
> I can't find any difinitive answer to this. I need to convert the data in a
> column to uppercase. Do I simply change the collation from the default of
> SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can I
> do this while the database is being replicated?
> Thanks in advance for your help.
> John Steen
>
|||Why can't you just do an update?
UPDATE Table
SET col1 = upper(col1)
|||John,
You have to update the column.
update t1
set c1 = upper(c1)
Changing the collation does not change the data.
AMB
"John Steen" wrote:

> Setup: SQL2000 Server running on Win2k Server
> I can't find any difinitive answer to this. I need to convert the data in a
> column to uppercase. Do I simply change the collation from the default of
> SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can I
> do this while the database is being replicated?
> Thanks in advance for your help.
> John Steen
>
|||Thanks, Alejandro. I was thinking that I not only had to change the
collation, but also reimport the data. This way makes more sense.
But back to the collation -- will changing it to
SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
Thanks,
John Steen
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> John,
> You have to update the column.
> update t1
> set c1 = upper(c1)
> Changing the collation does not change the data.
>
> AMB
> "John Steen" wrote:
|||> But back to the collation -- will changing it to
> SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
No. Seems you misunderstand what collations are. Where did you learn that any type of collation will
force only uppercase data?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:A4B10FFD-8460-4E79-BFB9-4030E87EF618@.microsoft.com...[vbcol=seagreen]
> Thanks, Alejandro. I was thinking that I not only had to change the
> collation, but also reimport the data. This way makes more sense.
> But back to the collation -- will changing it to
> SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
> Thanks,
> John Steen
>
> "Alejandro Mesa" wrote:
|||No that wopn't force anything, you could however do this thru a instead
of trigger or from the front-end
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||I couldn't find a good explanation, so it was an inferrence on my part.
Thanks,
John Steen
"Tibor Karaszi" wrote:

> No. Seems you misunderstand what collations are. Where did you learn that any type of collation will
> force only uppercase data?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:A4B10FFD-8460-4E79-BFB9-4030E87EF618@.microsoft.com...
>
>
|||Thanks, Denis. I'll convert the current data and mention the trigger to our
developer.
John Steen
"SQL" wrote:

> No that wopn't force anything, you could however do this thru a instead
> of trigger or from the front-end
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>