Hello..
I am having a difficult time trying to get SQL Server to convert the
following date:
22-08-2004 00:00:00
I have tried to convert and cast and I get the following error message:
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.
Any help would be most appreciative.
Thank you,
Brett
P.S.
I am using SQL Server 2000How are you doing this convert ? This works for me:
Select convert(varchar(20),'22-08-2004 00:00:00',102)
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Brett Davis" <bdavis123@.cox.net> schrieb im Newsbeitrag
news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> Hello..
> I am having a difficult time trying to get SQL Server to convert the
> following date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> 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.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>|||You need to tell SQL Server more about the pattern used for the datetime. Tr
y
this:
Select Convert(DateTime, '22-08-2004 00:00:00', 105)
Without specifying the date format, I believe that SQL uses the format from
the
database's collation or perhaps the server's regional settings.
HTH
Thomas
"Brett Davis" <bdavis123@.cox.net> wrote in message
news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> Hello..
> I am having a difficult time trying to get SQL Server to convert the follo
wing
> date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> 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.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>|||Use styles 112 or 126. See CONVERT in BOL.
Example:
select cast('20040822' as datetime)
select cast('2004-08-22T00:00:00.000' as datetime)
go
AMB
"Brett Davis" wrote:
> Hello..
> I am having a difficult time trying to get SQL Server to convert the
> following date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> 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.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>
>|||Thomas,
You use the style parameter when converting from datetime to varchar / char
and not the opposite. All these statements will give same result.
select convert(datetime, '20050427', 105)
select convert(datetime, '20050427', 112)
select convert(datetime, '20050427', 126)
select convert(datetime, '20050427')
AMB
"Thomas" wrote:
> You need to tell SQL Server more about the pattern used for the datetime.
Try
> this:
> Select Convert(DateTime, '22-08-2004 00:00:00', 105)
> Without specifying the date format, I believe that SQL uses the format fro
m the
> database's collation or perhaps the server's regional settings.
>
> HTH
>
> Thomas
>
> "Brett Davis" <bdavis123@.cox.net> wrote in message
> news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
>
>|||I think we might be saying the same thing. By passing the style parameter, y
ou
are giving SQL information about the format of the string.
>All these statements will give same result.
> select convert(datetime, '20050427', 105)
> select convert(datetime, '20050427', 112)
> select convert(datetime, '20050427', 126)
> select convert(datetime, '20050427')
But these do not:
1. select convert(datetime, '22-08-2004 00:00:00', 105)
2. select convert(datetime, '22-08-2004 00:00:00', 112)
3. select convert(datetime, '22-08-2004 00:00:00', 126)
4. select convert(datetime, '22-08-2004 00:00:00',)
Only the first one successfully parses the string into a datetime. The other
s
fail with a conversion error because the system thinks that the first digits
are
the month instead of the day.
I'll grant you that passing the ISO format (yyyymmdd) would be the best way
to
avoid all of these problems.
Thomas|||You are right.
AMB
"Thomas" wrote:
> I think we might be saying the same thing. By passing the style parameter,
you
> are giving SQL information about the format of the string.
>
> But these do not:
> 1. select convert(datetime, '22-08-2004 00:00:00', 105)
> 2. select convert(datetime, '22-08-2004 00:00:00', 112)
> 3. select convert(datetime, '22-08-2004 00:00:00', 126)
> 4. select convert(datetime, '22-08-2004 00:00:00',)
> Only the first one successfully parses the string into a datetime. The oth
ers
> fail with a conversion error because the system thinks that the first digi
ts are
> the month instead of the day.
> I'll grant you that passing the ISO format (yyyymmdd) would be the best wa
y to
> avoid all of these problems.
>
> Thomas
>
>
Showing posts with label thefollowing. Show all posts
Showing posts with label thefollowing. Show all posts
Sunday, February 19, 2012
Sunday, February 12, 2012
Converting Select query to Update
Hi,
I need to update some data based on the results of a select query. The
following select statement returns the values:
SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
C.Action, B.Status, C.dateEntered
FROM A INNER JOIN
B ON A.itemid = B.RecordNumber INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
ORDER BY A.itemid DESC
What I want to do is update DataSigned as follows.
UPDATE B SET B.DateSigned=C.dateEntered
WHERE '?
I am not sure how to set up the WHERE clause to update the correct records
with the correct values. Suggestions?
Thanks,
JerryYou can try the following:
UPDATE B
SET B.DateSigned = C.dateEntered
FROM B
inner join ( SELECT DISTINCT A.itemid
, B.DateSigned
, B.RefID
, C.EnteredBy
, C.Action
, B.Status
, C.dateEntered
FROM A
INNER JOIN B
ON A.itemid = B.RecordNumber
INNER JOIN C
ON A.itemid = C.recordid
LEFT OUTER JOIN D
ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
) C
on C.ItemID = B.RecordNumber
You may want to check the join clause to make sure you match the records
exactly. In any case, the idea is to use derived tables, which is the one
that is created on-the-fly using the SELECT statement, and referenced just
like a regular table or view
Let me know if it helps
"JerryK" wrote:
> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>
>|||something like this (completely untested):
UPDATE B
SET DateSigned=(
SELECT C.dateEntered
FROM A INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE D.itemid IS NULL
and C.action like '%signed%'
and A.itemid = B.RecordNumber )
WHERE status = 'app'
dean
"JerryK" <jerryk@.nospam.com> wrote in message
news:%23S1clJfIGHA.2668@.tk2msftngp13.phx.gbl...
> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>|||On Wed, 25 Jan 2006 13:00:37 -0800, JerryK wrote:
(snip)
Hi Jerry,
I just answered this question in microsoft.public.sqlserver.newusers.
In the future, please post your questions to one group only. And if you
really feel that a question should be in two groups, crosspost it (i.e.
send one copy to both groups at the same time) instead of sending
independent copies to the groups. With crossposting, all replies will
(normally) show up in both groups as well. That saves others the time
and energy to find an answer if the question already was answered
elsewhere!
Hugo Kornelis, SQL Server MVP
I need to update some data based on the results of a select query. The
following select statement returns the values:
SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
C.Action, B.Status, C.dateEntered
FROM A INNER JOIN
B ON A.itemid = B.RecordNumber INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
ORDER BY A.itemid DESC
What I want to do is update DataSigned as follows.
UPDATE B SET B.DateSigned=C.dateEntered
WHERE '?
I am not sure how to set up the WHERE clause to update the correct records
with the correct values. Suggestions?
Thanks,
JerryYou can try the following:
UPDATE B
SET B.DateSigned = C.dateEntered
FROM B
inner join ( SELECT DISTINCT A.itemid
, B.DateSigned
, B.RefID
, C.EnteredBy
, C.Action
, B.Status
, C.dateEntered
FROM A
INNER JOIN B
ON A.itemid = B.RecordNumber
INNER JOIN C
ON A.itemid = C.recordid
LEFT OUTER JOIN D
ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
) C
on C.ItemID = B.RecordNumber
You may want to check the join clause to make sure you match the records
exactly. In any case, the idea is to use derived tables, which is the one
that is created on-the-fly using the SELECT statement, and referenced just
like a regular table or view
Let me know if it helps
"JerryK" wrote:
> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>
>|||something like this (completely untested):
UPDATE B
SET DateSigned=(
SELECT C.dateEntered
FROM A INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE D.itemid IS NULL
and C.action like '%signed%'
and A.itemid = B.RecordNumber )
WHERE status = 'app'
dean
"JerryK" <jerryk@.nospam.com> wrote in message
news:%23S1clJfIGHA.2668@.tk2msftngp13.phx.gbl...
> Hi,
> I need to update some data based on the results of a select query. The
> following select statement returns the values:
> SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
> C.Action, B.Status, C.dateEntered
> FROM A INNER JOIN
> B ON A.itemid = B.RecordNumber INNER JOIN
> C ON A.itemid = C.recordid LEFT OUTER JOIN
> D ON A.itemid = D.DemoRecordID
> WHERE (D.itemid IS NULL)
> and B.status = 'app'
> and C.action like '%signed%'
> ORDER BY A.itemid DESC
>
> What I want to do is update DataSigned as follows.
> UPDATE B SET B.DateSigned=C.dateEntered
> WHERE '?
> I am not sure how to set up the WHERE clause to update the correct records
> with the correct values. Suggestions?
> Thanks,
> Jerry
>|||On Wed, 25 Jan 2006 13:00:37 -0800, JerryK wrote:
(snip)
Hi Jerry,
I just answered this question in microsoft.public.sqlserver.newusers.
In the future, please post your questions to one group only. And if you
really feel that a question should be in two groups, crosspost it (i.e.
send one copy to both groups at the same time) instead of sending
independent copies to the groups. With crossposting, all replies will
(normally) show up in both groups as well. That saves others the time
and energy to find an answer if the question already was answered
elsewhere!
Hugo Kornelis, SQL Server MVP
Subscribe to:
Posts (Atom)