Number to Date

Hi,
I have a legacy system that uses a strange date format, wich I'd like to
convert for datetime on a new table.
The date is in this format: 104001(2004-01-01), 104002(2004-01-02),
104271(2004-09-27)
I think that I'll have to break the problem in two parts:
The year - three first digits + 1900
the day - three last digits (dy) day of the year.
I can generate the days by using "datepart(dy,date)", but how can i make the
way back? (to transform 271 in 09-27)
Thanks for your help.
SQL Server can do this with simple CAST operations, for instance, do:
SELECT CAST( CAST( 1900 + LEFT( c, 3 ) AS CHAR( 4 ) ) + '0101' AS DATETIME )
+ CAST( RIGHT( c, 3 ) AS INT ) - 1
FROM tbl ;
-- where c is the column from the table tbl.
Anith
|||Miguel,
Anith's solution will work for dates in or after the year 2000, but it
could give the wrong answer for earlier dates, if they are stored as
integers or as strings but without a leading zero. Here's an alternate
solution that should work:
select dateadd(year,@.c/1000,-1) + @.c%1000 from yourTable
Steve Kass
Drew University
Miguel Salles wrote:

>Hi,
>I have a legacy system that uses a strange date format, wich I'd like to
>convert for datetime on a new table.
>The date is in this format: 104001(2004-01-01), 104002(2004-01-02),
>104271(2004-09-27)
>I think that I'll have to break the problem in two parts:
>The year - three first digits + 1900
>the day - three last digits (dy) day of the year.
>I can generate the days by using "datepart(dy,date)", but how can i make the
>way back? (to transform 271 in 09-27)
>Thanks for your help.
>
>
sql

num of checks written in 4 day period for more than $400

I have a transaction table which has Date as datetime field, amount and account number. i want to find out count of checks that were written in a period of 4 days which exceeded i.e. > $400, between 401 and 500, > 501 for a single month. the table has data for more than a year and i want the results then grouped in monthly format like in
OCT between 300 & 400 #30 (30 customers gave checks total worth $300-$400 within any 4 consecutive days period in the month of OCT )
between 400 & 500 # 20
> 501 # 10

NOV between 300 & 400 #30
between 400 & 500 # 20
> 501 # 10

and so on for a 6 month period.You will need to use a self join. Something like this:

select Table1.CustomerID, sum(Table2.CheckValue)
from YourTable Table1 inner join YourTable Table2
on Table1.CustomerID = Table2.CustomerID
and Table1.CheckDate > Table2.CheckDate
and datediff(day, Table2.CheckDate, Table1.CheckDate) <= 4
group by Table1.CustomerID
having sum(Table2.CheckValue) > 400

NullProcessing on Dimension keys

Running in to the following problem

Have a dimension (time dimension) with a datetime keyfield. Have a fact table with a date field that is sometimes not filled in. A regular relationship exists in the dimension usage tab between this time dimension and the measure group of the fact table based on the date field.

So I set up the time dimension with dimesion property UnknownMember visible, set the NullProcessing property of the keyColumn of the dimension keyfield (you're still folowing ;-) to UnknownMember because I want to avoid the default beheaviour of SSAS making it 0 or blank.

Deploy the thing and get the following 2 warnings:

Warning 1 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_ProjectCard, Column: ProjectDateSold, Value: 0:00:00. 0 0
Warning 2 Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Time of Dimension: Project Sold Date from Database: CDWH OLAP database, Cube: CDWH, Measure Group: Project Card, Partition: Project Card, Record: 8. 0 0

Both of them seem perfectly normal (Notice that the value of warning 1 represents the NULL date) and that warning 2 actualy tells me that it was converted to an unknown member, but then I get 3 errors

Error 3 Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. 0 0
Error 4 Errors in the OLAP storage engine: An error occurred while processing the 'Project Card' partition of the 'Project Card' measure group for the 'CDWH' cube from the CDWH OLAP database database. 0 0
Error 5 Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. 0 0

So it seems that's no good, This works perfect for dimension attributes but what am I missing here ?

Tx /Dirk

Found the solution

In the dimension usage tab, when you define the relationship. You can click the advanced button. In this dialog you can also modify the NullProcessing. So you need to set it here to UnknowMember instead of keyColumn of the dimension itself.

Wished MS would make it more consistent.

Null values

Hi,

How can I use "Derived Column" to check if a Datetime value is null or not and if null to insert 00/00/00 instead. ?

The background being that while using a "Derived Column" to change a Column from a (DT_DATE) to a (DT_DBTIMESTAMP) everytime I get a null value it see's it as a error.

And the column in particular has ~ 37 K blank / null fields so Im getting a lot of errors

So far I have tried to use something like

ISNULL([Column 34])

Or

SELECT ISNULL(ID, '00/00/0000') FROM [Column 34]

Or

SELECT ISNULL(au_id, '00/00/0000 00:00') AS ssn
FROM [Column 34

but none seems to work [Column 34] being the offending column.

What a normally use is just a simple "(DT_DBTIMESTAMP)[Column 34]"
in the expression column, which seems to work well, but here I get alot of errors

Any ideas?

Try

ISNULL([Column 34]) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)[Column 34]

Or something like that.

... ? ... : ... is the if then else operator! C++ stylee!!!

-Jamie

|||

You would use an expression like:

ISNULL([Column 34]) ? (DT_DBTIMESTAMP)"00/00/00" : (DT_DBTIMESTAMP)[Column 34]

Except, that will fail due to a conversion error, since 00/00/00 is not a valid DT_DBTIMESTAMP. You will need to use a valid date instead, I think.