converting boolean to bit

I have a control with checkboxes. The checkbox.checked property returns a boolean value. If I want to insert a record into a table that has a corresponding column of type bit, how do I do it?

I tried

CType(myChkBox.checked,String)

, which returns the strings "True" or "False". But, I get the error

The name 'True' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

I then tried

"CAST(" & CType(myChkBox.checked,String) & ",bit)"

, but got the same error.

I can write a function that will return a 1 or a 0 but that seems ludicrous. Surely there must be a more elegant way to use the result of a checkbox in an SQL statement.

Thanks
Martindoes this work ?

IIf(CheckBox1.Checked = True, 1, 0)

|||There is, you should be using parameters not on-the-fly sql strings.|||pkr, I agree that I should be using parameters, and I will get around to it before I finalise this control. I am not in front of my code at the moment, do you think that

dim prmChecked as new SqlParameter("@.myParam",SqlDbType.Bit)
prmChecked = myChkBox.checked

will work? According to the doco, the SqlDataType Bit takes an unsigned integer of 1 or 0. So, I would still have the same problem.

ndinaker, thanks for your suggestion. Is this the way its usually done?

Martin|||The checked flag is a boolean. Even *if* it doesn't automatically convert boolean to bit, it's trivial to convert boolean to int.

Converting a value

I have a select statement like this:

select IsVerified from AppForm

the IsVerified returns 'True' ...can I convert that value to 'Yes' by using some sort of function?

SELECT IsVerified=CASE WHEN True THEN 'Yes' ELSE 'No' END from AppForm|||excellent, thanks!

Convert(datetime, '07/11/48') Returns Year 2048!

Hello all! I'm having some issues trying to convert a varchar string
into datetime.
What I'm wanting to do is take the string '07/11/48' and convert it to
datetime, i.e.: 1948-07-11 00:00:00.000, but the convert function is
returning 2048 as the year! The function I'm using is:
SELECT CONVERT(DateTime, '07/11/48') AS Birth_Date
In addition to the wrong year, I'm also getting an error when trying to
apply the 101 style to the above function. The error is, "Syntax error
converting datetime from character string," and all I'm doing is adding
the ", 101" style number into the function like this:
SELECT CONVERT(DateTime, '07/11/48', 101) AS Birth_Date
I've searched and searched the groups and various other places and
can't find the solution. Can someone please help?!
Thank you =)Direct from BOL for SQL 2000:
(search CONVERT; it's the first hit).
Quote:
"By default, SQL Server interprets two-digit years based on a cutoff year of
2049. That is, the two-digit year 49 is interpreted as 2049 and the
two-digit year 50 is interpreted as 1950. Many client applications, such as
those based on OLE Automation objects, use a cutoff year of 2030. SQL Server
provides a configuration option (two digit year cutoff) that changes the
cutoff year used by SQL Server and allows the consistent treatment of dates.
The safest course, however, is to specify four-digit years."
James Hokes
<crferguson@.gmail.com> wrote in message
news:1147963833.086980.150920@.38g2000cwa.googlegroups.com...
> Hello all! I'm having some issues trying to convert a varchar string
> into datetime.
> What I'm wanting to do is take the string '07/11/48' and convert it to
> datetime, i.e.: 1948-07-11 00:00:00.000, but the convert function is
> returning 2048 as the year! The function I'm using is:
> SELECT CONVERT(DateTime, '07/11/48') AS Birth_Date
> In addition to the wrong year, I'm also getting an error when trying to
> apply the 101 style to the above function. The error is, "Syntax error
> converting datetime from character string," and all I'm doing is adding
> the ", 101" style number into the function like this:
> SELECT CONVERT(DateTime, '07/11/48', 101) AS Birth_Date
> I've searched and searched the groups and various other places and
> can't find the solution. Can someone please help?!
> Thank you =)
>|||Try Books Online, under the CONVERT syntax:
Important By default, SQL Server interprets two-digit years based on a
cutoff year of 2049. That is, the two-digit year 49 is interpreted as
2049 and the two-digit year 50 is interpreted as 1950. Many client
applications, such as those based on OLE Automation objects, use a
cutoff year of 2030. SQL Server provides a configuration option (two
digit year cutoff) that changes the cutoff year used by SQL Server and
allows the consistent treatment of dates. The safest course, however,
is to specify four-digit years.
HTH,
Stu|||Thanks both of you! I'm going to have to find a work-around for it
then as I can't change the configuration on the SQL server. Also, I'm
going to have to find the books online you both quoted!|||crferguson@.gmail.com wrote on 18 May 2006 07:50:33 -0700:

> Hello all! I'm having some issues trying to convert a varchar string
> into datetime.
> What I'm wanting to do is take the string '07/11/48' and convert it to
> datetime, i.e.: 1948-07-11 00:00:00.000, but the convert function is
> returning 2048 as the year! The function I'm using is:
> SELECT CONVERT(DateTime, '07/11/48') AS Birth_Date
> In addition to the wrong year, I'm also getting an error when trying to
> apply the 101 style to the above function. The error is, "Syntax error
> converting datetime from character string," and all I'm doing is adding
> the ", 101" style number into the function like this:
> SELECT CONVERT(DateTime, '07/11/48', 101) AS Birth_Date
> I've searched and searched the groups and various other places and
> can't find the solution. Can someone please help?!
> Thank you =)
In addition to the the other replies, the reason for the syntax error is
that 101 requires a 4 digit year, and you're passing in 2 digits. When
converting to a DateTime, the style value defines the format of the data
being converted to the DateTime.
Dan|||Thanks both of you! I'm going to have to find a work-around for it
then as I can't change the configuration on the SQL server. Also, I'm
going to have to find the books online you both quoted!|||Yeah, I feel dumb =P I was alreadly looking at the books online not
realizing that's what it was called. I skipped right over the
"Important!" note about the date cutoff... =/|||Ahh! Thanks Dan! I wasn't aware of the 4 digit requirement. I really
need to read more about these things before I fumble around so much,
but I'm on such a tight time crunch that I didn't have time to do more
than skim through everything. Thanks again for your help everyone!|||You can change it using CASE:
DECLARE @.Date varchar(8)
SET @.Date = '07/11/01'
SELECT CASE WHEN YEAR(@.Date) > YEAR(GETDATE())
THEN DATEADD(yyyy, -100, CONVERT(smalldatetime, @.Date))
ELSE CONVERT(smalldatetime, @.Date) END
untested.
The idea is that you are looking at people who are less than 100 years
old, and they couldn't have been born in the future.
HTH,
Stu|||crferguson@.gmail.com wrote on 18 May 2006 08:10:32 -0700:

> Ahh! Thanks Dan! I wasn't aware of the 4 digit requirement. I really
> need to read more about these things before I fumble around so much,
> but I'm on such a tight time crunch that I didn't have time to do more
> than skim through everything. Thanks again for your help everyone!
Go to the BOL entry for CONVERT, find 101 in the style column, look at the
top of the column where it specifies it's need a 4 digit year (yyyy), look
to the column to the left to find the style value for a 2 digit year (yy).
:)
Dansqlsql

convert value to 2 digits after decimal

I'm having a query as follows:
SELECT 41.78 * 0.01 * 17 + 41.78 AS new_price
it returns 48.8826, but how do I set it up that it would round it nicely to two digits? as of 48.88
THANKS!In Oracle you would use ROUND(value,2).|||Just an observation, but rounding is really a client-side issue. In all but a few cases, it should really be handled by the client instead of the server.

Most servers will accept the:

SELECT Round(41.78 * 0.01 * 17 + 41.78, 2) AS new_price

syntax, but you still should do the rounding at the client if you can. Someday you might decide that you want the "full value", or maybe you'll need to round at a different precision than 2 for some clients... Not that I've ever had that happen, I just read about it in a book once. ;)

-PatP

Convert to Julian Date

In Oracle, we can write query to convert current date to Julian date
SELECT TO_CHAR(TRUNC(SYSDATE),'J') FROM DUAL;
This returns the value 2453754
I think there is no function in SQL Server to convert to Julian date
How do I use the same logic in SQL Server?
MadhivananMadhivanan wrote:
> In Oracle, we can write query to convert current date to Julian date
> SELECT TO_CHAR(TRUNC(SYSDATE),'J') FROM DUAL;
> This returns the value 2453754
> I think there is no function in SQL Server to convert to Julian date
> How do I use the same logic in SQL Server?
> Madhivanan
Try this:
DECLARE @.dt DATETIME ;
SET @.dt = CURRENT_TIMESTAMP ;
SELECT
DATEDIFF(DAY,'17530101',@.dt) +2361330.5
+DATEDIFF(SECOND,DATEADD(DAY,
DATEDIFF(DAY,'17530101',@.dt),'17530101')
,@.dt)/86400.00
AS julian_date ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||CORRECTION. Use SET @.dt = GETUTCDATE() instead of CURRENT_TIMESTAMP.
Julian dates are always expressed relative to UTC and not local time.
If you supply your own date and time you'll have to convert it to UTC
yourself.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
DECLARE @.JulianDate AS INT
select @.JulianDate = 2415021 + datediff(dd, convert(datetime,
'1900-01-01',103),getdate())
PRINT @.JulianDate
This will give the julian date and to get back the current data,
select dateadd(dd, @.JulianDate-2415021, convert(datetime, '1900-01-01',103
))
Thanks
Sree
"Madhivanan" wrote:

> In Oracle, we can write query to convert current date to Julian date
> SELECT TO_CHAR(TRUNC(SYSDATE),'J') FROM DUAL;
> This returns the value 2453754
> I think there is no function in SQL Server to convert to Julian date
> How do I use the same logic in SQL Server?
> Madhivanan
>|||Thans David,
You used the value 17530101. Is this the number of days between January
1, 4713 BC and January 1, 1900?
Sree,
What does the value 2415021 refer to?
Madhivanan|||In SQL Server the datatype DateTime can store dates in the range - January
1, 1753 through December 31, 9999
HTH. Ryan
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1137583289.974927.92200@.g44g2000cwa.googlegroups.com...
> Thans David,
> You used the value 17530101. Is this the number of days between January
> 1, 4713 BC and January 1, 1900?
> Sree,
> What does the value 2415021 refer to?
> Madhivanan
>|||Madhivanan wrote:
> Thans David,
> You used the value 17530101. Is this the number of days between January
> 1, 4713 BC and January 1, 1900?
> Sree,
> What does the value 2415021 refer to?
> Madhivanan
'17530101' just represents January 1, 1753, which is just the earliest
date supported by SQL Server. I used that as a base date for
calculating the Julian date. 2361330.5 is the Julian date for midnight
on 1753-01-01.
In Sree's case 2415021 is the Julian date for 12 noon on 1900-01-01.
Sree's calculation ignores the time portion of the current time and
returns the Julian day number for 12 noon on that day.
As I explained in my correction, Julian dates refer to UTC (GMT) time
so it's not really correct to use GETDATE() or CURRENT_TIMESTAMP as the
input to this calculation. You can use GETUTCDATE instead. In practice
if you are only interested in the integer day number then whether you
choose to base it on local time or UTC may depend on your requirements
and on how the value is to be used.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Its the Julian Date equivalent to "1900-01-01 00:00:00.000".
Julian date is the number of days that have elapsed since "1900-01-01
00:00:00.000". So the logic here used is simple, add Julian equivalent of
"1900-01-01 00:00:00.000" ie "2415021" to number of days from "1900-01-01
00:00:00.000" to current data(getdate()).
Hope you got the idea.
Thanks,
Sree
"Madhivanan" wrote:

> Thans David,
> You used the value 17530101. Is this the number of days between January
> 1, 4713 BC and January 1, 1900?
> Sree,
> What does the value 2415021 refer to?
> Madhivanan
>|||Thanks David and Sree
How did you calculate the value 2361330.5 for the date 1753-01-01?
Madhivanan|||Madhivanan wrote:
> Thanks David and Sree
> How did you calculate the value 2361330.5 for the date 1753-01-01?
> Madhivanan
http://aa.usno.navy.mil/data/docs/JulianDate.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Convert returned values

I have a query where one of the fields returns a 0 or 1. I'd like the
zero's to show on the report as a "No" and the one's be a "Yes". Does
anyone know how to do this?
Thanks,
ColinIn Expression Editor
=IIF(Fields!ColumnName=1,"Yes","No")
--
THANKS & PLEASE RATE THE POSTING.
--RAVI--
"Colin" wrote:
> I have a query where one of the fields returns a 0 or 1. I'd like the
> zero's to show on the report as a "No" and the one's be a "Yes". Does
> anyone know how to do this?
> Thanks,
> Colin
>
>

convert positive and negative numbers

I have a sales report where sales dollars are negative and credits and returns show as positive numbers. I would like to change the negative numbers to positive numbers and the positive numbers to negative numbers.
Thank you for any help.Originally posted by DeannaUTI
I have a sales report where sales dollars are negative and credits and returns show as positive numbers. I would like to change the negative numbers to positive numbers and the positive numbers to negative numbers.

