Showing posts with label dates. Show all posts
Showing posts with label dates. Show all posts

Friday, March 23, 2012

replace out of range dates

Hi,

I'm migrating data from access 2003 to sql server 2005. An access table contains dates like 5/1/0999. This is a date that is out of the datetime range from sql server.

Now my question is ... How do I replace these dates? I don't find something like IsDate in the expressions?

Ok

I did it with a Derived Column task.
It replaces the date column values who have out of range dates. (It only checks for dates smaller then 1/1/1753)

The expression is like this: [DateToCheck] < (DT_DATE)"1753-1-1" ? (DT_DATE)"1753-1-1" : [DateToCheck]

Posted by originatorrtyg at 9:21 AM 0 comments
Labels: , , , , , , , , , , , , ,

Wednesday, March 21, 2012

Replace Errors with NULL using Convert

I have a table with a varchar field that for the most part contains valid
dates ie (mm/dd/yyyy). There are some items that are not dates. I would
like a SQL statement that converts the varchar to a datetime and where there
is an error for a particular field will return a null for that field.
For example if my table contains the following items in Field1:
2/1/2005
1/1/2004
other data
3/1/2005
The query should return:
2005-2-1 00:00:00
2004-1-1 00:00:00
NULL
2005-3-1 00:00:00
If I use:
SELECT convert(datetime, Field1) as Field1
FROM table1
Then I get a conver error. Any ideas or suggestions would be helpful.
Thanks!
Dan,
See if this works:
select
case when ISDATE(Field1) = 1
then cast(Field1 as datetime)
else NULL end
from table1
If you can't live with the fact that some garbled
data may convert unexpectedly (the string '110919'
will convert to September 19, 2011, for example),
you'll have to do some pattern matching of your own
as well, such as
case when Field1 like '%/%/%' and Field1 not like '%/%/%/%' ...
and isdate(Field1) = 1 then ...
Steve Kass
Drew University
Dan wrote:

>I have a table with a varchar field that for the most part contains valid
>dates ie (mm/dd/yyyy). There are some items that are not dates. I would
>like a SQL statement that converts the varchar to a datetime and where there
>is an error for a particular field will return a null for that field.
>For example if my table contains the following items in Field1:
>2/1/2005
>1/1/2004
>other data
>3/1/2005
>The query should return:
>2005-2-1 00:00:00
>2004-1-1 00:00:00
>NULL
>2005-3-1 00:00:00
>If I use:
>SELECT convert(datetime, Field1) as Field1
>FROM table1
>Then I get a conver error. Any ideas or suggestions would be helpful.
>Thanks!
>
>
Posted by originatorrtyg at 2:24 PM 0 comments
Labels: , , , , , , , , , , , , , , , , ,

Monday, March 12, 2012

Repeating data regions on every page

Hi all, I want to create an invoice that will repeat numbers dates and
shipping info on every page that the list of items grows on to. You know,
to simulate a standard preprinted invoice page but fully generated by
reporting services.
Any assistance is appreciated.
Raul RubioThe sample report, at the end of this posting, should point in the direction
you need to go.
Since you will have a fairly regular structure I suggest that you use a
table instead of lists.
I think you will find them much easier to work with than a List which
contains a number of textboxes.
Note the use of the table group on OrderID.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raul R" <roller8@.hotmail.com> wrote in message
news:e%23qgdx$nEHA.2900@.TK2MSFTNGP09.phx.gbl...
> Hi all, I want to create an invoice that will repeat numbers dates and
> shipping info on every page that the list of items grows on to. You know,
> to simulate a standard preprinted invoice page but fully generated by
> reporting services.
> Any assistance is appreciated.
> Raul Rubio
>
SampleInvoice.rdl
<?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>
<Table Name="table1">
<Height>1.5in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>17</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>16</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order Date</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Country</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ProductName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>ProductName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>UnitPrice</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!UnitPrice.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Quantity">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Quantity</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Quantity.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="OrderDate">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>OrderDate</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!OrderDate.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!CompanyName.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!City.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!Country.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="OrderIDGroup">
<GroupExpressions>
<GroupExpression>=Fields!OrderID.Value</GroupExpression>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Quantity_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Quantity_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>ca33ccc8-f580-468b-ae00-bdebbfd7a5f0</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="ProductName">
<DataField>ProductName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.CompanyName, Customers.City,
Customers.Country, Orders.OrderID, Orders.OrderDate, [Order
Details].UnitPrice, [Order Details].Quantity,
Products.ProductName
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID INNER JOIN
Products ON [Order Details].ProductID =Products.ProductID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4d305298-f219-4b15-ab25-32b10f38c465</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
Posted by originatorrtyg at 8:47 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)