Char and Varchar: format

Showing posts with label format. Show all posts

Thursday, March 22, 2012

Check if fieldname exists?

This is a multi-part message in MIME format.
--=_NextPart_000_001F_01C4A64F.273B6E50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Can I do a check if a field name exists like in an expression:
iif(Fields!Time_Month.Value,,)
iif(Exists(Fields!Time_Month.Value,,))
--=_NextPart_000_001F_01C4A64F.273B6E50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Can I do a check if a field name exists = like in an expression:

iif(Fields!Time_Month.Value,,)
iif(Exists(Fields!Time_Month.Value,,))

--=_NextPart_000_001F_01C4A64F.273B6E50--Only field names defined in the RDL file will be accessible in the Fields
collection. If a Field defined in the RDL is not returned by the data source
query, the IsMissing property of this field will be true:
=iif(Fields!xxx.IsMissing, "Field is not returned by query", ...)
More information about the Fields collection can be found in BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_7ilv.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:%230Ue0nopEHA.3396@.tk2msftngp13.phx.gbl...
Can I do a check if a field name exists like in an expression:
iif(Fields!Time_Month.Value,,)
iif(Exists(Fields!Time_Month.Value,,))|||This is a multi-part message in MIME format.
--=_NextPart_000_0044_01C4A6D7.AE802670
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Ok, I see that, but the problem comes when I try to use that field
=3Diif(Fields!xxx.IsMissing, "Field is not returned by query", "Returned =field is"& Fields!xxx.value)
If the field is there, I get
Returned field is (value)
If the field isn't there I don't get anything back.
I just get a field is missing error
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message =news:uv2elNppEHA.1992@.TK2MSFTNGP09.phx.gbl...
> Only field names defined in the RDL file will be accessible in the =Fields
> collection. If a Field defined in the RDL is not returned by the data =source
> query, the IsMissing property of this field will be true:
> =3Diif(Fields!xxx.IsMissing, "Field is not returned by query", ...)
> > More information about the Fields collection can be found in BOL:
> =http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/rscrea=
te/htm/rcr_creating_expressions_v1_7ilv.asp
> > --
> This posting is provided "AS IS" with no warranties, and confers no =rights.
> > > > "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> news:%230Ue0nopEHA.3396@.tk2msftngp13.phx.gbl...
> Can I do a check if a field name exists like in an expression:
> > iif(Fields!Time_Month.Value,,)
> iif(Exists(Fields!Time_Month.Value,,))
> >
--=_NextPart_000_0044_01C4A6D7.AE802670
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Ok, I see that, but the problem comes =when I try to use that field
=3Diif(Fields!xxx.IsMissing, "Field is =not returned by query", "Returned field is"& Fields!xxx.value)
If the field is there, I get
Returned field is (value)
If the field isn't there I don't get =anything back.
I just get a field is missing =error
"Robert Bruckner [MSFT]" wrote in message news:uv2elNppEHA.1992@.TK2MSFTNGP09.phx.gbl...> =Only field names defined in the RDL file will be accessible in the Fields> =collection. If a Field defined in the RDL is not returned by the data source> =query, the IsMissing property of this field will be true:> =3Diif(Fields!xxx.IsMissing, "Field is not returned by query", =...)> > More information about the Fields collection can be found in BOL:>http://msdn.microsoft.com/library/default.asp?url=3D/library/en-=us/rscreate/htm/rcr_creating_expressions_v1_7ilv.asp> > --> This posting is provided ="AS IS" with no warranties, and confers no rights.> > > > ="Cindy Lee" =wrote in message> news:%230Ue0nopEHA.3396@.tk2msftngp13.phx.gbl...> Can I do a check if a field name exists like in an expression:> > iif(Fields!Time_Month.Value,,)> iif(Exists(Fields!Time_Month.Value,,))> > =

--=_NextPart_000_0044_01C4A6D7.AE802670--

