Date Problem

I have a date problem within Reporting Services.

During ETL (SSIS 2005) process we converted a date e.g. purchase date from a file

which was 20070703 to 03/07/2007 00:00:00, (this is a standalone date not associated with dim_time)

and once i ran the fact table in sql query (in sql management studio 2005) the date does indeed come out as above, 03/07/2007.

So, the cube was built and i began building reports in SSRS 2005 when the above date has gone back to 2007-07-03 00:00:00.

I tried fromatting it to dd MMM yyyy but it still brings it out the wrong way.

Can anyone help me force the date back or am i missing something ?

Thanks

LakP wrote:

I tried fromatting it to dd MMM yyyy but it still brings it out the wrong way.

Be more specific, how and *where* did you try to format it? It should work fine if in your textboxes etc in the report if you do a Format(CDate(<your date>), <required format>) (use it in the expression property of the textbox).

Date Problem

I have a date problem within Reporting Services.

During ETL (SSIS 2005) process we converted a date e.g. purchase date from a file

which was 20070703 to 03/07/2007 00:00:00, (this is a standalone date not associated with dim_time)

and once i ran the fact table in sql query (in sql management studio 2005) the date does indeed come out as above, 03/07/2007.

So, the cube was built and i began building reports in SSRS 2005 when the above date has gone back to 2007-07-03 00:00:00.

I tried fromatting it to dd MMM yyyy but it still brings it out the wrong way.

Can anyone help me force the date back or am i missing something ?

Thanks

LakP wrote:

I tried fromatting it to dd MMM yyyy but it still brings it out the wrong way.

Be more specific, how and *where* did you try to format it? It should work fine if in your textboxes etc in the report if you do a Format(CDate(<your date>), <required format>) (use it in the expression property of the textbox).

Date parameter getting converted

I have my report's data source set to a stored procedure that I created which
takes 2 date parameters. If I run the dataset while in the Data Tab it works
fine but when I go to the preview window no data shows up. I ran a SQL
Profiler trace to figure out what was going on. Apparently in the Data Tab
the dates are being passed in just as typed but in the preview window the
dates get converted to extremely tiny decimals...9.677419354838709e-002. How
can I get these dates carried through properly? I even tried to make them
String datatype instead of DateTime, same results.It now appears that these same decimals are being passed in no matter what I
type in the date boxes...
"BrianW" wrote:
> I have my report's data source set to a stored procedure that I created which
> takes 2 date parameters. If I run the dataset while in the Data Tab it works
> fine but when I go to the preview window no data shows up. I ran a SQL
> Profiler trace to figure out what was going on. Apparently in the Data Tab
> the dates are being passed in just as typed but in the preview window the
> dates get converted to extremely tiny decimals...9.677419354838709e-002. How
> can I get these dates carried through properly? I even tried to make them
> String datatype instead of DateTime, same results.|||I figured it out. In the Parameters Tab of my dataset I thought I was
supposed to put default values in. Instead I put the expression of my Report
Parameters in and it worked. Wow, these message boards are great!
"BrianW" wrote:
> It now appears that these same decimals are being passed in no matter what I
> type in the date boxes...
> "BrianW" wrote:
> > I have my report's data source set to a stored procedure that I created which
> > takes 2 date parameters. If I run the dataset while in the Data Tab it works
> > fine but when I go to the preview window no data shows up. I ran a SQL
> > Profiler trace to figure out what was going on. Apparently in the Data Tab
> > the dates are being passed in just as typed but in the preview window the
> > dates get converted to extremely tiny decimals...9.677419354838709e-002. How
> > can I get these dates carried through properly? I even tried to make them
> > String datatype instead of DateTime, same results.

Date manipulation

I need to to get the result of the function GETDATE and converted to a simpler "mm/dd/yyyy" format in order to compare the results to another date in a table. In ACCESS the function DATE returns the format of 'mm/dd/yyyy' since I need to work with date ranges without a need for this application 'HH:MM:SS'

I have try 'TRANSFORM(GETDATE,'mm/dd/yyyy') but I keep getting errors.

I am not sure what I am doing wrong? Any help is appreciated since I need to work in SQL Server 2000.

Gratefull

Neil