Thank you for any help.

Hi,

1. Convert negative value to positive

abs(myvar)

2. Convert positive value to negative

myvar - (myvar * 2)

yours friendly,
K.Babu

Convert Null Values to 0

Hi

I've got a view which returns Null values due to an left outer join. The field which sometimes returns Nulls is called ClientCount. I want to create a new field which displays the value of ClientCount if ClientCount is not null and 0 if it is null.

In MS Access I use the following: IIF([ClientCount] Is Null,0,[ClientCount])

This does not seem to work in MS SQL. Is there some equivalent function I could use?

Thanks

David

David:

The best way to convert a null to a zero is to use ISNULL( [Client Count], 0 ) or COALESCE( [Client Count], 0 ).

|||How to convert null to zero ina query.

Convert Null Values to 0

Hi

I've got a view which returns Null values due to an left outer join. The field which sometimes returns Nulls is called ClientCount. I want to create a new field which displays the value of ClientCount if ClientCount is not null and 0 if it is null.

In MS Access I use the following: IIF([ClientCount] Is Null,0,[ClientCount])

This does not seem to work in MS SQL. Is there some equivalent function I could use?

Thanks

David

David:

The best way to convert a null to a zero is to use ISNULL( [Client Count], 0 ) or COALESCE( [Client Count], 0 ).

|||How to convert null to zero ina query.

Convert Function

Dear Friends,

I have a OLEDB Source and on it I have a convert(datetime,GetDate() ,102) AS RKData.

The field RKDATA returns me the value 2007-05-02 11:23:00...

How can I return only the date without time? Like:

2007-05-02 00:00:00...

Hi PedroCGD,

Either of these should work,

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))) as RKData

or

SELECT CAST(CAST(YEAR(GetDate()) AS VARCHAR(4)) + '/' + CAST(MONTH(GetDate()) AS VARCHAR(2)) + '/' + CAST(DAY(GetDate()) AS VARCHAR(2)) AS DATETIME) as RKData

|||Add a derived column transformation to the data flow and use the following expression:

(DT_DBTIMESTAMP)(DT_DBDATE)([RKDATA])|||

Subhash,

It works. And How can I do If I want to increment 15 days to the getdate?

Thanks!

|||

PedroCGD wrote:

Subhash,

It works. And How can I do If I want to increment 15 days to the getdate?

Thanks!

Use the dateadd() function either in your query or in a derived column.

Just so you know, technically, your question should be in the Transact-SQL forum. That is the reason I've given you SSIS methods of solving your question -- to tie it to SSIS.|||

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()+15))) as RKData

resolve my question... dont need derivated column... I can include in SQL Statment of OLEDBSource

Thanks!

|||

PedroCGD wrote:

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()+15))) as RKData

resolve my question... dont need derivated column... I can include in SQL Statment of OLEDBSource

Thanks!

This is a more appropriate statement because it works with date routines and is, frankly, more readable:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(20), DATEADD("d",15,GETDATE()),101))
|||

in the datareader source... it works if the database is SQL Server, but in my case the database is SYBASE and I use ODBC in datareader...

The statment:

SELECT Folders.Folders_ShortName, Bonds.Bonds_Name, Currencies.Currencies_ShortName, BondsDeals.CaptureDate,
BondsDeals.ValueDate, BondsDeals.Quantity, BondsDeals.DealType

FROM (((Bonds INNER JOIN BondsDeals ON Bonds.Bonds_Id = BondsDeals.Bonds_Id)
INNER JOIN Folders ON BondsDeals.Folders_Id = Folders.Folders_Id)
INNER JOIN Currencies ON Bonds.Currencies_Id = Currencies.Currencies_Id)

WHERE BondsDeals.CaptureDate<CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()+1)))
AND Folders.Folders_ShortName Not Like 'FID%'
AND Folders.Folders_ShortName Not Like 'FPS%'
AND Folders.Folders_ShortName Not Like 'VDR%'

The error:

"...An error occurred executing the provider SQL command."

"... INT is incompatible with DATETIME..."

|||

Yes, I think your tip is better... but my datareader (use to link via ODBC to SYBASE) doesnt work... if the database is SQL works, but in link to SYBASE, teh DataReader Source doesnt recognize DateAdd...

Do you have tips?