How to extract date Prtion from SF DateBox


Author
Message
Ger Cannoll
Ger Cannoll
Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)
Group: StrataFrame Users
Posts: 430, Visits: 507
How do I extract the Date Only from a  SF DateBox

e.g. DateTime myDate = DateTime.Today.Date gets Date Only Part

But I cant seem to do:  DateTime myDatepart = MySfDateBox.Date

 

 


Ivan George Borges
Ivan George Borges
Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)
Group: StrataFrame MVPs
Posts: 1.9K, Visits: 21K
Hi Ger.

Try

DateTime myDatepart = new DateTime(MySfDateBox.CurrentYear, MySfDateBox.CurrentMonth, MySfDateBox.CurrentDay);

Ger Cannoll
Ger Cannoll
Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)
Group: StrataFrame Users
Posts: 430, Visits: 507
Thanks Ivan. Thars worked fine.

regards,

Gerard
Ivan George Borges
Ivan George Borges
Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)Strategic Support Team Member (3.4K reputation)
Group: StrataFrame MVPs
Posts: 1.9K, Visits: 21K
You're welcome, Ger. Cool
Ger Cannoll
Ger Cannoll
Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)
Group: StrataFrame Users
Posts: 430, Visits: 507
Hi Ivan.

Having another problem with Date/DateTime.

I am trying to pass in a Date to a Stored procedure parameter (set up as date) but get a Conversion Failed in Sql Server

My date = 31 December 2012 (Displayed in UK format with day first)

DateTime DT =   new DateTime(this.dateFrom.CurrentYear,this.dateFrom.CurrentMonth, this.dateFrom.CurrentDay);

MessageBox.Show(DT.ToString() );   shows :  31/12/2012 00:00:00

 

I have tried various combinations of date and Date/time (both on the client side and in the Stored Procdure definition of the date Filed) but cannot seem to get the Sql SProc to run.

Any ideas ?


Edhy Rijo
E
StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
Hi Gerard,

In my Stored Procedures I cast the datetime as follow:

@DateFrom DATE,
@DateTo DATE,
@ParkingNumber INT,
@ParkingPlan INT,
@Cashier_Out_UserID VARCHAR(20)

AS
SELECT
    *
FROM
    PaymentsReport_View p
WHERE
    p.PaymentStatus = 1 AND
    (CAST(p.DatePaidOn AS DATE) >= @DateFrom AND
    CAST(p.DatePaidOn AS DATE) <= @DateTo) AND
    (p.ParkingNumber = @ParkingNumber OR @ParkingNumber = 0) AND
    (p.ParkingPlan = @ParkingPlan OR @ParkingPlan = -1) AND
    (p.Cashier_Out_UserID = @Cashier_Out_UserID OR @Cashier_Out_UserID = '')
ORDER BY
    p.DatePaidOn, p.Cashier_Out_UserID


And in SF, I use an SQLCommand object and pass the date as a parameter using bo.DateFieldName.Date

            Using cmd As New Data.SqlClient.SqlCommand
                 cmd.CommandTimeout = 0
                 cmd.CommandType = Data.CommandType.StoredProcedure
                 cmd.CommandText = "dbo.spPaymentsReport"

                 cmd.Parameters.AddWithValue("@DateFrom", rptParams.TransactionDateStart).SqlDbType = Data.SqlDbType.Date
                 cmd.Parameters.AddWithValue("@DateTo", rptParams.TransactionDateEnd).SqlDbType = Data.SqlDbType.Date

                 cmd.Parameters.AddWithValue("@ParkingNumber", rptParams.ParkingNumber).SqlDbType = Data.SqlDbType.Int
                 cmd.Parameters.AddWithValue("@ParkingPlan", rptParams.ParkingPlan).SqlDbType = Data.SqlDbType.Int
                 cmd.Parameters.AddWithValue("@Cashier_Out_UserID", rptParams.CashierUserID).SqlDbType = Data.SqlDbType.VarChar

                 BusinessLayer.FillMultipleDataTables(cmd, Me.DataSource.SourceBO)
             End Using


Edhy Rijo

Ger Cannoll
Ger Cannoll
Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)
Group: StrataFrame Users
Posts: 430, Visits: 507
Hi Edhy. Thanks for your reply.

I am still getting a Sql Exception--> Command failed when converting Date and/or Time from character string. I am initially taking the data from a SF Date Box. My Code is:


this.dateFrom is a SF DateBox


-- Client Code -- 
DateTime x = new DateTime(this.dateFrom.CurrentYear, this.dateFrom.CurrentMonth, this.dateFrom.CurrentDay);
comm.Parameters.AddWithValue("@DateFrom", x).SqlDbType= SqlDbType.Date;


-- Sql SProc Code -- 

ALTER PROCEDURE [dbo].[sprSelectSalesFormTrans]
@DateFrom    Date,
@DateTo      Date,
@TranFrom    Char(10),
@TranTo      Char(10),
@Post        Char(1)
 
Edhy Rijo
E
StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)StrataFrame VIP (4.5K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
Hi Gerard,

What is the code that is triggering the exception?  posting the exception will help in finding out where exactly the code is failing.

Also in your Client Code, pass the x.Date to the @DateFrom
DateTime x = new DateTime(this.dateFrom.CurrentYear, this.dateFrom.CurrentMonth, this.dateFrom.CurrentDay);
comm.Parameters.AddWithValue("@DateFrom", x.Date).SqlDbType= SqlDbType.Date;


Edhy Rijo

Ger Cannoll
Ger Cannoll
Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)
Group: StrataFrame Users
Posts: 430, Visits: 507
Hi Edhy.

Did as you suggested (comm.Parameters.AddWithValue("@DateFrom", x.Date).SqlDbType= SqlDbType.Date;..... still an error

I have attached the error in case i gives a clue.

Regards,

Gerard


 
Ger Cannoll
Ger Cannoll
Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)Advanced StrataFrame User (628 reputation)
Group: StrataFrame Users
Posts: 430, Visits: 507
Sorry previos post does not seem to have attachment....attachemnt now
Attachments
Image 4.png (99 views, 101.00 KB)
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search