If you want to store the "tranformed date" in a datetime column, the hh,mm,ss will still be added to the column, although they might be NULL, depending on your operation you use for the transform, but if you want to simply minsert the transformed/converted date into a character column you can do the following:

SELECT CONVERT(VARCHAR(10),GETDATE(),1)

See the CONVERT function in the BOL for more information.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Thanks for your help Jens, I am still making the transition from ACCESS Functions to SQL Server and how to get similar results

date field problem - "Value could not be converted because of a potential loss of data"

Hi,

I have a flat file that has a date column where the date fields look like 20070626, for example. No quotes.

The problem is that several of the date values are missing, and instead of the date value the field looks like this , ,

That is, there are several blank spaces where the date should be. The number of blank spaces between the commas doesn't appear to be a set number (and it could even be 8 blank spaces, I don't know, in which case I don't know if checking for the Len will produce the correct results, but that's another issue...)

So, similar to the numeric field blanks problem, I wrote a script to convert the field to null. This is the logic I used:

IfNot Len(Row.TradeDate) = 8 Then

Row.TradeDate_IsNull = True

EndIf

The next step in my data flow after the script is a derived column where I convert TradeDate from 20070625 to 06/25/2007. So the exact error message I am receiving is this:

[OLE DB Destination [547]] Error: There was an error with input column "TradeDate - derived" (645) on input "OLE DB Destination Input" (560). The column status returned was: "The value could not be converted because of a potential loss of data.".

Do I need to add a conditional split after the script and BEFORE the derived column to redirect bad rows so they don't go to the derived column?

What am I doing wrong here?

Thanks

Actually, I realize I don't want a conditional split because I don't want to throw the whole row away.

I just need to fix this "data conversion" problem.

|||Why not use a derived column to work with the date field and use the trim() function to get rid of the spaces, however many there may be? I see no need to invoke a script for this.

ISNULL(TRIM([TradeDate])) || [TradeDate] == "" || LEN([TradeDate]) < 8 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([TradeDate],5,2) + "/" + SUBSTRING([TradeDate],7,2) + "/" + SUBSTRING([TradeDate],1,4))|||

Hi,

I see your logic. I removed the script part and updated the derived column with your expression.

Now I get these errors:

[Derived Column [111]] Error: The conditional operation failed.

[Derived Column [111]] Error: The "component "Derived Column" (111)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "TradeDate - derived" (541)" specifies failure on error. An error occurred on the specified object of the specified component.

[Derived Column [111]] Error: The "component "Derived Column" (111)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "TradeDate - derived" (541)" specifies failure on error. An error occurred on the specified object of the specified component.

In the flat file conn mgr, TradeDate is defined as a DT_STR, 8. I see that you are casting TradeDate to a DT_DBTIMESTAMP. I don't think that has anything to do with this error, but thought I would mention it.

I really don't know what the problem is here!

Thanks

|||I see I forgot a couple of TRIM() calls. Try this:

ISNULL(TRIM([TradeDate])) || TRIM([TradeDate]) == "" || LEN(TRIM([TradeDate])) < 8 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([TradeDate],5,2) + "/" + SUBSTRING([TradeDate],7,2) + "/" + SUBSTRING([TradeDate],1,4))

The output of the above is a datetime field. If you don't want that, and instead want it as a string, then just get rid of the (DT_DBTIMESTAMP) cast before the substrings.|||

Hi,

Why is it when I remove the (DT_DBTIMESTAMP) from NULL(DT_DBTIMESTAMP) the expression is bad?

I guess I don't understand this part: NULL(DT_DBTIMESTAMP)

Thanks

|||Nulls have to by typed accordingly. So that is outputting a NULL of type, DT_DBTIMESTAMP. If you want it to be a string, you'll need to do: NULL(DT_STR,10,1252)|||

That's what I figured, but of course I didn't know the syntax for casting to a string.

Casting to a DT_DBDATETIME is fine since that's what it ultimately is in the table.

Anyhow, it works now, thanks!

I see how I could use derived columns to solve the "blank numeric" problem in my previous post, but it's probably easier with the script, since I don't have to define a derived column output name, and it's less verbose.

|||

sadie519590 wrote:

I see how I could use derived columns to solve the "blank numeric" problem in my previous post, but it's probably easier with the script, since I don't have to define a derived column output name, and it's less verbose.

Up to you!