Tuesday, March 20, 2012

check if cast is possible

I use to import data from DBF Clipper databases into SQL Server. When a table is just imported its date fields have string format. I need to copy their data to tables of database where they ahve to be converted into date. Direct operator INSERT doesn't convert properly (I've not successed in changing default date format so it'll be covertable) but using CAST I can get result of strings like 13.05.1970 0:00:00 as datetime type. But not all records can be coverted this way. For ones can't be converted I've solved to make NULL fields there. But I don't know how to make CAST operation return NULL when convertion isn't possible. The query
INSERT INTO people_temp
(reg_num, surname, stname, patronymic, foreing, gender, birthdate, fam_pos, dwell_type, children, nation, par_not, region, stud_fml, parn_fml,
com_prob, sp_prob, sn_passport, nn_passport, dv_passport, wg_passport)
SELECT STUDENTs_temp.REG_NOM, STUDENTs_temp.FAMILY, STUDENTs_temp.NAME, STUDENTs_temp.PARN_NAME, STUDENTs_temp.INOSTR,

STUDENTs_temp.SEX,
CAST(PSPR_temp.DATA_BORN AS smalldatetime), PSPR_temp.SEM_POL, PSPR_temp.XAR_JT, PSPR_temp.CHILDREN,

PSPR_temp.NATION,
PSPR_temp.SV_ROD1 + PSPR_temp.SV_ROD2 AS Expr1, PSPR_temp.REGION, PSPR_temp.STUD_FML,

PSPR_temp.PARN_FML,
PSPR_temp.OB_STAJ, PSPR_temp.SP_STAJ, PSPR_temp.SN_PASPORT, PSPR_temp.NN_PASPORT, PSPR_temp.DV_PASPORT,
PSPR_temp.WG_PASPORT
FROM STUDENTs_temp INNER JOIN
PSPR_temp ON STUDENTs_temp.REG_NOM = PSPR_temp.REG_NOM
gets an error 'The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value'. Tell me please how can make type casting return NULL if convertion isn't possible.I might try something like so:

Insert Into MyTable(MyDateField)
SELECT (CASE WHEN isDate(DateFieldTobeImported) = 1 Then DateFieldTobeImported ELSE Null End)
From TableBeingImported

If this does not work, please post some sample data.|||Thank you, it works.

Sunday, March 11, 2012

Check Contraint question

This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C67383.F9AD44F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + = ']%'),[LicensePlateNumber]),0) =3D 0)
which works fine, throwing an error if those characters are entered. Is = there a way to have it not throw an error, but rather just remove the = offending characters if entered? Thanks
--=_NextPart_000_0008_01C67383.F9AD44F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I have the following check constraint

(isnull(patindex(('%[' + ' ' + = char(9) + char(10) + char(13) + ']%'),[LicensePlateNumber]),0) =3D 0)

