Converting data types

Having a slight problem. For whatever reason I had a column setup for nvchar but the only data i have in there is dates (MM/DD/YY) and a whole bunch of NULLs. I wanted to change the datatype to datetime but it gave me an error. I thought it was due to all the NULLs, so I removed all the nulls. It still doesn't work. I get the error : Conversion failed when converting datetime from character string.

I don't belive its because I have the data in mm/dd/yy format. I created another table and populated it with mm/dd/yy data as nvchar (50) and then converted to datetime and it worked fine.

any ideas?

Obviously, as you know, you get this error because the conversion could not convert some of the data (as it was not in the correct format). First of all, you can use IsDate function to see which data is giving you problem. Other than that, try this post. Just as a precaution, backup the table or database before you try anything.

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

|||

Rather than trying to update the data type, try creating an additional field with a DateTime type. You can then copy all the dates that aren't null into this column and then drop the original column (and rename the new one if necessary).

|||

I would look through the data. When dates are stored as varchar, and especially if there isn't any validation when it is inserted into the database, you'll often see values like 01/01/3007 or 06/32/2007 or some other way it was entered that caused ambiguity with SQL Server. I've had the same thing happen in the past.

|||

Hi,

Review the Globalization configuration in the web.config, put en-US to work fine in the culture and uiculture attributes, by default is en-US, may be you change it.

greetings.

Converting data to UPPERCASE

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

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

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

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

Converting data to UPPERCASE

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

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

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

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

Converting data to UPPERCASE

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

Convert User DSN to System DSN

Hi all,
I stupidly setup a database as a userDSN but now want to make it a system
dsn so that I can access it from another machine. I have an access db that
uses msde linked tables for the back end, which is installed locally on 1
machine in the network. I want to be able to add new features to the access
database from another machine and then copy over the improved version to the
other machine. What should I do?
You cannot access a DSN from another machine unless you are using a remote
sofware program, such as Terminal Server, to connect to the machine that
houses the DSN.
Either way, there is no way to make a user DSN a system DSN. You will need
to recreate it using the ODBC Admin applet.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||So how do people connect to a sql server running on another machine? I have
3 machines and 1 server, the server is running SQL Server and I want the 3
machines to have the same access database with linked tables.
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:tNMbkd8fEHA.3340@.cpmsftngxa06.phx.gbl...
> You cannot access a DSN from another machine unless you are using a remote
> sofware program, such as Terminal Server, to connect to the machine that
> houses the DSN.
> Either way, there is no way to make a user DSN a system DSN. You will need
> to recreate it using the ODBC Admin applet.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
|||You create a user DSN on your OWN machine which gives the address (ip number
for example) of the machine running SQL server, to connect to a remote SQL
server.
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:%239P$W%238fEHA.632@.TK2MSFTNGP12.phx.gbl...
> So how do people connect to a sql server running on another machine? I
have[vbcol=seagreen]
> 3 machines and 1 server, the server is running SQL Server and I want the 3
> machines to have the same access database with linked tables.
>
> "Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
> news:tNMbkd8fEHA.3340@.cpmsftngxa06.phx.gbl...
remote[vbcol=seagreen]
need[vbcol=seagreen]
rights.
>

Convert User DSN to System DSN

