Consolidation Query: char

Showing posts with label char. Show all posts

Sunday, March 25, 2012

Conversion of Char to SmallDateTime

I have a column that has date information in it in the following
format: yyyymmdd I want to convert this information into and actual
SmallDateTime column so I can use it for comparison etc. Some of the
dates that are stored in this data are either blank or invalid dates
because of bad user input.
select
cast(left(inv_dt1,4) + '-' + right(left(inv_dt1,6),2)+'-'+
right(inv_dt1,2) as smalldatetime) as inv_dt
into newtable
from origtable
If I run that code, it errors out on the invalid fields. Is there a
way to tell SQL to just NULL the field if it is invalid in any way and
continue on?
Thanks,use the isdate function
create table WasabiTable (inv_dt1 varchar(23))
insert WasabiTable values ('ababababa')
insert WasabiTable values ('20060101')
insert WasabiTable values ('20060299')
insert WasabiTable values (NULL)
insert WasabiTable values ('20060401')
insert WasabiTable values ('20050331')
select
case
when isdate(inv_dt1) = 1 then convert(smalldatetime,inv_dt1)
else null
end onsetdate
from WasabiTable
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On 30 May 2006 12:10:08 -0700, "SQL Menace" <denis.gobo@.gmail.com>
wrote:

>use the isdate function
Just to add to that, be aware the ISDATE tests if the string will
convert to a valid DATETIME datatype. Valid dates for DATETIME are
from January 1, 1753 through December 31, 9999. Valid dates for
SMALLDATETIME are from January 1, 1900, through June 6, 2079. So,
while it may be unlikely, it is possible for a string to satisfy the
ISDATE test yet still fail the conversion to SMALLDATETIME.
Roy Harvey
Beacon Falls, CT

Thursday, March 22, 2012

CONVERSION FROM CHAR(4) TO DATETIME

i have another problem.and it's now on converting a char(4) to datetime
here is the situation
J_TIM < F_TIM

J_TIM is datetime while F_TIM is char of 4

example

J_TIM = 20:30
F_TIM = 2030

how can i convert F_TIM to datetime so that i can compare them.
?

thanksYou can try something like:

select *
from sometable
where convert(datetime, '20060101 ' + J_TIM) <
convert(datetime,'20060101 ' + substring(F_TIM,1,2) + ':' +
substring(F_TIM,3,2))

paul_zaoldyeck wrote:

Quote:

Originally Posted by

i have another problem.and it's now on converting a char(4) to datetime
here is the situation
J_TIM < F_TIM
>
J_TIM is datetime while F_TIM is char of 4
>
example
>
J_TIM = 20:30
F_TIM = 2030
>
how can i convert F_TIM to datetime so that i can compare them.
?
>
thanks

|||On 22 Nov 2006 22:46:19 -0800, paul_zaoldyeck wrote:

Quote:

Originally Posted by

>i have another problem.and it's now on converting a char(4) to datetime
>here is the situation
>J_TIM < F_TIM
>
>J_TIM is datetime while F_TIM is char of 4
>
>example
>
>J_TIM = 20:30
>F_TIM = 2030
>
>how can i convert F_TIM to datetime so that i can compare them.
>?
>
>thanks

Hi Paul,

DECLARE @.F_TIM char(4);
SET @.F_TIM = '2030';
SELECT STUFF(@.F_TIM, 3, 0, ':');
SELECT CAST(STUFF(@.F_TIM, 3, 0, ':') AS datetime);

Note that the reply posted by othellomy will work, but won't enable you
to efficiently use an index on the J_TIM column (in case there is one).

Also read Tibor's ultimate guide to the datetime datatype. You'll find
it at http://www.karaszi.com/SQLServer/info_datetime.asp
--
Hugo Kornelis, SQL Server MVP|||thanks for the codes...they all worked out...
regards!!!
Hugo Kornelis wrote:

Quote:

Originally Posted by

On 22 Nov 2006 22:46:19 -0800, paul_zaoldyeck wrote:
>

Quote:

Originally Posted by

i have another problem.and it's now on converting a char(4) to datetime
here is the situation
J_TIM < F_TIM

J_TIM is datetime while F_TIM is char of 4

example

J_TIM = 20:30
F_TIM = 2030

how can i convert F_TIM to datetime so that i can compare them.
?

thanks

>
Hi Paul,
>
DECLARE @.F_TIM char(4);
SET @.F_TIM = '2030';
SELECT STUFF(@.F_TIM, 3, 0, ':');
SELECT CAST(STUFF(@.F_TIM, 3, 0, ':') AS datetime);
>
Note that the reply posted by othellomy will work, but won't enable you
to efficiently use an index on the J_TIM column (in case there is one).
>
Also read Tibor's ultimate guide to the datetime datatype. You'll find
it at http://www.karaszi.com/SQLServer/info_datetime.asp
>
--
Hugo Kornelis, SQL Server MVP

Conversion from char to Nchar

Hello,

I am trying to convert a single code page MS Server database into a unicode database, using the unicode data types,NCHAR, NVARCHAR, NTEXT. The problem is that in the original database, indexes and constraints have been defined on the tables whose configurations need to be changed. As a result, the ALTER TABLE command fails. Are there any other alternative solutions?
Also, data from the old database needs to be preserved. The objective is to create a unicode database which keeps the old data intact as well as accepts the new data in unicode.
It would be great if you could help!
Thanks,
Sheetal.If you have enough disk space, I strongly recommend:

1) Use SQL Enterprise Manager to script your old database
2) Edit the script to change CHAR to NCHAR
3) Edit the script to change VARCHAR to NVARCHAR
4) Edit the script to change TEXT to NTEXT
5) Create a new database
6) Play the script into the new database using SQL Query Analyzer
7) Copy the data from the old database to the new one

The down side is that this new database can take about 2.5 times as much disk space as your old database, so you have to have quite a bit of space free to make this happen.

There are other ways to do this conversion, but they are a lot more complicated. If you have the disk space, this is a much simpler way to do the conversion.

-PatP|||Hello there,

Thanks very much for your speedy reply!
Excuse me if i sound like a complete beginner, but I am not really experienced with MS Server, as a result, I'm not aware of whether my approach to scripting the database is correct or not. Is it, right click on the existing database -> All new tasks -> Generate SQL Script , and if so,then , General->Show All ;Options->All checkboxes selected??
When the script is ready, i try to execute it on the 'master' DB, after renaming the existing database.
But I get errors and it doesn't execute saying it that it doesn't recognise the user defined data types and roles (while giving their names)
How can I take care of this?
Lastly, after changing the concerned fields, i.e, char to Nchar, varchar to Nvarchar and text to Ntext, which tool is used to transfer the data from the old DB to the new empty one?

Thanks again!
Sheetal.|||That's Ok, everybody has to start somewhere!

First, create a new database. If you prefer working in a GUI environment, you can do this using SQL Enterprise Manager.

Next, open SQL Query Analyzer. Connect to the database, then open the edited script. Click on the "play" button in the toolbar, or just hit Ctrl-E to execute. The script should run in the new database with no error messages.

The simplest way to move the data is probably to use the DTS Wizard. You can get to it by right clicking the Data Transformation Service in SQL Enterprise Manager.

-PatP|||Hello Pat :-)

Many thanks again for your speedy (and warm) reply!
I tried using the SQL Enterprise Manager to create a new database, and then play into it the edited version of the old DB Script, but it gives me really wierd errors everytime, like a certain table or type doesn't exist(though it does exist in the original DB), and there's no way I can find out what's going on with the automated script generation. Any tips?
If not, I wrote a piece of code which works perfectly in converting Char to Nchar :
ALTER TABLE t_nm_reports
DROP CONSTRAINT UQ__t_nm_reports__5D60DB10

ALTER TABLE t_nm_reports
ALTER COLUMN nm_dw_name NCHAR(50) /*the column which needs to be changed*/

ALTER TABLE t_nm_reports
ADD CONSTRAINT UQ__t_nm_reports__5D60DB10
UNIQUE (nm_dw_name);

But, this is a very rough and basic way of solving the problem, done manually for each concerned table. I'm looking for a piece of code which can search for the concerned tables and perform the query, all in one program. Is that possible?
Thank you for your time!

Sheetal.|||I'm going to be really, really detailed about this. Please don't be offended, I'm trying to cover everything, not insult anyone!

1) Launch SQL Enterprise Manager
2) Navigate the tree to the server of interest
3) Double click the server to connect and open
4) Double click the Databases collection to open it
5) Right click the source database
6) Click on All Tasks | Generate SQL Script...
7) Click on the Show All button in the upper right corner
8) Click in the Script all objects checkbox
9) Click the Options tab
10) In the security section:
11) Click the checkbox for Script database users and database roles
12) Click the checkbox for Script object-level permissions
13) In the Table Scripting section:
14) Click all four checkboxes
15) Click the Ok button
16) Make the appropriate choices for saving the file

This should get you a script that includes everything, in the correct order to rebuild the schema from scratch. You should be able to edit this script to change CHAR to NCHAR and VARCHAR to NVARCHAR without any problems (or at least I can't think of any).

While you can hunt down all of the "problem child" columns and fix them as you did in your example, it is a lot more work and I'm not completely comfortable that you'll get what you really want, especially from a performance standpoint.

-PatP|||Hello there,
I'm sorry but it just doesn't seem to work :-(
Everytime I try to execute the edited script in the query analyzer(against the new empty database or the source database), I get errors like a particular table/sp doesn't exist (or incorrect syntax), even though it does exist before the execution, but seems to get dropped/deleted during the execution from the source database.
Thanks for ur time, any other suggestions would be highly appreciated!

Sheetal.|||If you have foreign key definitions (look for the keyword FOREIGN to find them), you will want to move them to the end of the script. This is due to the fact that the scripting engine doesn't always respect the "dependance sequence" of the tables, so the tables aren't always created in the same order that they were originally created.

-PatP|||You may also just be able to run your script twice, ignoring any errors that state that a particular object already exists.|||You may also just be able to run your script twice, ignoring any errors that state that a particular object already exists.As long as you skip the DROPs after the first run!

-PatP|||So, the scripting was completed successfully,with everything done as specified except that in the Options tab, the MS-DOS(OEM) format was selected, and not the Windows ANSI format.It does give some errors because of dependent objects, which when moved before the creation of the calling procedure, works fine. But it can be a hassle if they are many in number(as in my case). Any workarounds this problem??

Also, an important question for me is to know how to find and replace a certain string, eg changing char to nchar while using the query analyzer's "Replace", passes thru every string named 'varchar' or 'character' as well, so its very time consuming.I've tried using '(space)char', but its not foolproof either. Is there any way I can search for regular expressions automatically, as doin it manually in a huge database script is not very practical. Like for eg, creating a .bat file and using FINDSTR? if I'm on the right track, please guide me further!
Thanks!
Sheetal.|||Let's try a different approach...

Modify the directions for step #14 to exclude the last check box for Primary, Foreign, and Check constraints. Build the script that way (without the constraints).

Build a second script with only the constraints.

1) Launch SQL Enterprise Manager
2) Navigate the tree to the server of interest
3) Double click the server to connect and open
4) Double click the Databases collection to open it
5) Right click the source database
6) Click on All Tasks | Generate SQL Script...
7) Click on the Show All button in the upper right corner
8) Click in the Script all tables checkbox
9) Click the Formatting tab
10) Clear all of the check boxes
11) Click the Options tab
12) In the Table Scripting section:
13) Click only the PRIMARY keys, FOREIGN KEYS, and check constraints checkbox
14) Click the Ok button
15) Make the appropriate choices for saving the file