which works fine, throwing an = error if those characters are entered. Is there a way to have it not throw = an error, but rather just remove the offending characters if entered? = Thanks
--=_NextPart_000_0008_01C67383.F9AD44F0--No, that's not what a constraint does.
You can perhaps use an instead-of trigger to achieve this functionality.
Conor
"Burma Jones" <somebody@.somedomain.not> wrote in message
news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
I have the following check constraint
(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
']%'),[LicensePlateNumber]),0) = 0)
which works fine, throwing an error if those characters are entered. Is
there a way to have it not throw an error, but rather just remove the
offending characters if entered? Thanks|||No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.|||Since this is only a few thousand records, I'm not too worried about the
cost of using a trigger. Can you share an example, even pseudocode, showing
how to create a trigger which will remove those characters? Thanks
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:eu$9uj9cGHA.4932@.TK2MSFTNGP03.phx.gbl...
> No, that's not what a constraint does.
> You can perhaps use an instead-of trigger to achieve this functionality.
> Conor
> "Burma Jones" <somebody@.somedomain.not> wrote in message
> news:%23lJiq67cGHA.4892@.TK2MSFTNGP02.phx.gbl...
> I have the following check constraint
> (isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) +
> ']%'),[LicensePlateNumber]),0) = 0)
> which works fine, throwing an error if those characters are entered. Is
> there a way to have it not throw an error, but rather just remove the
> offending characters if entered? Thanks
>|||On Wed, 10 May 2006 08:26:16 -0700, Burma Jones wrote:
>Since this is only a few thousand records, I'm not too worried about the
>cost of using a trigger. Can you share an example, even pseudocode, showing
>how to create a trigger which will remove those characters? Thanks
Hi Burma,
Here's a sample trigger that will remove the offending characters
silently:
CREATE TRIGGER YourTrigger
ON YourTable INSTEAD OF INSERT
AS
INSERT INTO YourTable (OtherColumns, LicensePlate)
SELECT OtherColumns,
REPLACE(REPLACE(REPLACE(REPLACE(LicensePlate, ' ', ''), CHAR(9),
''), CHAR(10), ''), CHAR(13), ''), OtherColumns
FROM inserted
go
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP

Saturday, February 25, 2012

Chart, how do I use format code for a label?

How do I refer to the actual value for the label inside my expression?
Say you have datetime data across the x-axis for the data. I get one row per
month in the dataset (every row is same date and time for each row/month). I
understand how I can format this, for instance "MMM" to get month name in
short format.
But if I want to do further or a bit more complex manipulation? In this
case, I want to show only first letter of the month. I fail to connect how
to put the actual data value inside my VB.NET expression (substring, left or
similar function in this case).
(I can retrieve the first letter of the month along with the data, as an
extra column, doing this in my stored procedure. If above is difficult, I'd
appreciate tips on how to refer to this column for the chart label. I.e., I
want to show some other column as the label, not the one used to derive the
actual value.)
TIA
Tibor Karaszi
SQL Server MVPExpressions for formatting labels are not directly supported in the current
release. You could do a bar chart and use an expression
(=Left(Format(Fields!OrderDate.Value, "MMM"), 1)) for the corresponding
category group. The sample report attached at the end of this post (which
runs against local Northwind database) demonstrates this.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OEwX0qaiEHA.2848@.TK2MSFTNGP10.phx.gbl...
> How do I refer to the actual value for the label inside my expression?
> Say you have datetime data across the x-axis for the data. I get one row
per
> month in the dataset (every row is same date and time for each row/month).
I
> understand how I can format this, for instance "MMM" to get month name in
> short format.
> But if I want to do further or a bit more complex manipulation? In this
> case, I want to show only first letter of the month. I fail to connect how
> to put the actual data value inside my VB.NET expression (substring, left
or
> similar function in this case).
> (I can retrieve the first letter of the month along with the data, as an
> extra column, doing this in my stored procedure. If above is difficult,
I'd
> appreciate tips on how to refer to this column for the chart label. I.e.,
I
> want to show some other column as the label, not the one used to derive
the
> actual value.)
> TIA
> Tibor Karaszi
> SQL Server MVP
>
+++++++++++++ Sample report +++++++++++++
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!Freight.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<Style>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>0</PointWidth>
<Type>Bar</Type>
<Title />
<Width>5.25in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Left(Format(Fields!OrderDate.Value, "MMM"), 1) & "
[" & Format(Fields!OrderDate.Value, "MMM yyyy") & "]"</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<Style>
<Format>c</Format>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>3in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>14b06457-afff-49a5-9624-2ecc74ef5643</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.25in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Freight">
<DataField>Freight</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 OrderDate, Freight
FROM Orders
ORDER BY ShipCity</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>b8405333-c29c-4c57-8ba3-7915ae7bf5eb</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Friday, February 24, 2012

chart question

