CONVERT a text column to date format

I have a column nvarchar(8) that I need to update to a date format, MM/DD/YYYY

Some of the values have 7 characters. the rest have 8 characters as shown below:

Col1

6051998

12061999

In both rows the format is M/DD/YYYY and MM/DD/YYYY respectively. I have tried using CONVERT and CAST but receive the error:

Conversion failed when converting datetime from character string.

I've manage to generate the correct format in a select statement using CASE:

SELECT date_updted =

CASE

WHEN(selectLEN(date_updted))= 7 THEN(SELECTLEFT((RIGHT(date_updted, 7)), 1)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4)))

ELSE(SELECTLEFT((RIGHT(date_updted, 8)), 2)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4)))

END

FROM Table1

How can perform an update of this column using the UPDATE statement? I've tried the following with no success:

UPDATE dbo.Table1

SET date_updted =(SELECT date_updted =

CASE

WHEN(selectLEN(date_updted))= 7 THEN(SELECTLEFT((RIGHT(date_updted, 7)), 1)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4))As date_updted)

ELSE(SELECTLEFT((RIGHT(date_updted, 8)), 2)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4))As date_updted)

END

FROM Table1)

FROM Table1

If your data is as strongly formated as you say, you should be able to convert to datetime with something like:

Code Snippet

select aDate,
convert(datetime, right(aDate, 4) + left( right('0'+aDate, 8), 4))
as convertedDT
from ( select '6051998' as aDate union all
SELECT '12061999'
) a

/*
aDate convertedDT
--
6051998 1998-06-05 00:00:00.000
12061999 1999-12-06 00:00:00.000
*/

|||

Try something like this:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
DateCol varchar(8)
)

INSERT INTO @.MyTable VALUES ( '6051998' )
INSERT INTO @.MyTable VALUES ( '12061999' )

SELECT convert( datetime, ( stuff( stuff( right( '0' + DateCol, 8 ), 3, 0, '/' ), 6, 0, '/' )), 101 )
FROM @.MyTable

-
1998-06-05 00:00:00.000
1999-12-06 00:00:00.000

|||

Thanks for your responses and I'm sure these methods will work fine, however, my question really was how to use the UPDATE Statement to update the column in my initial post without creating a temp table or column. Maybe I missed something in your responses?

|||

Copy the expression from those select statement...

Code Snippet

UPDATE

dbo.Table1

SET

date_updted =convert(datetime, right(date_updted, 4) + left( right('0'+date_updted, 8), 4))

FROM

Table1

|||

Maybe:

Code Snippet

UPDATE dbo.Table1
SET date_updted
= convert(varchar(10),convert(datetime, right(date_updted, 4) + left( right('0'+date_updted, 8), 4)),101)

|||Very Nice! Thanks.

Controlling Updated fields on trigger

In myTable i've got a trigger for INSERT, UPDATE, DELETE
I Update myField1 of myTable in the trigger but not in the query that starts
the trigger.
Then i check if UPDATE(myField1). This is true or false?
CREATE TRIGGER tr_MyTable ON dbo.MyTable
FOR INSERT, UPDATE, DELETE
AS
...
UPDATE myTable SET myField1 = 'XYZ' WHERE ...
...
if update(myField1 ) /* IS TRUE OR FALSE ' */
begin
..
endFalse.
The UPDATE( ) clause comes from the state of the data that fired the
trigger, not from any manipulations inside the trigger.
Also, FWIW, if your update sets a column to the same value it had before the
update, that will also be considered an updated column. (Since I did not
find the clause useful, I stopped using it. If it is smarter in 2000,
someone should know.)
RLF
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:9B1547D0-05DA-438B-947D-8DFDA29FE77A@.microsoft.com...
> In myTable i've got a trigger for INSERT, UPDATE, DELETE
> I Update myField1 of myTable in the trigger but not in the query that
> starts
> the trigger.
> Then i check if UPDATE(myField1). This is true or false?
> CREATE TRIGGER tr_MyTable ON dbo.MyTable
> FOR INSERT, UPDATE, DELETE
> AS
> ...
> UPDATE myTable SET myField1 = 'XYZ' WHERE ...
> ...
> if update(myField1 ) /* IS TRUE OR FALSE ' */
> begin
> ...
> end
>|||Russell Fields (RussellFields@.NoMailPlease.Com) writes:
> Also, FWIW, if your update sets a column to the same value it had before
> the update, that will also be considered an updated column. (Since I
> did not find the clause useful, I stopped using it. If it is smarter in
> 2000, someone should know.)
I have not tried it, but there is no reason to expect it to be smart.
If you update 1000 rows, and one of them changes value, what should IF
UDPATE return?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Controlling flow in a stored procedure

I have a stored procedure with two UPDATE statements in it. The second UPDATE statement relies on the completion of the first UPDATE statement to run correctly.

The problem I am running into is that SQL Server sometimes runs the second statement before completing the first.

To get around this, I tried putting the second UPDATE statement in a different stored procedure called within the first procedure, but I am still having problems.

I do not believe I am doing anything wrong, but just in case, here is the relevant code from the proc:

-- Look up County ID

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET CountyID =

(SELECT CountyID

FROM tblCountyLkp

WHERE tblCountyLkp.CountyName = LTRIM(RTRIM(tmpZoneTypes.CountyName)))

COMMIT TRANSACTION

-- Look up existing Zone Type IDs

BEGIN TRANSACTION

UPDATE tmpZoneTypes

SET ZoneTypeID =

