get only date from sql

Question

I need to get the date part only from SQL. SQL data type is date but when i tried to call it it returns this value 01-Mar-14 12:00:00 AM. The only part I'm interested in is 01-Mar-14 I've a long search in stackoverflow and microsoft and google but in vain. First search didn't work with me
also this one didn't work out
Here is my C# code to get these values.

private void SearchName_Click(object sender, EventArgs e)     
{                
      db.con.Open();
      SqlDataReader reader;
      SqlCommand command = new SqlCommand("select * from Employee inner join DatePics on ID=Emp_ID where EmpName='" + SearNametxt.Text + "' ", db.con);                
      reader = command.ExecuteReader();
      while (reader.Read())
      {
           NameSeartxt.Text = (reader["EmpName"].ToString());                   
           PassSeartxt.Text = (reader["Passport"].ToString());               
           IDSeartxt.Text = (reader["IDIssue"].ToString());
           expiryseartxt.Text = (reader["IDExpiry"].ToString());                  
      }
      db.con.Close();
}

but also return only the whole format. Tried to use this one, but fail.
select convert(varchar(10), '01-Mar-14 12:00:00', 120)

EDIT: latest code:

db.con.Open();
SqlDataReader reader; 
SqlCommand command = new SqlCommand("select * from Employee inner join 
     DatePics on ID=Emp_ID where EmpName='" + SearNametxt.Text + "' ", db.con);
reader = command.ExecuteReader(); 
while (reader.Read()) 
{
 DateTime Received; 
 DateTime.TryParse(reader["ReceivedDate"], CultureInfo.InvariantCulture,  
                                    DateTimeStyles.None, out Received); 
 recsearchtxt.Text = Received.ToString("d");

Sudhakar Tillapudi · Accepted Answer · 2014-03-16T17:03:59.350

3

You need to pass first argument as String, so convert the reader["ReceivedDate"] into string using reader["ReceivedDate"].ToString()

Try This:

DateTime Received;
DateTime.TryParse(reader["ReceivedDate"].ToString(),
              CultureInfo.InvariantCulture, DateTimeStyles.None, out Received);

edited Mar 16 '14 at 17:03

answered Mar 16 '14 at 16:41

Sudhakar Tillapudi

25,935
5
37
67

@BillG: could you please Edit your question with latest code? – Sudhakar Tillapudi Mar 16 '14 at 16:54
`db.con.Open(); SqlDataReader reader; SqlCommand command = new SqlCommand("select * from Employee inner join DatePics on ID=Emp_ID where EmpName='" + SearNametxt.Text + "' ", db.con); reader = command.ExecuteReader(); while (reader.Read()) { DateTime Received; DateTime.TryParse(reader["ReceivedDate"], CultureInfo.InvariantCulture, DateTimeStyles.None, out Received); recsearchtxt.Text = Received.ToString("d"); ` – Bill G Mar 16 '14 at 16:57
@BillG: first argument should be string, check my modified answer – Sudhakar Tillapudi Mar 16 '14 at 17:04
@SudhakarTillapudi: Thanks for following my initial (incomplete) answer. This overload of TryParse is better than the two steps I blurted out originally. – Karl Kieninger Mar 16 '14 at 17:21
@KarlKieninger: i think yours also fine if you could use `ToString()` on reader data, but yes as you said `InvariantCulture` overload is better than normal 2 parameter method atleast when dealing with Dates :) – Sudhakar Tillapudi Mar 16 '14 at 17:26
@KarlKieninger: i see you are already using `ToString()`, okay but i think you didnt notify OP about it. – Sudhakar Tillapudi Mar 16 '14 at 17:32

score 2 · Answer 2 · answered Mar 16 '14 at 15:15

2

To get the date, use convert() with a format specification:

select *, convert(varchar(10), datecol, 121) as datestr
from Employee inner join
     DatePics
     on ID = Emp_ID
where EmpName='" + SearNametxt.Text + "' ";

This will convert it to YYYY-MM-DD.

answered Mar 16 '14 at 15:15

Gordon Linoff

1,242,037
58
646
786

still the same . This is the statement i've used ` SqlCommand command = new SqlCommand("select *, convert (varchar(10), ReceivedDate,121) as datestr from Employee inner join DatePics on ID=Emp_ID where EmpName='" + SearNametxt.Text + "' ", db.con); ` – Bill G Mar 16 '14 at 15:23
The problem then is at the application layer and Karl's answer probably works. – Gordon Linoff Mar 16 '14 at 15:25

Karl Kieninger · Answer 3 · 2014-03-16T16:50:06.447

2

You should be able to do something like:

DateTime issueDate;
DateTime.TryParse(reader["IDIssue"].ToString(), out issueDate);
IDSeartxt.Text = issueDate.ToString("d");

Though you'll need to use the right formatting param.

Here is a fiddle.

edited Mar 16 '14 at 16:50

answered Mar 16 '14 at 15:20

Karl Kieninger

8,841
2
33
49

error ` The best overloaded method match for 'System.DateTime.TryParse(string, Out System.DateTieme)' has some invalid arguments. ` – Bill G Mar 16 '14 at 15:39
@Sudhakar Tillapudi: The only one is `CultureInfoConverter` – Bill G Mar 16 '14 at 15:52
1

@BillG: you need to import `System.Globalization` namespace , import this -> `using System.Globalization;` – Sudhakar Tillapudi Mar 16 '14 at 15:55
@Sudhakar Tillapudi: `DateTime Received; DateTime.TryParse(reader["ReceivedDate"], out Received,CultureInfo.InvariantCulture); recsearchtxt.Text = Received.ToString("d");` error ` no overload for method " Tryparse" take 3 arguments` – Bill G Mar 16 '14 at 16:03
@BillG: Try This: `DateTime.TryParse(reader["IDIssue"], CultureInfo.InvariantCulture, DateTimeStyles.None, out issueDate)` – Sudhakar Tillapudi Mar 16 '14 at 16:10
@Sudhakar Tillapudi: sorry to disappoint you, but another error ` has some invalid arguments` – Bill G Mar 16 '14 at 16:20
@BillG: are you sure? i dont think the error is coming from `DateTime.TryParse ` statement but from somewhere else ?could you please edit your question with latest code with which your trying? – Sudhakar Tillapudi Mar 16 '14 at 16:26
@Sudhakar Tillapudi: `DateTime.TryParse(reader["ReceivedDate"], CultureInfo.InvariantCulture, DateTimeStyles.None, out Received);` Tried to commented this line and there was no error – Bill G Mar 16 '14 at 16:28
`Error 1 The best overloaded method match for 'System.DateTime.TryParse(string, System.IFormatProvider, System.Globalization.DateTimeStyles, out System.DateTime)' has some invalid arguments ` – Bill G Mar 16 '14 at 16:31
Might be you can just force the first param ToString() which should make it work with minimal changes. Try to share via dotnetfiddle for the first time, so we'll see. – Karl Kieninger Mar 16 '14 at 16:52

score 0 · Answer 4 · edited Dec 18 '19 at 01:18

0

Try :

expiryseartxt.Text = (reader["IDExpiry"].ToShortDateString());

Or this :

expiryseartxt.Text = reader.GetDateTime().ToShortDateString();

edited Dec 18 '19 at 01:18

Mohammad

1,549
1
15
27

answered Dec 18 '19 at 00:24

Dharmin Shah

1
2

get only date from sql

4 Answers4