Could someone please tell me how I can show 0 - 100% on Y-axis in line
charts. I have been trying Format code of #% but that adds 2 additional zeros
like this 20% will be 2000%. Please advise.
Also, I am unable to change the series color.
I need to add a window outset all around the chart with a small window on
top for label. Is it even possible? It seems so easy to do with .Net charting
but I can't figure it out here.
Thanks!!
SonalIf you format values as percentages, then 1 mean 100%. 0.5 means 50%
This behavior is similar to e.g. Excel.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"sonal" <sonal@.discussions.microsoft.com> wrote in message
news:9F5930D7-14B7-43DE-AE94-09774E41BF7F@.microsoft.com...
> Could someone please tell me how I can show 0 - 100% on Y-axis in line
> charts. I have been trying Format code of #% but that adds 2 additional
> zeros
> like this 20% will be 2000%. Please advise.
> Also, I am unable to change the series color.
> I need to add a window outset all around the chart with a small window on
> top for label. Is it even possible? It seems so easy to do with .Net
> charting
> but I can't figure it out here.
> Thanks!!
> Sonal

Chart Question

I have numbers along the side of my chart in MB's.
The numbers a large so i want to format the number with ","
ie
I currently have 200000. I want to see 200,000 along the side fo the chart.
along the y axis.
ThanksIn the format button on the x or y axis tab, enter #,00.
Hope this helps
"Fab" wrote:
> I have numbers along the side of my chart in MB's.
> The numbers a large so i want to format the number with ","
> ie
> I currently have 200000. I want to see 200,000 along the side fo the chart.
> along the y axis.
> Thanks
>
>|||Sure did! Thanks!!
Fab.
"Ramani" <Ramani@.discussions.microsoft.com> wrote in message
news:270AC95B-08CE-45DF-B7B2-471CAA4620D1@.microsoft.com...
> In the format button on the x or y axis tab, enter #,00.
> Hope this helps
> "Fab" wrote:
>> I have numbers along the side of my chart in MB's.
>> The numbers a large so i want to format the number with ","
>> ie
>> I currently have 200000. I want to see 200,000 along the side fo the
>> chart.
>> along the y axis.
>> Thanks
>>
>>|||What format button?
--
Thanks,
CGW
"Ramani" wrote:
> In the format button on the x or y axis tab, enter #,00.
> Hope this helps
> "Fab" wrote:
> > I have numbers along the side of my chart in MB's.
> >
> > The numbers a large so i want to format the number with ","
> >
> > ie
> >
> > I currently have 200000. I want to see 200,000 along the side fo the chart.
> > along the y axis.
> >
> > Thanks
> >
> >
> >
> >

Chart problem

I am displaying a chart in crystal report. In this chart/graph on the vertical data axis i have values in the format like 0.00. Now I want to display this value in the format 0:00, i.e. I want to replace the period(.) with a colon.

I am not getting any solution for this. Also I have a field label on the top of the graph which is formula. In this also I have the same problem.

I can get a colon in the Time format but it is disturbing the number system of the graph.

Need a solution.I have a similar problem at the moment; my dates on the horizontal axis are in the standard DD/MM/YYYY format, whereas I need DD.MM.YYYY. I don't think Crystal Graphing tool is powerful enough to do this, but I did find that I could make it appear this way by changing my computer's regional settings.

Hope this is of some help to you.|||Yes, even I think that crystal is not capable of such a thing.
Thanks for your input anyway.

Chart not being rendered properly in MHTML format -- URGENT

Hi
I have a report that has a chart also. When I try to
render the report the chart that is supposed to be
displayed as a picture does not show up and there is some
junk text after the chart.
Can anyone please tell me what can be wrong here.
The chart shows up perfectly in the preview and also on
the report manager.
Thanks a lotDo you try to render the report by code using the RS web service?
If so, you need to use the renderstream method to render all the images of
the report.
There is a sample here :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_49f6.asp
Cédric
"Ravi" <anonymous@.discussions.microsoft.com> wrote in message
news:7c7001c4847c$707b0ca0$a401280a@.phx.gbl...
> Hi
> I have a report that has a chart also. When I try to
> render the report the chart that is supposed to be
> displayed as a picture does not show up and there is some
> junk text after the chart.
> Can anyone please tell me what can be wrong here.
> The chart shows up perfectly in the preview and also on
> the report manager.
> Thanks a lot
>

