Conert Char to Datetime

Hello,

I have made a slight error in my SQL2000 database and have found this out with 10000 plus records under my belt.

I have a field called Char with entries 12mid and 12noon. I need to convert these to Datetime. All records convert OK except these two.

I am using DTS to transfer records form old table to new table. How do I write a procedure (or something) to enable conversion of the records 12mid and 12noon to 12am and 12pm.

Thanks

GoongYou run one batch that "REPLACE"s noon/mid with pm|||pkr,

When you say batch, what exactly is that. Is is the transformation in the DTS options.
When you say replace, how would I go about it, Is it through a query, if so, how would it possibly look.

Thanks
Goong|||Batch as in 'SQL Query'. You can run a batch in DTS but since you prob' want to do this once I'd just run it from your fav' query tool.

The clue was in my response, "REPLACE". Use it with an Update query.

conditions, expressions

I have a table

CREATE TABLE [dbo].[CmnLanguage]
(
[Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY,
[EnglishName] [varchar](26) NOT NULL,
[NativeName] [nvarchar](26) NOT NULL,
[DirectionType] [smallint] NOT NULL,
[IsVisible] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

We will use these 3 queries

select * from CmnLanguage where IsVisible = 0
select * from CmnLanguage where IsVisible = 1
select * from CmnLanguage

I want to make a method which handles these queries.

But at the back end on Stored Procedures

We have to write 3 queries

Which I don't want to do.

I want to minimize the queries and conditions

and want to just write one for these 3

Can any one do it?

How about this:

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE PROCEDURE dbo.sp_MyProcedure(@.IsVisibleAS BIT =NULL)ASBEGINSELECT*FROM[dbo].[CmnLanguage]WHERE[IsVisible] =CASEWHEN @.IsVisibleISNULLTHEN [IsVisible]ELSE @.IsVisibleENDENDGO

|||

Nice.

Very Useful.

Thanks.

Conditional Split query

Hi,

I have the following table in MsAccess

EmployeesA

empId integer,

empName varchar(60),

empAge integer,

empStatus char(1) - can be N,D or S - New, Deleted or Shifted

and the following in Sql2005

EmployeesB

Id smallint,

Name varchar(60),

Age int,

Status char(1) - Bydefault 'N'

I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

How do I do it for each table each row in EmployeesA using a foreach file loop?

Thanks,

ron

If you are using a data flow inside your For Each, you can use the techniques shown in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1) to determine whether the row should be inserted or updated. The thread is dicussing specifically whether the row already exists or not, so you may need to add a conditional split to your data flow.

|||

Hi,

thanks for the reply. I had already seen that link. I cannot do a look up as Employees B will already contain millions of rows.

I just want to know this step by step if you could explain. I am so new to this SSIS.

How will I specify conditions :

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

Which control to use. Where to specify etc.

thanks,

|||

Ok,

I have figured out most of it through a friend. Just tell me this:

What i am doing is :

For status D, I do a Lookup and if found, I have to use an OLE DB Command tranform to do the update.

What query do I fire in the look up over here. If that row exists, after that what to do in the OLEDB command. ?How to pass the current row?

thanks.

|||Do you mean how to update the row that was matched in the lookup? Why can you not use the same fields you used in the lookup for the match and put them into the WHERE clause of your update? Sometimes it is cleaner to return a key column or two from the lookup, and them as basis for the WHERE clause.|||

Can you state an example. What should be in the Lookup and what in the OledbCommand based on my table.

Thanks

|||

An example, do you mean for this problem-

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

I would be tempted to skip the lookup. Use a Conditional Split to get a feed of all EmployeesA rows where empStatus = "D", then use a command to do the update. Set your connection, and end the SQL statement -

UPDATE EmployeesB

SET empStatus = 'D'

WHERE empname = ?

AND age = ?

Map the two input columns empname and age to the two parameters, to complete the OLD-DB Command setup.

This avoids the costs of a lookup, which may be faster overall. If there is no match, then no update happens, which is the same overall outcome as if the lookup had failed to find anything and the command was not run.

It may be faster to use a Lookup to help filter out the non-matches, it really depends on row counts and ratios of lookup hits to misses. Test both if you are worried about performance, but it is often faster to attempt and "fail" than to prevent the "fail" in the first place in SSIS.

|||

Darren,

you know what..that worked like a charm i removed the look up and did as you said..I will try the rest and if everything works, i will close this thread. thankuuuuuu.

If you could have a look at this thread too, I will be much obliged.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2178311&SiteID=1

thanks.

Condensed Type vs Data Type

In the database diagram, it used to be that Data Type was int, varchar, char, etc. and Condensed Type was int, varchar(30), char(5), etc. Why was this changed in SQL 2005? Is this a bug?

My assumption is that it's because of the new data type "xml". The help says as follows:-

Condensed Data Type

Displays information about the field's data type, in the same format as the SQL CREATE TABLE statement. For example, a field containing a variable-length string with a maximum length of 20 characters would be represented as "varchar(20)". To change this property, type the value directly.

ym

Condensed Type vs Data Type

In the database diagram, it used to be that Data Type was int, varchar, char, etc. and Condensed Type was int, varchar(30), char(5), etc. Why was this changed in SQL 2005? Is this a bug?

My assumption is that it's because of the new data type "xml". The help says as follows:-

Condensed Data Type

Displays information about the field's data type, in the same format as the SQL CREATE TABLE statement. For example, a field containing a variable-length string with a maximum length of 20 characters would be represented as "varchar(20)". To change this property, type the value directly.

ym

Concatenation problem: two integers and a char

Hello to all,

I am writing a query that is attempting to take three fields in a table, and create a new field called "MyKey." I'm doing this using concatenation. The problem: two of these fields, Storage_Facility and Storage_Receipt_Number, are integer fields. The third of these fields, Receipt_Suffix, is a char field. It appears that SQL server will not allow this. I can do it in Microsoft Access, why not in SQL Server? Is there any way around this?

Here's the relevant part of my query:

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(MyTable.STORAGE_FACILITY+MyTable.STORAGE_RECEIPT_ NUMBER+MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey,

Etc

If you can help, great!

Thanks.

Quote:

Originally Posted by mikeDA

Hello to all,

I am writing a query that is attempting to take three fields in a table, and create a new field called "MyKey." I'm doing this using concatenation. The problem: two of these fields, Storage_Facility and Storage_Receipt_Number, are integer fields. The third of these fields, Receipt_Suffix, is a char field. It appears that SQL server will not allow this. I can do it in Microsoft Access, why not in SQL Server? Is there any way around this?

Here's the relevant part of my query:

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(MyTable.STORAGE_FACILITY+MyTable.STORAGE_RECEIPT_ NUMBER+MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey,

Etc

If you can help, great!

Thanks.

Try any of these...

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(CAST(MyTable.STORAGE_FACILITY AS VARCHAR(10))
+ CAST(MyTable.STORAGE_RECEIPT_ NUMBER AS VARCHAR(10))+
MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey,

SELECT MyTable.STORAGE_FACILITY,
MyTable.STORAGE_RECEIPT_NUMBER,
MyTable.STORAGE_RECEIPT_SUFFIX,
(CONVERT(VARCHAR(10),MyTable.STORAGE_FACILITY) +
CONVERT(VARCHAR(10),MyTable.STORAGE_RECEIPT_ NUMBER)+
MyTable.STORAGE_RECEIPT_SUFFIX)
AS MyKey ,

Concatenation of Text

I have an instance where I need to concatenate some data that is stored in a text datatype. I can't cast it to a varchar/char because that may well truncate the data. I just read about UPDATETEXT, which I think I can use, but I need to use it for a bunch or rows and it looks like this works on one row at a time. Anyone have experience with this?Yes, UpdateText processes only one row per call.

The MDAC infrastructure was never meant to handle BLOBs, so trying to update a TEXT column in a million rows at once is really a bad idea. I'd either change the column datatype, or find a different way to acheive the same goal.

This just sounds like a problem sniffing eagerly at a new victim to me!

-PatP|||Thanks Pat. Unfortunately, I have to use a TEXT column because the data can exceed the 8k limit for VARCHAR and CHAR. I guess my choices are to use DTS and some sort of ActiveX script, write a script to concatenate before it hits the database, or do the unthinkable and write a cursor (although I don't really want to do that).

Dandy|||Nothing quite like being caught between the devil and the deep blue sea, is there? Given those choices, I'd opt for DTS.

Actually, cursors aren't logically bad, it is just that their performance is awful compared to set operations. Many databases like Oracle and Z-Series DB2 rely on cursors to do much of anything.

Be forewarned though, TEXT manipulation is slower than other datatypes. Just retrieving or storing a row with a TEXT or IMAGE column takes a lot longer than it does without those columns.

-PatP|||Of course it does, doh...First it needs to get the pointer (binary(16)), then retrieve 8K worth of data stored in a separate set of pages which results in at least 1 additional IO per row retrieved. BLOBs are wonderful (hehehe) when used for what they were invented, - not for set-based operations. And this is one of the few cases when a cursor may very well be applicable.

The MDAC infrastructure was never meant to handle BLOBs...Really? Who told you that?|||Really? Who told you that?It was either the guy who invented DTS, or the GPM for MS-SQL 7.0. They were both there, I just don't remember who actually said it and who just nodded sagely.

-PatP|||Oh, I see, and I'm an airplane :D|||I'm a teapot! I'm a teapot!

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
DanOn Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
--
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegroups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
> > When executing the following statements:
> > declare @.x char(10), @.y char(10)
> > set @.x = 'abc'
> > set @.y = @.x + 'def'
> > select @.x
> > select @.y
> > the results are:
> > abc
> > abc
> > I expect @.y should be equal to 'abcdef'. If I change the var types to
> > int for example, then @.y is summed correctly. Can anyone tell me why,
> > or what I'm doing wrong? Thanks.
> > Dan
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
DanOn Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegroups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
>
>
>
>
>
>
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
Dan
On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.
|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegro ups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>
|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
>
>
>
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan
|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.