Now you should be able to play the first script, then play the second script without running into the dependancy problems you've been having.

In terms of better editing tools, I'd use an editor that recognizes regular expressions (Elvis is free, there are lots of others), or a tool like Perl that was made for those kinds of tasks.

-PatP

conversion from CHAR to DATETIME error

on a column DateNew = DateTime

i am trying :
INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')

and i get an error :
conversion of char data type to datetime data type resulted in an out of range datetime value

I had never this error before , do you know why ?
i must enter a yyyy/mm/dd format because this database will be used for Fr and Us langages

thank you for helpingIm not getting any error with ur insert statement.I think u are passing date value as a variable which is in char.

try trim that variable on both side using ltrim,rtrim before inserting.|||lookup SET DATEFORMAT in Books online. Maybe that would help.|||You can try this -
insert <tablename>
select convert(datetime,'2003/01/31 10:04:14')

Well, for more info check this out...
http://groups.google.co.in/group/microsoft.public.sqlserver.programming/browse_frm/thread/ada70fc46e7005ac/419fae5346ac4bc0?lnk=st&q=dateformat()+in+ms+sql+server+200&rnum=3&hl=en#419fae5346ac4bc0|||try this instead --

INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003-01-31 10:04:14')|||INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')

insert <tablename>
select convert(datetime,'2003/01/31 10:04:14')

or this one

try this instead --

INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003-01-31 10:04:14')
__________________

These statements all are working fine in my machine also,so no problem in statements...I think mailler made a point ..plz check that.
Joydeep|||i got it with
INSERT INTO [dbo].[Users] (DateNew) VALUES (convert(datetime,'2003/01/31 10:04:14',111))

thank you|||i got it with convert(datetime,'2003/01/31 10:04:14',111)

