Does anyone have a VB function they could pass along for
converting a date to the end of the last month?
I have a report that will be run for the period ending the prior
month and want the parameter date to default as follows:
Assum they are running it on 12/21, date should default to 11/30/2005
Thanks!"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
@.TK2MSFTNGP15.phx.gbl:
> Does anyone have a VB function they could pass along for
> converting a date to the end of the last month?
> I have a report that will be run for the period ending the prior
> month and want the parameter date to default as follows:
> Assum they are running it on 12/21, date should default to 11/30/2005
> Thanks!
>
>
DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)|||Outstanding, many thanks!
"Asher_N" <compguy666@.hotmail.com> wrote in message
news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)|||Asher
I am using timedate format; can it be set to the end of the month 11:59 pm?
"Asher_N" <compguy666@.hotmail.com> wrote in message
news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)|||If your using 2005, you use my misc date project at
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
hour:minute:second:millisecond of that day (i.e. adding one more ms would
move the date to start of next day.)
--
William Stacey [MVP]
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%235%236n4nBGHA.216@.TK2MSFTNGP15.phx.gbl...
> Asher
> I am using timedate format; can it be set to the end of the month 11:59
> pm?
> "Asher_N" <compguy666@.hotmail.com> wrote in message
> news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
>> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
>>
>> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)
>|||William
Thanks for your reply but I an still on RS 2000
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:eNviqHpBGHA.3896@.TK2MSFTNGP09.phx.gbl...
> If your using 2005, you use my misc date project at
> http://channel9.msdn.com/ShowPost.aspx?PostID=147390
> Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
> hour:minute:second:millisecond of that day (i.e. adding one more ms would
> move the date to start of next day.)
> --
> William Stacey [MVP]
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:%235%236n4nBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Asher
>> I am using timedate format; can it be set to the end of the month 11:59
>> pm?
>> "Asher_N" <compguy666@.hotmail.com> wrote in message
>> news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
>> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
>>
>> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)
>>
>|||I missed the VB function need. Here is a c# method you can convert:
public static DateTime GetEndOfMonth(DateTime date)
{
int daysInMonth = DateTime.DaysInMonth(date.Year, date.Month);
return new DateTime(date.Year, date.Month, daysInMonth, 23, 59,
59, 999);
}
private void button9_Click(object sender, EventArgs e)
{
DateTime date = DateTime.Parse("12/22/2005"); // Any date.
DateTime lastMth = date.AddMonths(-1);
DateTime endOfLast = GetEndOfMonth(lastMth);
Console.WriteLine("End of Last Month:" + endOfLast);
}
--
William Stacey [MVP]
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:unxvFipBGHA.3496@.TK2MSFTNGP11.phx.gbl...
> William
> Thanks for your reply but I an still on RS 2000
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:eNviqHpBGHA.3896@.TK2MSFTNGP09.phx.gbl...
>> If your using 2005, you use my misc date project at
>> http://channel9.msdn.com/ShowPost.aspx?PostID=147390
>> Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
>> hour:minute:second:millisecond of that day (i.e. adding one more ms would
>> move the date to start of next day.)
>> --
>> William Stacey [MVP]
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:%235%236n4nBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Asher
>> I am using timedate format; can it be set to the end of the month 11:59
>> pm?
>> "Asher_N" <compguy666@.hotmail.com> wrote in message
>> news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
>> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
>>
>> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)
>>
>>
>
Showing posts with label converting. Show all posts
Showing posts with label converting. Show all posts
Thursday, March 29, 2012
Wednesday, March 7, 2012
Find floats with exponential notation
I'm having trouble identifying a column that is a float data type that uses
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
Matt
The float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>
|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt
|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
>
> I understand that, but I still need to identify those that display using exp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decimal
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows where
> that field is exponential notation. So, if there is ANY way to determine if
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation whether
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt
|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.
>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I understand that, but I still need to identify those that display using exp
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decimal
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows where
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whether
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
Matt
The float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>
|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt
|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
>
> I understand that, but I still need to identify those that display using exp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decimal
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows where
> that field is exponential notation. So, if there is ANY way to determine if
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation whether
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt
|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.
>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I understand that, but I still need to identify those that display using exp
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decimal
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows where
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whether
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>
Find floats with exponential notation
I'm having trouble identifying a column that is a float data type that uses
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
MattThe float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
> > The size at which a float is displayed using the E notation is built
> > into the front end program performing the display. It is not an
> > attribute of SQL Server.
> I understand that, but I still need to identify those that display using exp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decimal
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows where
> that field is exponential notation. So, if there is ANY way to determine if
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation whether
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.
>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>> The size at which a float is displayed using the E notation is built
>> into the front end program performing the display. It is not an
>> attribute of SQL Server.
>I understand that, but I still need to identify those that display using exp
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decimal
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows where
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whether
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
MattThe float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
> > The size at which a float is displayed using the E notation is built
> > into the front end program performing the display. It is not an
> > attribute of SQL Server.
> I understand that, but I still need to identify those that display using exp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decimal
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows where
> that field is exponential notation. So, if there is ANY way to determine if
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation whether
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.
>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>> The size at which a float is displayed using the E notation is built
>> into the front end program performing the display. It is not an
>> attribute of SQL Server.
>I understand that, but I still need to identify those that display using exp
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decimal
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows where
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whether
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>
Find floats with exponential notation
I'm having trouble identifying a column that is a float data type that uses
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
MattThe float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
>
> I understand that, but I still need to identify those that display using e
xp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decim
al
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into
a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows whe
re
> that field is exponential notation. So, if there is ANY way to determine i
f
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation wheth
er
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.
>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I understand that, but I still need to identify those that display using ex
p
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decima
l
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows wher
e
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whethe
r
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
MattThe float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
>
> I understand that, but I still need to identify those that display using e
xp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decim
al
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into
a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows whe
re
> that field is exponential notation. So, if there is ANY way to determine i
f
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation wheth
er
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.
>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I understand that, but I still need to identify those that display using ex
p
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decima
l
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows wher
e
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whethe
r
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>
Subscribe to:
Posts (Atom)