Hi all,
I stupidly setup a database as a userDSN but now want to make it a system
dsn so that I can access it from another machine. I have an access db that
uses msde linked tables for the back end, which is installed locally on 1
machine in the network. I want to be able to add new features to the access
database from another machine and then copy over the improved version to the
other machine. What should I do?You cannot access a DSN from another machine unless you are using a remote
sofware program, such as Terminal Server, to connect to the machine that
houses the DSN.
Either way, there is no way to make a user DSN a system DSN. You will need
to recreate it using the ODBC Admin applet.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||So how do people connect to a sql server running on another machine? I have
3 machines and 1 server, the server is running SQL Server and I want the 3
machines to have the same access database with linked tables.
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:tNMbkd8fEHA.3340@.cpmsftngxa06.phx.gbl...
> You cannot access a DSN from another machine unless you are using a remote
> sofware program, such as Terminal Server, to connect to the machine that
> houses the DSN.
> Either way, there is no way to make a user DSN a system DSN. You will need
> to recreate it using the ODBC Admin applet.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||You create a user DSN on your OWN machine which gives the address (ip number
for example) of the machine running SQL server, to connect to a remote SQL
server.
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:%239P$W%238fEHA.632@.TK2MSFTNGP12.phx.gbl...
> So how do people connect to a sql server running on another machine? I
have
> 3 machines and 1 server, the server is running SQL Server and I want the 3
> machines to have the same access database with linked tables.
>
> "Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
> news:tNMbkd8fEHA.3340@.cpmsftngxa06.phx.gbl...
remote[vbcol=seagreen]
need[vbcol=seagreen]
rights.[vbcol=seagreen]
>

convert stored procedures returned output to varchar from int?

I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :(

Syntax error converting the varchar value 'ABC' to a column of data type int.

SQL file

======================

DECLARE @.S AS VARCHAR(1000)

EXEC @.S = PDT.DBO.SP_RPT
'ABC'

SELECT CONTENT = @.S -- this is the value I am trying to pass as content of the email

EXEC PRODUCTION.DBO.SENDEMAIL'xxx@.hotmail.com', 'Notification', @.S

======================

Stored Procedure

======================

CREATE procedure sp_RPT
( @.array varchar(1000) )
AS

DECLARE @.content AS VARCHAR(2000)

SET @.content = 'RPT: ' + @.array + ' loaded successfully '

SET @.array = 'ABC'
RETURN CONVERT(VARCHAR(1000),@.array)

GO

try

cast( @.array as varchar(1000))

Hope this help

|||Don't use RETURN values in that manner. Either use an output parameter, or a resultset. RETURN values should be used as a control channel, not as a data channel; RETURN's purpose is to terminate control of a stored procedure immediately and also to report on how control was terminated (0 = normal/good, anything else is abnormal/bad).

convert into to hours minutes

Someone else setup the table and I cannot change it, so I am stuck with
what I have.
I have an INT field called hourminute.
The data is 3-4 digits, and is always based on hour/minute in 24-hour
format.
I need the output to be hh:mm. In the field, there is not a COLON and
that is what I need.
I have the following sql query that converts it correctly, but I need to
insert the :
select CONVERT(CHAR(5),hourminute,8)as thetime from booking
Help'
*** Sent via Developersdex http://www.examnotes.net ***SELECT CAST(hourminute / 100 AS char(2)) + ':' + RIGHT(CAST(hourminute AS
char(4)),2)
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"Joey Martin" wrote:

> Someone else setup the table and I cannot change it, so I am stuck with
> what I have.
> I have an INT field called hourminute.
> The data is 3-4 digits, and is always based on hour/minute in 24-hour
> format.
> I need the output to be hh:mm. In the field, there is not a COLON and
> that is what I need.
> I have the following sql query that converts it correctly, but I need to
> insert the :
> select CONVERT(CHAR(5),hourminute,8)as thetime from booking
>
> Help'
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Works if there are 4 digits, but does not work if 3.
Example:
730 = 7 :0
725 = 7 :5
*** Sent via Developersdex http://www.examnotes.net ***|||Hi, Joey
You have used the CONVERT function using 8 for the style argument, but
you specified that the hourminute column is an int. The style argument
is only used if you convert a datetime, smalldatetime, float, real,
money, smallmoney or xml value. For any other data type (including
int), the style argument is ignored. See Books Online topic "CAST and
CONVERT (Transact-SQL)" for more informations.
You probably want to use something like this:
SELECT CONVERT(varchar(2),hourminute/100)+':'
+RIGHT(CONVERT(char(3),hourminute%100+10
0),2)
FROM booking
Razvan|||Hi, Mark
Your query works only for values greater than 1000.
Here is my sample data used for testing:
CREATE TABLE booking (hourminute int)
INSERT INTO booking VALUES (1234)
INSERT INTO booking VALUES (2307)
INSERT INTO booking VALUES (0)
INSERT INTO booking VALUES (105)
INSERT INTO booking VALUES (555)
Razvan|||"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1137522307.881961.56610@.g49g2000cwa.googlegroups.com...
> Hi, Mark
> Your query works only for values greater than 1000.
> Here is my sample data used for testing:
> CREATE TABLE booking (hourminute int)
> INSERT INTO booking VALUES (1234)
> INSERT INTO booking VALUES (2307)
> INSERT INTO booking VALUES (0)
> INSERT INTO booking VALUES (105)
> INSERT INTO booking VALUES (555)
> Razvan
This is ugly, but it works:
select left(right('000' + cast(hourminute as varchar(4)), 4), 2) + ':' +
right(right('000' + cast(hourminute as varchar(4)), 4), 2) from booking|||"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1137522307.881961.56610@.g49g2000cwa.googlegroups.com...
> Hi, Mark
> Your query works only for values greater than 1000.
> Here is my sample data used for testing:
> CREATE TABLE booking (hourminute int)
> INSERT INTO booking VALUES (1234)
> INSERT INTO booking VALUES (2307)
> INSERT INTO booking VALUES (0)
> INSERT INTO booking VALUES (105)
> INSERT INTO booking VALUES (555)
> Razvan
Another one:
select STUFF(REPLICATE('0', 4-LEN(hourminute)) + CAST(hourminute as
VARCHAR(4)), 3,0,':') FROM booking|||How about this one:
SELECT STUFF(RIGHT(CAST(hourminute+10000 as CHAR(5)),4),3,0,':')
FROM booking
Razvan|||Nothing fundamentally wrong with arriving at the desired format by using the
disivion; the problem was a result of using char as opposed to varchar.
CASTING the pieces into char was adding extra spaces onto the end, which was
throwing off the result of the RIGHT function.
SELECT CAST(hourminute / 100 AS varchar(2)) + ':' + RIGHT(CAST(hourminute AS
varchar(4)),2) .
This does produce some strange results as well. With ML's supplied sample
data, a hourminute value of 0 returns 0:0 . The below cleans that up:
SELECT
CONVERT(char(10),
CAST(
CAST(hourminute / 100 AS varchar(2)) + ':' + RIGHT(CAST(hourminute AS
varchar(4)),2)
AS datetime)
, 8)
FROM booking
"Joey Martin" wrote:

> Works if there are 4 digits, but does not work if 3.
> Example:
> 730 = 7 :0
> 725 = 7 :5
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1137525849.104051.25420@.g43g2000cwa.googlegroups.com...
> How about this one:
> SELECT STUFF(RIGHT(CAST(hourminute+10000 as CHAR(5)),4),3,0,':')
> FROM booking
> Razvan
Good STUFF Razvan. :-)

Convert EXISTS to JOIN or? Reformulated!