thank you|||I know that I'm being pendantic here, but I'd invest a bit of time now into making your application much more portable/flexible/etc. The ISO 8601 (http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html) format for date/time information is CCYY-MM-DD HH:MM:SS.TTT and that format is used by virtually the entire computing universe. It has been adopted by W3C (http://www.w3.org/TR/NOTE-datetime) which means that almost anywhere you find time on the Internet, you'll find it in this format.

You'll almost certainly save yourself lots of time and energy if you switch to using this format now, instead of having to switch to it later!

As a side note, if you expect your application to grow to the point where you may need to support more than one server, I'd suggest you spend the time to convert your application to use UCT (aka GMT) now too... This is easy to do up front, and almost impossible to do "after the fact" due to many very difficult problems caused by different locales.

-PatP|||...due to many very difficult problems caused by different locales...
PatP
You mean time zones, right?|||You mean time zones, right?You can think of the problem that way, but it is really more complex than just time zone... A locale rolls the problem up into a nice tidy (but not simple bundle). Time zones reflect a difference between local time and UCT, essentially a time offset. The problem comes from Daylight Savings Time, where different locales observe different shift dates, not all of which adjust by the same amount (some only move 30 minutes).

Unravelling the mess is easy if done while recording an event because it is easy for a computer to find UCT from its local time if necessary. Once the time is stored, there may not be any way to recover true UCT again. This gets really hard to explain, but there have been a couple of good whitepapers done on the problem.

-PatP|||Thanks a lot I shall do it at once but HOW do you convert a normal date into ISO 8601 ?

what is the SQL command for it ?|||for the moment I store all my dates time inthe format
yyyy/MM/dd hh:mm:ss

2000/12/31 18:50:06
I have just to replace / by - ?

thanks a lot|||for the moment I store all my dates time inthe format
yyyy/MM/dd hh:mm:ss

2000/12/31 18:50:06
I have just to replace / by - ?

thanks a lotYes! Exactly.

This is a relatively small change "up front", but it makes your date/time format match the format used by nearly everything else. That makes your code much easier to port to other programming languages, databases, etc at a later time. It is a small investment up front, that can pay off hugely in the future.

As a side note, SQL Server stores the data internally in a completely different form... Once you get the data into a column or variable, the work has been done. The only place you need to change anything is in the actual conversion from a character representation to a DATETIME.

-PatP|||Pat I did it , I have inserted 100 rows in my SQL database
in the good format but the database seems change it for the french format
31/12/2005 18:20:45

Once you get the data into a column or variable
I store the date in a datetime format column ?!

thanks a lot

and for searching any row in my database where a date
>
<
=
<>
=<
<=
to another date but on the date not on the datetime (yyyy-MM-dd) ?|||What is actually happening is that the database stores the value internally as a bunch of bits... They don't look like anything to the average human eye, and are logically close to a pair of integer counters. When your application retrieves the DATETIME value, the client component of the software converts those bits to a human readable form based on the locale that I mentioned in an earlier post, and the rules for that conversion happen to make the converted text appear "French" on your machine.

There are a number of ways to search for dates within a range (such as entered at any time on a given date). I prefer to do this by finding the minimum value (the very start of the day, at midnight) and the maximum value (or just past the maximum value if that is easier), then finding values between the minimum and maximum that I've selected. So for instance to find values that happened on Saint Valentine's Day 2006, I'd use:SELECT *
FROM myTable
WHERE '2006-02-14' <= myDate -- Note "equals"
AND myDate < '2006-02-15' -- Note no "equals"Using this logic is a bit strange at first, but it allows the database to use indicies to find dates of interested quickly and easily. That makes it possible to pick the rows for one day out of ten years worth of data in seconds instead of hours!

-PatP|||you helped me a lot Pat !!! in a few answers more than a few weeks looking everywhere, thanks a TON

a last question !!

I am using now

WHERE CONVERT(CHAR(10), myDate, 120) = CONVERT(CHAR(10), myDateValue, 120)

it is very easy with server side language to generate it, but for the database on millions of rows (the application will be very big) is it faster or slower than your method ?

WHERE '2006-02-14' <= myDate AND myDate < '2006-02-15'

because with your method I must add a day to the normal date and it is more complicated for server side programming

thanks again for helping|||I am using now

WHERE CONVERT(CHAR(10), myDate, 120) = CONVERT(CHAR(10), myDateValue, 120)

it is very easy with server side language to generate it, but for the database on millions of rows (the application will be very big) is it faster or slower than your method ?slower, much slower

first of all, you don't have to convert a datetime value such as '2006-02-15' to datetime, as you do on the right side of that condition, because the database will treat it that way (as a datetime value) by default

however, if you convert your table column to a string, as you do on the left side of that condition, then the database cannot use the index, if any, on that column, and will do a table scan

in other words, performing a function on a column means that the condition is not sargable (http://netknowledgenow.com:81/CS/blogs/onmaterialize/archive/2006/01/11/65.aspx) (this link is not working today but it was fine yesterday, it's a really good explanation -- you can also do a quick search to find other articles which also explain that word)|||then i must absolutly keep this only way ? :

SELECT FROM myTable
WHERE '2006-02-14' <= myDate AND myDate < '2006-02-15'

but in my database datetimes are stored in that was yyyy/mm/dd hh:mm:ss
and for the moment i couldnt get any row comparing yyyy/mm/dd hh:mm:ss to yyyy/mm/dd

of course the column is a datetime datatype|||then i must absolutly keep this only way ? :

SELECT FROM myTable
WHERE '2006-02-14' <= myDate AND myDate < '2006-02-15'that is the only way to achieve good performance (except you need to change the first operand from <= to >=)

but in my database datetimes are stored in that was yyyy/mm/dd hh:mm:ssno, actually, they are not stored that way -- datetimes are stored as two integers|||and is it better to use a datetime columns or a smalldatetime
all my dates are starting after 2000 ?

thank you|||that depends on whether you need precision in the time|||smalldatetime : Date and time data from January 1, 1900, through June 6, 2079,
with an accuracy of one minute

datetime :Date and time data from January 1, 1753, through December 31, 9999,
with an accuracy of 3.33 milliseconds

if i dont need (who needs ?) a precision of 1 minutes is it better for performances on millions of rows to use smalldatetime ?|||now I get all int that way and it seems to work :

-----------

>= 2006-02-10

SELECT FROM Users
WHERE (DateColumn > '2006-02-11')

-----------

< 2006-02-10

SELECT FROM Users
WHERE (DateColumn < '2006-02-10')
-----------
<= 2006-02-10

SELECT FROM Users
WHERE (DateColumn < '2006-02-11')
-----------

= 2006-02-10
SELECT FROM Users
WHERE
(DateColumn >= '2006-02-10')
AND
(DateColumn < '2006-02-11')

-----------

<> 2006-02-10

SELECT FROM Users
WHERE
(DateColumn > '2006-02-11')
OR
(DateColumn <= '2006-02-10')
-----------

>= 2006-02-15

SELECT FROM Users
WHERE (DateColumn < '2006-02-15')|||if i dont need (who needs ?) a precision of 1 minutes is it better for performances on millions of rows to use smalldatetime ?Yes, a SMALLDATETIME will perform better than a DATETIME for many reasons. Maybe looking at things from the machine's perspective will help (and maybe that will just confuse issues even more):DECLARE @.d DATETIME, @.s SMALLDATETIME

SELECT @.d = GetUTCDate()
SELECT @.s = @.d

SELECT @.d, Convert(VARBINARY(20), @.d)
SELECT @.s, Convert(VARBINARY(20), @.s)

SELECT @.d = DateAdd(minute, 1, @.d)
SELECT @.s = @.d

SELECT @.d, Convert(VARBINARY(20), @.d)
SELECT @.s, Convert(VARBINARY(20), @.s)-PatP|||thanks again a lot Pat that was really usefull, a deep help

thank you to everybody|||2006-02-16 03:10:53.967 | 0x0000976A00346E9E

2006-02-16 03:11:00 | 0x976A00BF

2006-02-16 03:11:53.967 | 0x0000976A0034B4EE

2006-02-16 03:11:53.967 | 0x0000976A0034B4EE

2006-02-16 03:12:00 | 0x976A00C0

here is the result of your Query
not easy to read and understand|||hard to understand?

these numbers -- 0x0000976A00346E9E, 0x976A00BF -- show you exactly how datetime values are stored internally in sql server

:)|||not easy to read and understandThe results show a couple of the issues that I was trying to explain, in a concrete form (so we don't have to talk abstractly, but can deal with real values. Please bear with me, this explanation is long, but I think it will help.

The first two results show the difference between a DATETIME (as displayed in character form) and how that DATETIME value converts to both raw binary (on the same line), and how it converts to a SMALLDATETIME (which appears one line down), and also to the SMALLDATETIME expressed as raw binary. All of the values are different, but they represent the same moment in time in different ways!

A DATETIME is accurate to +/- 3 milliseconds (I know the docs say 3.33, but that is a case where the doc writers took some liberty with what is actually stored... There's no such thing as a third of a bit). It is actually stored as a bunch of bits that mean very little to the untrained eye, except for one minor thing I'll get to later.

A SMALLDATETIME is accurate to +/- 1 minute. When you assign a DATETIME to a SMALLDATETIME, rounding takes place to the nearest whole minute. The same time is represented in a slightly less acurate form. The binary value is also quite a bit smaller, and radically different.

As an interesting side note (of little practical value), note that the value 976A appears in both of the binary strings (although in different places). This is not an accident. It has to do with how the date values are actually stored.

Another more interesting note is that the binary values of a DATETIME and the corresponding SMALLDATETIME are not directly comparable. If you take the time to understand the details, you can work around this, but it is of very little use except as an academic exercise.

Things become a bit more interesting when we add a minute to the original DATETIME value. The character form makes it easy to see this addition, and it makes perfect sense.

When we convert the changed DATETIME value to a SMALLDATETIME, the same rounding takes place, and the binary values are still quite different from each other.

The interesting part comes when you compare the binary values of the DATETIME before and after adding a minute, and the binary values of the SMALLDATETIME before and after adding a minute. The important part to notice is that the binary values of the later values have larger binary values too! There is a direct, one to one relationship between the time and the corresponding binary value.

This is why Rudy pointed out earlier that computing the minimum and maximum values of interest was much more efficient than converting the date values to character form and comparing them... You can convert a DATETIME into many different character forms, most of which are not usable for range computations like this, so in order to search for a character form SQL Server has to query every possible row. SQL Server understands dates as either DATETIME or SMALLDATETIME values, and knows how to search an index for values in a specific range. This means that it can "ride the index" to only the rows of interest in your range, and it knows exactly when it has reached the end of that range. For large sets of data, this is MANY times more efficient!

-PatP|||wow ! perfect !
I never like to apply something without understanding it
now I get a better idea of the way SQL is working with dates

and I think that generally i need only smalll
datetime datatype, i had never used it before

thanks a lot once more !|||One more thing to throw out, just so you don't get surprised... The SMALLDATETIME datatype allows entry of temporal (time based) values from 1900-01-01 through 2079-06-06 23:39. This is fine for many purposes (I won't be alive to deal with any problems it might cause when it runs out, and I sincerely doubt that SQL Server will still be in use (at least in its present form) 70 years from now! However, many contracts (like Japanese mortgages) already extend well past that limit, so I usually use DATETIME even though a SMALLDATETIME would do.

I'm a lazy bum... If I can code/create something once then safely forget about it, I'll almost always do that instead of something a bit simpler that will work for a while, but might be a problem for me (or my successor) in the future. I won't do a lot of work to avoid a potential problem, but I'll do easy things to avoid getting a call at 03:00 wondering why a job failed and how soon can I get it fixed!

-PatP|||Pat your code works fine for me in SQL 2005, but a customer with SQL 2000 get errors again everywhere on dates, i have started a new thread here >> http://dbforums.com/showthread.php?t=1212861

i am really lost with dates .. and i dont know what to do

thanks again

Conversion ERROR

This is the error message I get: :(
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is the query:
Select Qual_ins.CompanyCode, Qual_ins.ParticipantCode, Qual_ins.Ins_Code, Qual_ins.Plan_Code,
dbo.PremiumRate(Qual_Ins.Crit,Qual_Ins.PQB_Spec,Pl an_Mas.Extend_Fee,
Qual_Ins.Adjpremium,Qual_Ins.Adjpremiumper,Qual_In s.Adjpremend,
GetDate(),Qual_Ins.Cover_Amt, Plan_Mas.CR_A, Plan_Mas.CR_B,
Plan_Mas.CR_C, Plan_Mas.CR_D, Plan_Mas.CR_E, Plan_Mas.CR_F,
Plan_Mas.CR_G, Plan_Mas.CR_H, Plan_Mas.CR_I, Plan_Mas.CR_J,
Plan_Mas.CR_K, Plan_Mas.CR_L, Plan_Mas.CR_M, Plan_Mas.CR_N,
Plan_Mas.CR_O) AS PremiumRate
FROM Qual_ins, Plan_Mas
WHERE Qual_Ins.CompanyCode = 'ACME'
AND Qual_ins.ParticipantCode = 4
AND Plan_Mas.CompanyCode = Qual_ins.CompanyCode
AND Plan_Mas.Ins_Code = Qual_ins.Ins_Code
AND Plan_Mas.Plan_Code = Qual_ins.Plan_Code
Order BY Qual_ins.Ins_Code

Please let me know if you need to see my PremiumRate (User Defined Function) in order to help me elimate this error message.
Any help is appreciate!
I'm new to this... "Hello" to all!

ShuviBased on the error message, I'd guess that you are trying to convert a string (CHAR or VARCHAR) to a DATETIME or a SMALLDATETIME. If that is the case, then one or more rows in your data isn't a valid date string.

-PatP

conversion char/nchar

Hi,
We are using sql server 2000..We intend to change the
char/varchar columns to nchar/nvarchat.Our application
contains lot's of dynamic tables.Hence there are no
standard no of tables and indexes in all sites..Can any
one help me out in writing scripts which queries the
dictionary objects and gives scripts which would work fine
in all sites...?
Thanks in advance
SridharThis script should get you started:
use tempdb
GO
CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar(10), thircol =char(10))
go
SELECT 'ALTER TABLE ' + table_name +
' ALTER COLUMN ' + COLUMN_NAME +
CASE WHEN DATA_TYPE =3D 'char' THEN ' nchar ' ELSE ' nvarchar ' =END + ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
char(13) + char(10) + 'GO'
FROM information_schema.columns WHERE DATA_TYPE IN ('varchar', 'char')
--now execute the statements that are returned from the select statement
go
DROP TABLE foo
-- Keith
<anonymous@.discussions.microsoft.com> wrote in message =news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> Hi,
> > We are using sql server 2000..We intend to change the > char/varchar columns to nchar/nvarchat.Our application > contains lot's of dynamic tables.Hence there are no > standard no of tables and indexes in all sites..Can any > one help me out in writing scripts which queries the > dictionary objects and gives scripts which would work fine > in all sites...?
> > > Thanks in advance
> > Sridhar
>|||Hi,
Thanks..do some where sql server stores the index and
constraints structure some where in dictionary ...other
wise how do i recreate the indexes and constraints after
converting to nchar
Sridhar
>--Original Message--
>This script should get you started:
>use tempdb
>GO
>CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
(10), thircol char(10))
>go
>
>SELECT 'ALTER TABLE ' + table_name +
> ' ALTER COLUMN ' + COLUMN_NAME +
> CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
nvarchar ' END +
> ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> char(13) + char(10) + 'GO'
>FROM information_schema.columns WHERE DATA_TYPE IN
('varchar', 'char')
>--now execute the statements that are returned from the
select statement
>go
>DROP TABLE foo
>
>
>--
>Keith
>
><anonymous@.discussions.microsoft.com> wrote in message
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
>> Hi,
>> We are using sql server 2000..We intend to change the
>> char/varchar columns to nchar/nvarchat.Our application
>> contains lot's of dynamic tables.Hence there are no
>> standard no of tables and indexes in all sites..Can any
>> one help me out in writing scripts which queries the
>> dictionary objects and gives scripts which would work
fine
>> in all sites...?
>>
>> Thanks in advance
>> Sridhar
>.
>|||SQL Server stores this information in system tables, like sysindexes, syscomments etc. You can read off of
these and use that information to re-generate the statements needed to re-create your stuff. Or script the
stuff: http://www.karaszi.com/sqlserver/info_generate_script.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:898401c432a5$31453bb0$a601280a@.phx.gbl...
> Hi,
> Thanks..do some where sql server stores the index and
> constraints structure some where in dictionary ...other
> wise how do i recreate the indexes and constraints after
> converting to nchar
> Sridhar
> >--Original Message--
> >This script should get you started:
> >
> >use tempdb
> >GO
> >CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
> (10), thircol char(10))
> >go
> >
> >
> >SELECT 'ALTER TABLE ' + table_name +
> > ' ALTER COLUMN ' + COLUMN_NAME +
> > CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
> nvarchar ' END +
> > ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> > char(13) + char(10) + 'GO'
> >FROM information_schema.columns WHERE DATA_TYPE IN
> ('varchar', 'char')
> >
> >--now execute the statements that are returned from the
> select statement
> >
> >go
> >DROP TABLE foo
> >
> >
> >
> >
> >--
> >Keith
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> >> Hi,
> >>
> >> We are using sql server 2000..We intend to change the
> >> char/varchar columns to nchar/nvarchat.Our application
> >> contains lot's of dynamic tables.Hence there are no
> >> standard no of tables and indexes in all sites..Can any
> >> one help me out in writing scripts which queries the
> >> dictionary objects and gives scripts which would work
> fine
> >> in all sites...?
> >>
> >>
> >> Thanks in advance
> >>
> >> Sridhar
> >>
> >.
> >

conversion char/nchar

Tuesday, March 20, 2012

Conversion

Hi,
I am in the process writing scripts for CHAR/VARCHAR to
NCHAR/NVARCHAR conversion..Infact i have completed
it..When I run these scripts against DB is verly
slow..This is happening when i run convert scripts
eaxctly..Even select name from sysobjects doesn't return
values..There are heavy IO is going on the DB..what are
the areas i should concentrate the to improve performance..
Basically I have written cursor whic executes one by one
the alter scripts..
Sridhar...Can you tell us how you are performing the upgrade i.e.
Have a different database or are you performing an alter
column ?
Thanks
Peter
>--Original Message--
>Hi,
> I am in the process writing scripts for CHAR/VARCHAR
to
>NCHAR/NVARCHAR conversion..Infact i have completed
>it..When I run these scripts against DB is verly
>slow..This is happening when i run convert scripts
>eaxctly..Even select name from sysobjects doesn't return
>values..There are heavy IO is going on the DB..what are
>the areas i should concentrate the to improve
performance..
>
>Basically I have written cursor whic executes one by one
>the alter scripts..
>
>Sridhar...
>.
>|||By alter columns...
>--Original Message--
>Can you tell us how you are performing the upgrade i.e.
>Have a different database or are you performing an alter
>column ?
>Thanks
>Peter
>>--Original Message--
>>Hi,
>> I am in the process writing scripts for CHAR/VARCHAR
>to
>>NCHAR/NVARCHAR conversion..Infact i have completed
>>it..When I run these scripts against DB is verly
>>slow..This is happening when i run convert scripts
>>eaxctly..Even select name from sysobjects doesn't return
>>values..There are heavy IO is going on the DB..what are
>>the areas i should concentrate the to improve
>performance..
>>
>>Basically I have written cursor whic executes one by one
>>the alter scripts..
>>
>>Sridhar...
>>.
>.
>|||Changing a CHAR to VARCHAR will most likely involve each row being affected
and hence all the I/O. If you are altering more than one column per table
you might get better performance by creating a second table with the changed
columns and Inserting all the rows into it. Drop the original, rename the
new to the old and add the appropriate indexes, RI etc.. Just make sure you
have good backups first.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:297601c47e07$62bd4ae0$a501280a@.phx.gbl...
> Hi,
> I am in the process writing scripts for CHAR/VARCHAR to
> NCHAR/NVARCHAR conversion..Infact i have completed
> it..When I run these scripts against DB is verly
> slow..This is happening when i run convert scripts
> eaxctly..Even select name from sysobjects doesn't return
> values..There are heavy IO is going on the DB..what are
> the areas i should concentrate the to improve performance..
>
> Basically I have written cursor whic executes one by one
> the alter scripts..
>
> Sridhar...
>|||Consider what Andrew has said, but also regardless of
which way you are doing it take off all indexes before you
start.
How many rows of data are you converting ?
>--Original Message--
>By alter columns...
>>--Original Message--
>>Can you tell us how you are performing the upgrade i.e.
>>Have a different database or are you performing an alter
>>column ?
>>Thanks
>>Peter
>>--Original Message--
>>Hi,
>> I am in the process writing scripts for CHAR/VARCHAR
>>to
>>NCHAR/NVARCHAR conversion..Infact i have completed
>>it..When I run these scripts against DB is verly
>>slow..This is happening when i run convert scripts
>>eaxctly..Even select name from sysobjects doesn't
return
>>values..There are heavy IO is going on the DB..what are
>>the areas i should concentrate the to improve
>>performance..
>>
>>Basically I have written cursor whic executes one by
one
>>the alter scripts..
>>
>>Sridhar...
>>.
>>.
>.
>

Sunday, February 19, 2012

contact 2 column

Hi I have 2 coloumn one hast char(Productname) type another has int type (CustomerID)I want to contact them in my stored procedure like:
select Productname+CustomerID az newID
but I have error becouse one of them are int another in char ddo you have any idea?
I suggest that you read about "Datatype Precedence" in Books Online. It will explain what that fail. SQL
Server will try to convert the string to an int. You have to use an explicit CAST:
SELECR Productname + CAST(CustomerID AS varchar(20))
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type (CustomerID)I want to contact them
in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have any idea?
|||mahsa
CREATE TABLE #Test
(
col INT,
col1 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES (1,'AA')
INSERT INTO #Test VALUES (2,'BB')
GO
SELECT CAST(col AS VARCHAR(10))+col1 FROM #Test
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type
(CustomerID)I want to contact them in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have
any idea?
|||thank you it works

contact 2 column

Hi I have 2 coloumn one hast char(Productname) type another has int type (CustomerID)I want to contact them in my stored procedure like
select Productname+CustomerID az newI
but I have error becouse one of them are int another in char ddo you have any idea?I suggest that you read about "Datatype Precedence" in Books Online. It will explain what that fail. SQL
Server will try to convert the string to an int. You have to use an explicit CAST:
SELECR Productname + CAST(CustomerID AS varchar(20))
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type (CustomerID)I want to contact them
in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have any idea?|||mahsa
CREATE TABLE #Test
(
col INT,
col1 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES (1,'AA')
INSERT INTO #Test VALUES (2,'BB')
GO
SELECT CAST(col AS VARCHAR(10))+col1 FROM #Test
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type
(CustomerID)I want to contact them in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have
any idea?|||thank you it works :)

contact 2 column

Hi I have 2 coloumn one hast char(Productname) type another has int type (Cu
stomerID)I want to contact them in my stored procedure like:
select Productname+CustomerID az newID
but I have error becouse one of them are int another in char ddo you have an
y idea?I suggest that you read about "Datatype Precedence" in Books Online. It will
explain what that fail. SQL
Server will try to convert the string to an int. You have to use an explicit
CAST:
SELECR Productname + CAST(CustomerID AS varchar(20))
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type (Customer
ID)I want to contact them
in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have any idea
?|||mahsa
CREATE TABLE #Test
(
col INT,
col1 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES (1,'AA')
INSERT INTO #Test VALUES (2,'BB')
GO
SELECT CAST(col AS VARCHAR(10))+col1 FROM #Test
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type
(CustomerID)I want to contact them in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have
any idea?|||thank you it works

Tuesday, February 14, 2012

constraints on char,varchar

hI,
Is there is a way to find constraints on columns char and
varchar alone..(through query)
Sridhar.
Hi
You could try something like:
SELECT object_name(c.id), c.name, t.name, s.*
FROM sysconstraints s JOIN syscolumns c on c.id = s.id and c.colid = s.colid
JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%char%'
John
<anonymous@.discussions.microsoft.com> wrote in message
news:91d001c43330$171e8760$a001280a@.phx.gbl...
> hI,
> Is there is a way to find constraints on columns char and
> varchar alone..(through query)
> Sridhar.
|||Thanks John..
Is it possible to find out the indexes on char columns

>--Original Message--
>Hi
>You could try something like:
>SELECT object_name(c.id), c.name, t.name, s.*
>FROM sysconstraints s JOIN syscolumns c on c.id = s.id
and c.colid = s.colid
>JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%
char%'[vbcol=seagreen]
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:91d001c43330$171e8760$a001280a@.phx.gbl...
and
>
>.
>
|||> Is it possible to find out the indexes on char columns
I modified John's original script for this requirement:
SELECT object_name(i.id), i.name, c.name, t.name
FROM sysindexes i
JOIN sysindexkeys ik ON ik.id = i.id
JOIN syscolumns c ON c.id = ik.id AND c.colid = ik.colid
JOIN systypes t ON c.xtype = t.xtype AND t.name LIKE '%char%'
ORDER BY object_name(i.id), i.name, c.name
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:8f6401c43345$6601f1e0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks John..
> Is it possible to find out the indexes on char columns
>
> and c.colid = s.colid
> char%'
> and

constraints on char,varchar

hI,
Is there is a way to find constraints on columns char and
varchar alone..(through query)
Sridhar.Hi
You could try something like:
SELECT object_name(c.id), c.name, t.name, s.*
FROM sysconstraints s JOIN syscolumns c on c.id = s.id and c.colid = s.colid
JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%char%'
John
<anonymous@.discussions.microsoft.com> wrote in message
news:91d001c43330$171e8760$a001280a@.phx.gbl...
> hI,
> Is there is a way to find constraints on columns char and
> varchar alone..(through query)
> Sridhar.|||Thanks John..
Is it possible to find out the indexes on char columns

>--Original Message--
>Hi
>You could try something like:
>SELECT object_name(c.id), c.name, t.name, s.*
>FROM sysconstraints s JOIN syscolumns c on c.id = s.id
and c.colid = s.colid
>JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%
char%'
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:91d001c43330$171e8760$a001280a@.phx.gbl...
and[vbcol=seagreen]
>
>.
>|||> Is it possible to find out the indexes on char columns
I modified John's original script for this requirement:
SELECT object_name(i.id), i.name, c.name, t.name
FROM sysindexes i
JOIN sysindexkeys ik ON ik.id = i.id
JOIN syscolumns c ON c.id = ik.id AND c.colid = ik.colid
JOIN systypes t ON c.xtype = t.xtype AND t.name LIKE '%char%'
ORDER BY object_name(i.id), i.name, c.name
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:8f6401c43345$6601f1e0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks John..
> Is it possible to find out the indexes on char columns
>
> and c.colid = s.colid
> char%'
> and

constraints on char,varchar

hI,
Is there is a way to find constraints on columns char and
varchar alone..(through query)
Sridhar.Hi
You could try something like:
SELECT object_name(c.id), c.name, t.name, s.*
FROM sysconstraints s JOIN syscolumns c on c.id = s.id and c.colid = s.colid
JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%char%'
John
<anonymous@.discussions.microsoft.com> wrote in message
news:91d001c43330$171e8760$a001280a@.phx.gbl...
> hI,
> Is there is a way to find constraints on columns char and
> varchar alone..(through query)
> Sridhar.|||Thanks John..
Is it possible to find out the indexes on char columns
>--Original Message--
>Hi
>You could try something like:
>SELECT object_name(c.id), c.name, t.name, s.*
>FROM sysconstraints s JOIN syscolumns c on c.id = s.id
and c.colid = s.colid
>JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%
char%'
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:91d001c43330$171e8760$a001280a@.phx.gbl...
>> hI,
>> Is there is a way to find constraints on columns char
and
>> varchar alone..(through query)
>> Sridhar.
>
>.
>|||> Is it possible to find out the indexes on char columns
I modified John's original script for this requirement:
SELECT object_name(i.id), i.name, c.name, t.name
FROM sysindexes i
JOIN sysindexkeys ik ON ik.id = i.id
JOIN syscolumns c ON c.id = ik.id AND c.colid = ik.colid
JOIN systypes t ON c.xtype = t.xtype AND t.name LIKE '%char%'
ORDER BY object_name(i.id), i.name, c.name
--
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:8f6401c43345$6601f1e0$a401280a@.phx.gbl...
> Thanks John..
> Is it possible to find out the indexes on char columns
>
> >--Original Message--
> >Hi
> >
> >You could try something like:
> >
> >SELECT object_name(c.id), c.name, t.name, s.*
> >FROM sysconstraints s JOIN syscolumns c on c.id = s.id
> and c.colid = s.colid
> >JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%
> char%'
> >
> >John
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:91d001c43330$171e8760$a001280a@.phx.gbl...
> >> hI,
> >>
> >> Is there is a way to find constraints on columns char
> and
> >> varchar alone..(through query)
> >>
> >> Sridhar.
> >
> >
> >.
> >

Sunday, March 25, 2012

Conversion of Char to SmallDateTime

Thursday, March 22, 2012

CONVERSION FROM CHAR(4) TO DATETIME

Conversion from char to Nchar

conversion from CHAR to DATETIME error

Conversion ERROR

conversion char/nchar

conversion char/nchar

conversion char/nchar

Tuesday, March 20, 2012

Conversion

Conversion

Conversion

Sunday, February 19, 2012

contact 2 column

contact 2 column

contact 2 column

Tuesday, February 14, 2012

constraints on char,varchar

constraints on char,varchar

constraints on char,varchar

Consolidation Query

Blog Archive

About Me