(SELECT tblZoneTypes.ZoneTypeID

FROM tblZoneTypes

WHERE tblZoneTypes.CountyID = tmpZoneTypes.CountyID

AND tblZoneTypes.FieldNbr = tmpZoneTypes.FieldNbr

AND LTRIM(RTRIM(tblZoneTypes.ZoneAbbrev)) = LTRIM(RTRIM(tmpZoneTypes.ZoneAbbrev))

AND LTRIM(RTRIM(tblZoneTypes.ZoneFull)) = LTRIM(RTRIM(tmpZoneTypes.ZoneFull)))

COMMIT TRANSACTION

Is there a way to control the flow so the second update statement won't run until the first statement has been completed? I thought about maybe using a trigger to fire whenever the CountyID field is updated. Other options?

chris

1 variant (for SQL 2000 & SQL 2005):

begin transaction

declare @.ErrorVar int

update .... --The First Update

set @.ErrorVar = @.@.Error

if @.ErrorVar <>0

begin

-- Insert your error hadling code

rollback --For Example rollback transaction

end

else

begin

update ... --The second update

commit

end

2 variant (for SQL 2005 only):

begin tran

begin try

update ... --The first update

--If you have error in fist update you go to catch block

update ... - The second update

commit

end try

begin catch

-- Insert your error hadling code

rollback --For Example rollback transaction

end catch

|||SQL always executes "top down" and completes the first statement before starting the 2nd. What makes you think it is not complete?

The only way I see you would get different results than expected with what you posted, would be if you have the isolation level set to "read uncommitted". You can set the isolation level by using:

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE

at the top of your stored proc and that will force all updates to be committed and locks to be placed on the data until you are done.|||

Thanks for the suggestions from both of you. It turns out the problem was a bug in a subsequent UPDATE statement that was changing my ZoneTypeID back to NULL. I fixed the bug, and now the proc works perfectly.

chris

Controlling a Transaction by User in SQL Server 2000

Hey Folks!

I have a typical requirement by my client. On submitting a Update (Bulk) button a huge database operation starts. A huge bulk update operation need to be performed. This would take 2-3 minutes some times. Client wants a cancel button in this case where he can be given a way to cancel the database Transaction.

Please let me know in case if there is a way out.

Thanks, in advance.

Regards,

Uday.D

Hi ,

Where u want to handle transactions ...

from vb.net / C# or in Store Procedure (in SQL Server 2000) itself ...i prefers in Store Procedure

following links may help you

http://www.codeproject.com/database/sqlservertransactions.asp

http://www.samspublishing.com/articles/article.asp?p=27225&rl=1

|||

Thanks Amit for the same.

But, the links you had given does not give me the solution.

I am looking at the functionality something similar to the "Cancel executing Query Method" button in Query Anlayser in Sql Server 2005. If we run a huge query and click it when the query is processing the SQL execution engine can be stopped by clicking the button.

I hope this throws some more light.

Continuation of long SQL statement syntax

Hi All -

I am updating four values. What is the proper syntax to have the
following 4 update statements as one statement?

set objRec = objDB.Execute("Update orientform set session = '" &
strSession & "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set fname = '" & strfname
& "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set gender = '" &
strgender & "' where id = '" & strid & "'")
set objRec = objDB.Execute("Update orientform set lname = '" & strlname
& "' where id = '" & strid & "'")

Thanks,

Joey"update orientform set sessions = '" & strSession & "', fname = '" &
strfname & '", gender = etc etc
where id = '" & strid & "'"

Notes I see you called your command objRec... maybe just habit but you
aren't creating a recordset earlier in the piece are you? Not needed for
updates/inserts/deletes. Also if your id (in the table) has an int dataype
then forget the single quotes around your strid

Jay

<joseph.jasinski@.quinnipiac.edu> wrote in message
news:1102635650.764000.267730@.z14g2000cwz.googlegr oups.com...
> Hi All -
> I am updating four values. What is the proper syntax to have the
> following 4 update statements as one statement?
> set objRec = objDB.Execute("Update orientform set session = '" &
> strSession & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set fname = '" & strfname
> & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set gender = '" &
> strgender & "' where id = '" & strid & "'")
> set objRec = objDB.Execute("Update orientform set lname = '" & strlname
> & "' where id = '" & strid & "'")
> Thanks,
> Joey

context in trigger

Hi All,
I need to know if there is any methode to detect within a trigger, that it
was fired because of merge agent update of the table and not because of the
application's update.
Thanks.
query sessionproperty('replication_agent') to see if its value is 0. If so
a replication agent is making the update, if it is 1, it is another user
process.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Oussama Albairat" <OussamaAlbairat@.discussions.microsoft.com> wrote in
message news:990BF6F4-FF7A-4F76-B560-D9A02261FA3D@.microsoft.com...
> Hi All,
> I need to know if there is any methode to detect within a trigger, that
it
> was fired because of merge agent update of the table and not because of
the
> application's update.
> Thanks.
|||Hi Hilary,
Thank you for the indication. But I noticed that the condition should be
evaluated as in replication triggers : if (
sessionproperty('replication_agent') = 1 and (select trigger_nestlevel()) =
1) the first part alone is not enough to detect that the trigger has been
fired by replication agent.
Thanks.
"Hilary Cotter" wrote:

> query sessionproperty('replication_agent') to see if its value is 0. If so
> a replication agent is making the update, if it is 1, it is another user
> process.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Oussama Albairat" <OussamaAlbairat@.discussions.microsoft.com> wrote in
> message news:990BF6F4-FF7A-4F76-B560-D9A02261FA3D@.microsoft.com...
> it
> the
>
>