Hello all!
Thanks for the followups. I think I was too unclear: The
function was not the focus, more an appendix to the setup.
So run the setup and the function.
My question was how to fix the query so that I can access
the matching row that makes the EXISTS true. Either via a
rewrite of the EXISTS condition using a join (which I have
attempted but ultimately failed) or something else, like
TOP 1 on the combined xfers / intervals set.
This query returns the correct result, yet I do not have
access to the (first found) matching row in the EXISTS:
-- Desired rows, yet I cannot access the matching row in
the EXISTS test
SELECT
nodes.nodeid, nodes.nodename,
source_nodeid, volume
FROM nodes
LEFT OUTER JOIN xfers
ON target_nodeid = nodes.nodeid AND EXISTS
( SELECT *
FROM intervals sourceIntervals
JOIN intervals nodeIntervals
ON
nodeIntervals.lot = sourceIntervals.lot
AND dbo.Intersection( nodeIntervals.start,
nodeIntervals.stop, sourceIntervals.start,
sourceIntervals.stop ) = 1
WHERE
sourceIntervals.nodes_nodeid = xfers.source_nodeid
AND nodeIntervals.nodes_nodeid = xfers.target_nodeid
)
LEFT OUTER JOIN nodes sourceNodes
ON sourceNodes.nodeid = source_nodeid
Here is the setup script again, nicely formatted:
http://authors.aspalliance.com/ayla...asteCodeID=4019
Here is the problem query and one of my feeble attempts:
http://authors.aspalliance.com/ayla...asteCodeID=4020
Ali: The case is nicer, thanks.
/mawi
>--Original Message--
>mawi
>What do you want to return? What is an output?
>I'd rewrite a little bit the function
>CREATE FUNCTION Intersection ( @.startA int, @.stopA int,
> @.startB int, @.stopB int )
> RETURNS bit
> AS
> BEGIN
> RETURN CASE
> WHEN @.startA BETWEEN @.startB AND @.stopB THEN
1 ELSE 0 END
> END
> go
>
>"mawi" <anonymous@.discussions.microsoft.com> wrote in message
>news:24d101c5344c$d11c2a50$a401280a@.phx.gbl...
>
>.
>mawi
You cannot specify columns as parameters to the UDF.
Perhasp you want to use a CASE expression to compare the dates.
"mawi" <anonymous@.discussions.microsoft.com> wrote in message
news:259101c53462$4ed023b0$a401280a@.phx.gbl...
> Hello all!
> Thanks for the followups. I think I was too unclear: The
> function was not the focus, more an appendix to the setup.
> So run the setup and the function.
> My question was how to fix the query so that I can access
> the matching row that makes the EXISTS true. Either via a
> rewrite of the EXISTS condition using a join (which I have
> attempted but ultimately failed) or something else, like
> TOP 1 on the combined xfers / intervals set.
> This query returns the correct result, yet I do not have
> access to the (first found) matching row in the EXISTS:
> -- Desired rows, yet I cannot access the matching row in
> the EXISTS test
> SELECT
> nodes.nodeid, nodes.nodename,
> source_nodeid, volume
> FROM nodes
> LEFT OUTER JOIN xfers
> ON target_nodeid = nodes.nodeid AND EXISTS
> ( SELECT *
> FROM intervals sourceIntervals
> JOIN intervals nodeIntervals
> ON
> nodeIntervals.lot = sourceIntervals.lot
> AND dbo.Intersection( nodeIntervals.start,
> nodeIntervals.stop, sourceIntervals.start,
> sourceIntervals.stop ) = 1
> WHERE
> sourceIntervals.nodes_nodeid = xfers.source_nodeid
> AND nodeIntervals.nodes_nodeid = xfers.target_nodeid
> )
> LEFT OUTER JOIN nodes sourceNodes
> ON sourceNodes.nodeid = source_nodeid
> Here is the setup script again, nicely formatted:
> http://authors.aspalliance.com/ayla...asteCodeID=4019
> Here is the problem query and one of my feeble attempts:
> http://authors.aspalliance.com/ayla...asteCodeID=4020
>
> Ali: The case is nicer, thanks.
> /mawi
> 1 ELSE 0 END|||
>--Original Message--
>mawi
>You cannot specify columns as parameters to the UDF.
>Perhasp you want to use a CASE expression to compare the
dates.
True. But nevermind the function, it is just a shorthand
and works fine as it is.
How about that query, any ideas?

http://authors.aspalliance.com/ayla...asteCodeID=4019
http://authors.aspalliance.com/ayla...asteCodeID=4020
message
have many
gotten
INT,
VARCHAR(3),
>
>.
>|||mawi
There will be some ideas if post desired output.
<anonymous@.discussions.microsoft.com> wrote in message
news:0bef01c53469$caddc870$a501280a@.phx.gbl...
>
> dates.
> True. But nevermind the function, it is just a shorthand
> and works fine as it is.
> How about that query, any ideas?
>
> http://authors.aspalliance.com/ayla...asteCodeID=4019
> http://authors.aspalliance.com/ayla...asteCodeID=4020
> message
> have many
> gotten
> INT,
> VARCHAR(3),