converting a float to a varchar _without_E syntax?

I have to output a column in a very specific format:
single quote integer number single quote
like this:
'100000'
But the data is in a float. When I do:
'''' + CAST(quantity as varchar) + ''''
I get:
'2e+007'
which is useless. I can't figure out the trick to telling SQL Server not to
use e notation. Is there a way?
MauryCAST(CAST(quantity as integer) as varchar)
Providing it casts to integer without overflowing.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6EAD6EF9-8ED7-44D3-897C-EFB3B995589C@.microsoft.com...
>I have to output a column in a very specific format:
> single quote integer number single quote
> like this:
> '100000'
> But the data is in a float. When I do:
> '''' + CAST(quantity as varchar) + ''''
> I get:
> '2e+007'
> which is useless. I can't figure out the trick to telling SQL Server not
> to
> use e notation. Is there a way?
> Maury|||"Russell Fields" wrote:
> CAST(CAST(quantity as integer) as varchar)
> Providing it casts to integer without overflowing.
Yikes!
Is it just me or would we all be a lot better off if MS put some time into
painfully obvious basic functionality like CONVERT instead of gee-wiz
features none of us actually use?
Maury|||Maury,
Maybe, but in cases like this the question is always: What do you expect as
your answer?
You wanted an integer this time, but at other times you might have wanted
the E notation. The code snippet made your intention explicit by saying 1)
make the float into an integer, then 2) make the integer into a string.
Interestingly, the CONVERT function offers 'styles', but I don't think any
absolutely matched your need to be an integer. You could try:
SELECT CONVERT(VARCHAR(15),floatquantity, 0)
But the definition of style 0 is "A maximum of 6 digits. Use in scientific
notation, when appropriate", so you might get 1234 or 123.4 or 1.234E9 all
depending on the value.
It seems that, in this case, you are really asking for some more flexible
styles for conversion. A good place for making such suggestions is at:
https://connect.microsoft.com/SQLServer. I found one suggestion like this
posted by Adam Machanic back in 2005 and "closed by design" by Microsoft.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126338
But you could raise it again.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:25B21A42-7E78-423C-A51A-0E79294BF6CF@.microsoft.com...
> "Russell Fields" wrote:
>> CAST(CAST(quantity as integer) as varchar)
>> Providing it casts to integer without overflowing.
> Yikes!
> Is it just me or would we all be a lot better off if MS put some time into
> painfully obvious basic functionality like CONVERT instead of gee-wiz
> features none of us actually use?
> Maury

Convert HH,MM to decimal ?

I have a table with a specific column that i get from an AS/400
The column holds worktime specified in HH,MM format. How do i convert
that do a decimal number.
ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75.
Thankskjo007@.hotmail.com wrote:

> ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75.
select datepart(hour, '2005-01-01 18:45') + cast(datepart(minute,
'2005-01-01 07:45 AM') as decimal) / 60
HTH,
Stijn Verrept.|||>> ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75.
>
> select datepart(hour, '2005-01-01 18:45') + cast(datepart(minute,
> '2005-01-01 07:45 AM') as decimal) / 60
I don't think that will work as "HH,MM" is not a recognised Sql date format
and therefore the date functions you use will not return the expected data..

> HTH,
> Stijn Verrept.
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter wrote:

> I don't think that will work as "HH,MM" is not a recognised Sql date
> format and therefore the date functions you use will not return the
> expected data..
? What do you mean? I tried it out, it works without problems. He
wants the time converted to decimal and that's what this does:
select datepart(hour, '2005-01-01 07:45 AM') + cast(datepart(minute,
'2005-01-01 07:45 AM') as decimal) / 60
Kind regards,
Stijn Verrept.|||
> ? What do you mean? I tried it out, it works without problems. He
> wants the time converted to decimal and that's what this does:
> select datepart(hour, '2005-01-01 07:45 AM') + cast(datepart(minute,
> '2005-01-01 07:45 AM') as decimal) / 60
> --
> Kind regards,
> Stijn Verrept.
>
He said he is receiving the data from AS/400 in the format "HH,MM" and gave
the example "7,45"
Substituting that into your solution gives...
select datepart(hour, '7,45') + cast(datepart(minute, '7,45') as decimal) /
60
... which doesn't work!
Regards
Peter
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||<kjo007@.hotmail.com> wrote in message
news:1139314990.653609.216860@.f14g2000cwb.googlegroups.com...
>I have a table with a specific column that i get from an AS/400
> The column holds worktime specified in HH,MM format. How do i convert
> that do a decimal number.
> ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75.
> Thanks
declare @.time varchar(5)
set @.time = '7,45'
select cast(left(@.time, charindex(',', @.time)-1) + (right(@.time,
len(@.time) - charindex(',', @.time)))/60.0 as decimal(5,2))|||It may be worth creating a function for this logic if it is needed in more
than one place.
usd_ConvertAS400Time(7,45) will be easier to type, read, and maintain.
However, there may be a performance hit, I'm not really sure.
Then again, changing the column and your AS400 import to store the value
differently may remove more headaches, depending on if you ever use the data
in the current format.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23ZoNr3$KGHA.2276@.TK2MSFTNGP15.phx.gbl...
> <kjo007@.hotmail.com> wrote in message
> news:1139314990.653609.216860@.f14g2000cwb.googlegroups.com...
> declare @.time varchar(5)
> set @.time = '7,45'
> select cast(left(@.time, charindex(',', @.time)-1) + (right(@.time,
> len(@.time) - charindex(',', @.time)))/60.0 as decimal(5,2))
>|||Peter wrote:

> He said he is receiving the data from AS/400 in the format "HH,MM"
> and gave the example "7,45"
> Substituting that into your solution gives...
> select datepart(hour, '7,45') + cast(datepart(minute, '7,45') as
> decimal) / 60
> ... which doesn't work!
Very true, my bad!
HTH,
Stijn Verrept.|||Of course, either way there could be a performance hit.
If he cannot change the table structure...
it may be better (easier/faster) for the poster to do the conversion client
side..
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:ejySWSALGHA.532@.TK2MSFTNGP15.phx.gbl...
> It may be worth creating a function for this logic if it is needed in more
> than one place.
> usd_ConvertAS400Time(7,45) will be easier to type, read, and maintain.
> However, there may be a performance hit, I'm not really sure.
> Then again, changing the column and your AS400 import to store the value
> differently may remove more headaches, depending on if you ever use the
> data
> in the current format.
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:%23ZoNr3$KGHA.2276@.TK2MSFTNGP15.phx.gbl...
>|||maybe this:
select replace('7,45', ',', ':')
7:45

Convert datetime to char/nvarchar or the opposite (in specific for

I have a table with a column name Tdate, data type datetime, and length 8.
The look of the existing data in this column is from this format
DD/MM/YYYY HH: MI: SS.
I want to pass to a stored procedure string and another data that will
update a specific row, where the string I pass is equal to the value in the
column.
That’s why I want to convert the value of the column from datetime to
nvarchar.
My problem is I do not find the date format I wrote above – I searched in
sql books online and over the Internet.
I tried the following code (because it looks the most similar to the format)
and more others.
CONVERT(CHAR(30), Tdae, 131)='11/05/2006 12:20:35'
Can you please tell me how can I convert the datetime to nvarchar that will
look like that format?
Or – convert the string that looks like the format above to datetime from
that format.
Thanks in advance!Hi,
Converting datetime to varchar for comparison is not a good option.
But you can convert varchar to datetime.
Can't you use somthing like this for comparison?
Tdae = cast('11/05/2006 12: 20: 35' as datetime)
Or have I understood the question wrong?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Convert it the other way around instead. See http://www.karaszi.com/SQLServer/in...
_datetime.asp for
elaboration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niron kag" <Nironkag@.discussions.microsoft.com> wrote in message
news:A082F020-4217-4571-9E52-C6A8F91E65F8@.microsoft.com...
>I have a table with a column name Tdate, data type datetime, and length 8.
> The look of the existing data in this column is from this format
> DD/MM/YYYY HH: MI: SS.
> I want to pass to a stored procedure string and another data that will
> update a specific row, where the string I pass is equal to the value in th
e
> column.
> That’s why I want to convert the value of the column from datetime to
> nvarchar.
> My problem is I do not find the date format I wrote above – I searched i
n
> sql books online and over the Internet.
> I tried the following code (because it looks the most similar to the forma
t)
> and more others.
> CONVERT(CHAR(30), Tdae, 131)='11/05/2006 12:20:35'
> Can you please tell me how can I convert the datetime to nvarchar that wil
l
> look like that format?
> Or – convert the string that looks like the format above to datetime fro
m
> that format.
> Thanks in advance!
>

Convert datetime to char/nvarchar or the opposite (in specific

Omnibuzz,
In the beginning of my code, I take the date from the table and its contents
(in c#) is: "11/05/2006 12:20:35"”.
When I want to pass it to sql the real value of the column there (from the
query analyzer) is:
2006-05-11 12:20:35.487
So when I query:
TDate = cast('11/05/2006 12:20:35' as datetime)
They not equal.
Do I need to separate the year month seconds and more and than to compare
them?
Or there is other way?
"Omnibuzz" wrote:

> Hi,
> Converting datetime to varchar for comparison is not a good option.
> But you can convert varchar to datetime.
> Can't you use somthing like this for comparison?
> Tdae = cast('11/05/2006 12: 20: 35' as datetime)
> Or have I understood the question wrong?
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>Hi,
When you are sending it from the C# code to the SP, format the date as
follows.
DateValue.ToString("yyyy-MM-dd hh:mm:ss.fff")
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi Omnibuzz,
First thank you, I tried to run it but it does not compile.
The two error messages are:
1)The best overloaded method match for ‘string.tostring
(system.iformatprovider)’ has some invalid arguments.
2)Argument ‘1’: cannot convert from string to system.iformatprovider.
"Omnibuzz" wrote:

> Hi,
> When you are sending it from the C# code to the SP, format the date as
> follows.
> DateValue.ToString("yyyy-MM-dd hh:mm:ss.fff")
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||You should be format the date variable.
Something like this..
DateTime dt
dt = System.DateTime.Now
dt.ToString("yyyy-MM-dd hh:mm:ss.fff")
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Omnibuzz thank you , in the end I solve it like this:
triggerTime = yearTriggerDate + "-" + monthTriggerDate + "-" +
dayTriggerDate + " " + hoursTriggerDate + ":" + minutesTriggerDate + ":" +
secondsTriggerDate;
and then pass the variable.
"Omnibuzz " wrote:

> You should be format the date variable.
> Something like this..
> DateTime dt
> dt = System.DateTime.Now
> dt.ToString("yyyy-MM-dd hh:mm:ss.fff")
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>