Sunday, February 19, 2012

Chart Axis Labels

Is there a way to format numerics like this:
Instead of this:
10,000,000,000
9,000,000,000
8,000,000,000
I would like this:
10B
9B
8B
Or Instead of this:
2,000,000
1,500,000
1,000,000
500,000
I would like this:
2M
1.5M
500k
Is this possible with the charting object? I am quite familiar with
the formatting codes but have never run across something like this.
Thank you in advance for your help.Does anyone know is this is possible?

Tuesday, February 14, 2012

Character to date conversion

I am trying to write and insert statement that takes up a date value, such as,
Insert into tablename(date_column) values (date('some date', format));

I however do not know how exactly to convert a character string to transact sql date. can someone please lead me in correct direction? Some documentation could also help.

Thanks
--Shilpa

No special functions needed. Just insert it as text:

CREATE TABLE test
(
dateValue datetime
)
go
INSERT INTO test
VALUES ('2006-01-01T00:00:00')
go

As for format of datetime values, there are many different ways to format a date, but really only a couple of good ways. Look up "Date Data Types" in books online. It explains it really well.

|||I am new to MS domain. Couldn't get to Books online.
I attempted your suggested Insert statement. The problem is, my date format is different,
it appears as a 12 hour clock. The date shows as 05/12/2005 06:30:12 PM. The table design view shows it as general date. Could you suggest a format for this?

Thanks
S
|||INSERT INTO test
VALUES ('2006-01-01T00:00:00')

Sorry about the previous post. Your solution works without the T before time insertion.
Thanks,
S
|||

There is a copy of books online (not unsurprisingly) online:

http://msdn2.microsoft.com/en-us/ms130214(sql.90).aspx

You can also download it online. They update it quarterly, and this download is from April:

http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en

Sunday, February 12, 2012

char to total time

I must use a database with strange columns ( I cannot change it) ... one column store time into a string format (char) >>>

4 m 42 s
1 m 10 s

and I must get the total of seconds !!

then how can I get with >>>
4 m 42 s (= 282)
1 m 10 s (= 70)

a total = 352

??

thank youSince no two database engines seem to handle strings quite the same way, which engine are you using? Are the columns limited to just minutes and seconds, or can they add hours, days, fortnights, or other units of time? Is the formatting fixed (always two digit seconds), or can it vary? Are minutes required or optional?

-PatP|||it is for ACCESS 2000

and in the database are only m and s
but I think it is possible to find
4 h 8 m 24 s

nothing else !
maximum are hours

thanks a lot if you can find|||I have tried

Table1 is the table
hms is the column

SELECT Sum(Left([hms],InStr(1,[hms],"m")-1)*60+Mid([hms],InStr(1,[hms],"m")+2,2)) AS sumOfSeconds FROM Table1;

but it doesn't work|||In the VBA Editor, I'd addFunction hms2c(hms As String) As Integer
' ptp 20040404 Covert "[ x h][ y m][ z s]" string to integer seconds

Dim retval As Integer ' return value
Dim c As String ' current character

retval = 0: d = "": hms = LCase(hms)

While hms <> ""
c = Left(hms, 1): hms = Mid(hms, 2)
If 0 < InStr(1, "0123456789", c) Then d = d & c
If "h" = c Then retval = retval + 3600 * Val(d): d = ""
If "m" = c Then retval = retval + 60 * Val(d): d = ""
If "s" = c Then retval = retval + Val(d): d = ""
Wend

