Need output in dd/MM/yyyy format from table value pair

Question

Here is my code for passing table value pair to stored procedure. DOJ field is DateTime and in SP, DOJ field is date. Both are compatible. Output is like dd/MM/yyyy.

If DOJ field is DateTime and in SP, DOJ field is DateTime2(3), o/p is dd/MM/yyyy hh:mm:ss But I need o/p to be dd/MM/yyyy. How should i write the code ?

dt1.Columns.Add("DOJ", typeof(System.DateTime)); 
DataRow dr1 = dt1.NewRow();
dr1["DOJ"] = DateTime.ParseExact("02/03/2001", formats, us, DateTimeStyles.None); 

// dr1["DOJ1"] = "12/13/2001";   if i use  this one it works .

dt1.Rows.Add(dr1);    // Get DOJ as - 3/2/2001 12:00:00 AM
ds1.Tables.Add(dt1);

Here is my stored procedure code -

-- CREATE TYPE StateTbls7 AS TABLE
( StateID   VARCHAR(200)
, StateCode VARCHAR(200)
, StateName VARCHAR(200)
, DOJ      date            
)

ALTER PROCEDURE sp_Add_contact
    (
      @ds1 StateTbls7 readonly
    )
AS
    begin
              declare @DOJ       VARCHAR(200)
          select @DOJ = d1.DOJ   from @ds1   d1
              select  @DOJ as 'a1'
    end
return

dd/mm/yyyy will return day/minute/year instead use dd/MM/yyyy which return day/month/year. — Jade, Dec 21 '13 at 09:24
What i meant is in your paragraph not in the code snippet. Sorry for that. — Jade, Dec 21 '13 at 09:29
Where is it that you see the output, with hours-minutes-seconds? In .NET, you can use `.ToString("d")` or `.ToShortDateString()`. — Jeppe Stig Nielsen, Dec 21 '13 at 09:30
i am using gridview for watching output . One more interesting thing is In cs file , if i look at datatable dt1 before calling SP , i always get dd/MM/yyyy hh:mm:ss instead of dd/MM/yyyy , though i am using parseExact . Long format then pass to SP and sp return me short date if and only if i am using System.Date . — Bhushan Mahajan, Dec 21 '13 at 09:36
It is because sqlserver will always saves date along with time in a field with date/datetime datatype. See my answer below on how to format the date in your select query statement inside your SP — Jade, Dec 21 '13 at 10:26

Jade · Answer 1 · 2013-12-21T10:27:25.063

0

Try to change you SQL query like this

 ....Code before this

      select @StateID = d1.StateID 
            ,@StateCode = d1.StateCode  
            ,@DOJ = convert(varchar(15), d1.DOJ, 103) -- 103 is dd/MM/yyyy
      from @ds1   d1

 ....Rest of the Code

here i cast the date time to text and removed the time

HOPE THIS HELP!

edited Dec 21 '13 at 10:27

answered Dec 21 '13 at 09:56

Jade

2,972
1
11
9

It gives error Conversion failed when converting date and/or time from character string. if i use dr1["DOJ1"] = "12/13/2001"; it works – Bhushan Mahajan Dec 22 '13 at 23:57

score 0 · Answer 2 · answered Dec 21 '13 at 10:11

0

Remove 'formats' declaration

Instead, change like this in your c#,

dr1["DOJ"] = DateTime.ParseExact("02/03/2001", us, DateTimeStyles.None).ToString("d");

Hope it may help.Let me know the result.

answered Dec 21 '13 at 10:11

gkrishy

756
1
8
33

fortmats argument is must . output still include time part – Bhushan Mahajan Dec 22 '13 at 23:56
Instead of Formats declaration, here I used Tostring("d"). It produce the result as you want(Time part). You just remove format declaration and change this line as i mentioned above. – gkrishy Dec 23 '13 at 05:16

Need output in dd/MM/yyyy format from table value pair

2 Answers2