DateTime without the time

Hi,

Im moving data from a OLE DB Source to a Flat File Destination.

I have a DateTime field in my database.

My current query returns:
2007-05-21 00:00:00

How can I make it return:
2007-05-21

Thank you!!

Use a derived column to cast the field to DT_DBDATE...

(DT_DBDATE)[YourDateTimeField]|||

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

|||

MrHat wrote:

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

Yes, you need to define the data type of that column to DT_DBDATE in the flat file connection manager.|||My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?|||

JStutz wrote:

My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?

Displaying just the time is a simple transact-sql statement using the CONVERT function.

|||

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

|||

SQL-PRO wrote:

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

Still if that's in your source query, you can't store it that way -- not in SQL Server anyway. (Unless you're storing it in a varchar field.)

DateTime without the time

Hi,

Im moving data from a OLE DB Source to a Flat File Destination.

I have a DateTime field in my database.

My current query returns:
2007-05-21 00:00:00

How can I make it return:
2007-05-21

Thank you!!

Use a derived column to cast the field to DT_DBDATE...

(DT_DBDATE)[YourDateTimeField]|||

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

|||

MrHat wrote:

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

Yes, you need to define the data type of that column to DT_DBDATE in the flat file connection manager.|||My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?|||

JStutz wrote:

My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?

Displaying just the time is a simple transact-sql statement using the CONVERT function.

|||

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

|||

SQL-PRO wrote:

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

Still if that's in your source query, you can't store it that way -- not in SQL Server anyway. (Unless you're storing it in a varchar field.)

Datetime to time conversion with default date

Hi,

I am importing a csv file to SQL 2005 table. The source column is coming as datetime. The destination filed is a datetime type. I would like to update the destination with the time part from the source. I used the data conversion to convert it to time using "database time[DT_DBTIME]". For a source value "2/08/2007 21:51:07" this inserts a value "2007-08-03 21:51:07.000". I need the column to have a value as "1900-01-01 21:57:07.000".

Can someone please tell me how do I do this conversion?

Thanks,

Try this in a Derived Column transform (replace DateValue with the name of your column):

Code Snippet

(DT_DBTIMESTAMP)("1900-01-01 " + (DT_WSTR,10)(DT_DBTIME)DateValue)

|||

Thanks, jwelch.

DateTime null in Sql Server database

Hi,

I'm using this source code in order to set the DateTime field of my Sql Server database to null.
I am retreiving dates from an excel sheet. If no date is found, then I set my variable myDate to DateTime.MinValue then i test it just before feeding my database.

I have an error saying that 'object' does not contain definition for 'Value'.

In french :Message d'erreur du compilateur:CS0117: 'object' ne contient pas de définition pour 'Value'
dbCommand.Parameters["@.DateRDV"].Value = System.Data.SqlTypes.SqlDateTime.Null;

The funny thing is that in the class browser i can see the Value property for the class Object...

C#, asp.net
string sqlStmt ;
string conString ;
SqlConnection cn =null;
SqlCommand cmd =null;
SqlDateTime sqldatenull ;
try
{
sqlStmt = "insert into Emp (Date) Values (@.Date) ";
conString = "server=localhost;database=Northwind;uid=sa;pwd=;";
cn = new SqlConnection(conString);
cmd = new SqlCommand(sqlStmt, cn);
cmd.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime));
sqldatenull = System.Data.SqlTypes.SqlDateTime.Null;
if (myDate == DateTime.MinValue)
{
cmd.Parameters ["@.Date"].Value =sqldatenull ;
}
else
{
cmd.Parameters["@.Date"].Value = myDate;
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = "Record Inserted Succesfully";
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

Are you sure you're referencing the correct Parameter? The error message says "@.DataRDV" but your code uses "@.Date".

|||

GranPas wrote:

Hi,

I'm using this source code in order to set the DateTime field of my Sql Server database to null.
I am retreiving dates from an excel sheet. If no date is found, then I set my variable myDate to DateTime.MinValue then i test it just before feeding my database.

I have an error saying that 'object' does not contain definition for 'Value'.

In french :Message d'erreur du compilateur:CS0117: 'object' ne contient pas de définition pour 'Value'
dbCommand.Parameters["@.DateRDV"].Value = System.Data.SqlTypes.SqlDateTime.Null;

The funny thing is that in the class browser i can see the Value property for the class Object...

C#, asp.net
string sqlStmt ;
string conString ;
SqlConnection cn =null;
SqlCommand cmd =null;
SqlDateTime sqldatenull ;
try
{
sqlStmt = "insert into Emp (Date) Values (@.Date) ";
conString = "server=localhost;database=Northwind;uid=sa;pwd=;";
cn = new SqlConnection(conString);
cmd = new SqlCommand(sqlStmt, cn);
cmd.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime));
sqldatenull = System.Data.SqlTypes.SqlDateTime.Null;
if (myDate == DateTime.MinValue)
{
cmd.Parameters ["@.Date"].Value =sqldatenull ;
}
else
{
cmd.Parameters["@.Date"].Value = myDate;
}
cn.Open();
cmd.ExecuteNonQuery();
Label1.Text = "Record Inserted Succesfully";
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
cn.Close();
}