hms2c = retval
End FunctionIn the Query, I'd use:SELECT Table1.hms, hms2c([hms]) AS Expr1
FROM Table1;You'll probably find other uses for that function if you deal with these strings much. ;)

-PatP|||yes from outside no problem , and I use VB NET ... but I found the solution on another forum .. only with SQL ! impressive !!

thank you|||Originally posted by castali
yes from outside no problem , and I use VB NET ... but I found the solution on another forum .. only with SQL ! impressive !!

thank you What exactly do you mean by "outside"? everything I've suggested is from pure Access 2000. Unless you are using Office 2003 (aka Office.NET), you can't use VB.NET from within Access.

-PatP|||The solution offered by schlauberger is interesting, but it only works for very limited cases. It will fail if there are hours, or if either the minutes or the seconds are missing. If that works for your needs, enjoy!

-PatP

Char Format

I have one table that has a column 'amount' with datatype 'numeric(19,5)'
I'm want to insert the data from this column into another column (in a different database) with this format: '00000000.00' as 'char'
example: If in the first column I have 800.75864 I want to insert it in the other column as '00000800.75'

any suggestions on how to make this conversion?This is freaking blowing my mind...

This should work

DECLARE @.x decimal(15,5), @.y char(11)
SELECT @.x = 800.75864
SELECT @.x, RIGHT(REPLICATE('0',11)+CONVERT(char(11),CONVERT(d ecimal(15,2),@.x)),11)

But's it's acting like it's doing an implicint conversion to numeric...

Hold on..

If I do...

DECLARE @.x decimal(15,5), @.y char(11)
SELECT @.x = 800.75864
SELECT @.x, REPLICATE('0',11)+CONVERT(char(11),@.x)|||If you just need the rounding, then check the round (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_93z8.asp) function. If you need zero padding too (which is rather silly in most cases, since SQL Server handles all of the "leading zero" issues for you), then you'll probably have to resort to a user defined function.

-PatP|||Methinks thou meant:DECLARE @.x decimal(15,5), @.y char(11)
SELECT @.x = 800.75864
SELECT @.x, RIGHT(REPLICATE('0',11)+CONVERT(varchar(11),CONVER T(decimal(15,2),@.x)),11)That works, as long as the value to convert is positive... Then you get some "interesting" results as the sign floats about!

-PatP|||Yeah...what an idiot...

char(11)...

just found the prob...

Thanks...|||It is the "little" things that make the biggest boom when you trip over them!

-PatP|||declare @.n numeric(19,5)
set @.n = 800.75864
SELECT REPLACE(STR(@.n-.005,11,2),' ','0')

Hans.|||THANKS A LOT! IT WORKED|||Originally posted by diegocro
THANKS A LOT! IT WORKED

What did you expect?

And which one did you use?|||Both, I've been trying to do this since yestarday, that's why I was happy.|||Well that'll learn ya

Friday, February 10, 2012

changing/reordering text in a field

I have a table with approx 1200 names in the format of "John Doe" I need to edit the names to the format of "Doe, John"
Any easy way in SQL to do this or am I better off dumping to text file, making the changes in another app and then updating the table?I guess in some time you'll need another format - "FN Doe LN John" ;). It is better to keep first and last name in different fields.|||Your lucky day. See attached file with function for parsing names.|||Yes, Separate columns would be better.

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(myName varchar(50))
GO

INSERT INTO myTable99(myName)
SELECT 'John Doe' UNION ALL
SELECT 'John Apple' UNION ALL
SELECT 'John Q. Adams' UNION ALL
SELECT 'Mr. John Doe' UNION ALL
SELECT 'John Doe III'
GO

SELECT SUBSTRING(myName,(CHARINDEX(' ',myName)+1),(LEN(myName)-(CHARINDEX(' ',myName)))) + ', '
+ SUBSTRING(myName,1,(CHARINDEX(' ',myName)-1))
FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

changing time format