Did you add a parameter called "@.DateRDV"?

|||

Thanks for help. I actually changed my variable name which was DateRDV to Date because the source code i had pasted was from a sample i found on Internet.

I found a solution to my problem. When the user click on a button, I set myDate to DateTime.MinValue if myDate is null, as i did before. Now, I am using a function in order to insert the date in my database. This is working and I still don't know why the older source code did not. Here is my source working :

int Insert_Trdv(System.DateTime dateRDV)
{
string connectionString = "server=\'myServer\'; user id=\'myId\';
password=\'myPassword\'; database=\'myPassword\'";
System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
string queryString = @."INSERT INTO [Trdv] ([DateRDV])";
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
System.Data.IDataParameter dbParam_dateRDV = new
System.Data.SqlClient.SqlParameter();
dbParam_dateRDV.ParameterName = "@.DateRDV";
if(dateRDV == DateTime.MinValue)
{
dbParam_dateRDV.Value = DBNull.Value;
}
else
{
dbParam_dateRDV.Value = dateRDV;
}
dbParam_dateRDV.DbType = System.Data.DbType.DateTime;
dbCommand.Parameters.Add(dbParam_dateRDV);
int rowsAffected = 0;
dbConnection.Open();
try
{
rowsAffected = dbCommand.ExecuteNonQuery();
}
finally
{
dbConnection.Close();
}
return rowsAffected;
}

Thx

Datetime entry for querying analysis service cube

Hi everybody,

I have two problems while using a analysis service cube as data source for a reporting service report.

1.) I've an individual time dimension which has day entries in the standard date format "mm/dd/yyyy". When using an parametric entry for the date hierachy the reporting offers me all entries as a list (some 1000 entries). Looking under report parameters I recognized that the input parameter is listed as of the type string. However I know that the underlying field and as well the hierachy in the cube is of the format datetime. Change it to datetime causes the reporting service to fail with the error message:

An error occured during local report processing.
The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type.

How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function.

2.) I have another dimension with the hierachy cycle which has the string format "year-month". I would like to use the selection of the date hierachy to create the restriction on the cycle hierachy. I.e. entering '01/16/2007' on the time dimension should write the value '2007-01' to a parameter which is then used to restrict the cycle hierachy. Experimenting with report parameters always caused the error message:

An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set 'DIM...'.
Query (1,453) The restriction by the CONSTRAINED-flag in the STRTOSET-function has been violated.

As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem.

How can I create an input for a restriction on a dimension based on a parameter with a self constructed string?

Thanks,

StSt

However I know that the underlying field and as well the hierachy in the cube is of the format datetime

Each member in your Time dimension is identified using the following format [DimensionName].[AttributeHierarchyName].&[MemberKey]. This is the format that the generated parameter query uses. You can use this format to apply a fiter and limit the members shown. The Report Builder could help you to understand how to set the filter. Alternatively, you can set the Value property of the Date dimension key to the underlying field of DateTime type. However, each SSRS parameter can have only two values (label and value). To pass the selected value to the main query you need to resolve it to a valid member (again [DimensionName].[AttributeHierarchyName].&[MemberKey]). So, it may be more convenient to stick to this format as the parameter value.

|||Thanks this was of help ...even so I don't like the idea of constructing the member representation of the analysis service but it works |||can you tell me exactly how you resolved this? thanks,

Datetime entry for querying analysis service cube

Hi everybody,

I have two problems while using a analysis service cube as data source for a reporting service report.

1.) I've an individual time dimension which has day entries in the standard date format "mm/dd/yyyy". When using an parametric entry for the date hierachy the reporting offers me all entries as a list (some 1000 entries). Looking under report parameters I recognized that the input parameter is listed as of the type string. However I know that the underlying field and as well the hierachy in the cube is of the format datetime. Change it to datetime causes the reporting service to fail with the error message:

An error occured during local report processing.
The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type.

How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function.

2.) I have another dimension with the hierachy cycle which has the string format "year-month". I would like to use the selection of the date hierachy to create the restriction on the cycle hierachy. I.e. entering '01/16/2007' on the time dimension should write the value '2007-01' to a parameter which is then used to restrict the cycle hierachy. Experimenting with report parameters always caused the error message:

An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set 'DIM...'.
Query (1,453) The restriction by the CONSTRAINED-flag in the STRTOSET-function has been violated.

As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem.

How can I create an input for a restriction on a dimension based on a parameter with a self constructed string?

Thanks,

StSt

However I know that the underlying field and as well the hierachy in the cube is of the format datetime

Each member in your Time dimension is identified using the following format [DimensionName].[AttributeHierarchyName].&[MemberKey]. This is the format that the generated parameter query uses. You can use this format to apply a fiter and limit the members shown. The Report Builder could help you to understand how to set the filter. Alternatively, you can set the Value property of the Date dimension key to the underlying field of DateTime type. However, each SSRS parameter can have only two values (label and value). To pass the selected value to the main query you need to resolve it to a valid member (again [DimensionName].[AttributeHierarchyName].&[MemberKey]). So, it may be more convenient to stick to this format as the parameter value.

|||Thanks this was of help ...even so I don't like the idea of constructing the member representation of the analysis service but it works |||can you tell me exactly how you resolved this? thanks,