I have a datetime field that looks like this:
2004-04-15 09:31:37.000
I used the convert function (convert(char(8),executiontime,108)) to produce this:
09:31:37
How do I make the result return:
093137
Thanks
You could use REPLACE()
select CONVERT(char(8),REPLACE(convert(char(8),executiont ime,108), ':', =
'')) from foo
--=20
Keith
"Chambers" <anonymous@.discussions.microsoft.com> wrote in message =
news:4041CE38-C011-4CB7-BBBC-C651870B1C37@.microsoft.com...
>=20
>=20
> I have a datetime field that looks like this:
>=20
> 2004-04-15 09:31:37.000
>=20
> I used the convert function (convert(char(8),executiontime,108)) to =
produce this:
>=20
> 09:31:37
>=20
> How do I make the result return:
>=20
> 093137
>=20
> Thanks

changing time format

I have a datetime field that looks like this:
2004-04-15 09:31:37.000
I used the convert function (convert(char(8),executiontime,108)) to produce
this:
09:31:37
How do I make the result return:
093137
ThanksYou could use REPLACE()
select CONVERT(char(8),REPLACE(convert(char(8),
executiontime,108), ':', =
'')) from foo
--=20
Keith
"Chambers" <anonymous@.discussions.microsoft.com> wrote in message =
news:4041CE38-C011-4CB7-BBBC-C651870B1C37@.microsoft.com...
>=20
>=20
> I have a datetime field that looks like this:
>=20
> 2004-04-15 09:31:37.000
>=20
> I used the convert function (convert(char(8),executiontime,108)) to =
produce this:
>=20
> 09:31:37
>=20
> How do I make the result return:
>=20
> 093137
>=20
> Thanks

Changing the value of the Database Field?

Hi all,

I'm working on Crystal reports in VS2005.

My database fields got the values in integer format whereas I want to display some string instead.
for example... if the database field value is 0, I must display "No Value" and for 1, "Low", for 2, "High".... so on!

I've been trying this using formula fields. But I couldn't get the correct results. the text of the formula fields is not varying according to the database field value.

So, how can I display a value based on the database field value instead of the database field value itself??

Pls help me solving this prob out!

Thank you.What do you currently have as your formula?
Also, look at the help for 'case' or 'switch'.|||Actually, select expressions are more what you're after, like

Select {table.field}
Case 0:
'No Value'
Case 1:
'Low'
Case 2:
'High'
...and so on
default: 'Unknown!';|||I dont know if this works out in your case but you can give a try
right click and select format field
click on the formula editor for suppress
write the formula
if
{dbfield i.e your field} = o then no value else if

{dbfield i.e your field} = 1 then low value else if

{dbfield i.e your field} = 2 then high value.

should work

do reply if it works...
regards|||I dont know if this works out in your case but you can give a try
right click and select format field
click on the formula editor for suppress
write the formula
if
{dbfield i.e your field} = o then no value else if

{dbfield i.e your field} = 1 then low value else if

{dbfield i.e your field} = 2 then high value.

should work

do reply if it works...
regards

Hi...

It worked out, but not with the exact format! I used UnboundString Field and inserted a formula in it for every Database field in my table. My formula contains something similar to what you've suggested!

Any way... I got the result in time and thank you very much for the reply!!

Thursday, March 22, 2012

Check if fieldname exists?

Tuesday, March 20, 2012

check if cast is possible

Sunday, March 11, 2012

Check Contraint question

Saturday, February 25, 2012

Chart, how do I use format code for a label?

Friday, February 24, 2012

chart question

Chart Question

Chart problem

Chart not being rendered properly in MHTML format -- URGENT

Sunday, February 19, 2012

Chart Axis Labels

Tuesday, February 14, 2012

Character to date conversion

Sunday, February 12, 2012

char to total time

Char Format

Friday, February 10, 2012

changing/reordering text in a field

changing time format

changing time format

Changing the value of the Database Field?

Char and Varchar

Blog Archive

About Me