CODEWITHSUNDEEP
c#sqlsql-server
Boom
Boom

Reputation: 29

How to convert yyyy-mm-dd to yyyymmdd in where clause of SQL select command

The format in the database for date column is yyyymmdd but my code in aspx.cs passes the parameter value as yyyy-mm-dd. How can I convert yyyy-mm-dd to yyyymmdd in the WHERE clause if my select statement?

aspx.cs code:

adapter.SelectCommand = new SqlCommand("select distinct a.e_id from tenter where  (date='" + Convert(DateTime.Value.ToString()) + "')", myConn);

Upvotes: 1

Views: 2246

Answers (7)

Boom
Boom

Reputation: 29

Thank you very much to all of you. The best answer for me like this:- adapter.SelectCommand = new SqlCommand ("select distinct a.e_id from tenter where (date='" + startdate.Value.ToString().Replace("-",string.Empty) + "')", myConn);

Upvotes: 0

Arockia Raj
Arockia Raj

Reputation: 1

adapter.SelectCommand = new SqlCommand ("select distinct a.e_id from tenter where (date='" + DateTime.ParseExact(("2015-03-18"), "yyyy-MM-dd", null).ToString("yyyyMMdd") + "')", myConn);

Upvotes: 0

A_Sk
A_Sk

Reputation: 4630

use SQL CAST()

SqlCommand cmd=new SqlCommand();
cmd.Connection=con;
cmd.CommandText="select distinct a.e_id from tenter where cast(date as date)=cast(@date as date)";
cmd.Paramenter.AddWithValue("@date",Convert.ToDateTime(DateTime.Value));

Upvotes: 0

Zwo
Zwo

Reputation: 1113

Maybe simply : DateTime.Value.ToString().Replace("-",string.empty)

Upvotes: -1

Soner Gönül
Soner Gönül

Reputation: 98750

Please don't save your DateTime values as a character.

Type your date column as a datetime or datetime2 and pass your DateTime.Now value directly to your parameterized queries. Otherwise, it will be open for SQL Injection attacks.

Read: Bad habits to kick : choosing the wrong data type

As an example;

using(var myConn = new SqlConnection(conString))
using(var cmd = myConn.CreateCommand())
{
    cmd.CommandText = "select distinct a.e_id from tenter where date = @date";
    cmd.Parameters.Add(@date, SqlDbType.DateTime2).Value = DateTime.Now;
    using(var adapter = new SqlDataAdapter(cmd))
    {
       // Do your operations.
    }
}

Also date might reserved word in future releases of SQL Server. You might need to use it as [date] as well.

Upvotes: 4

koushik veldanda
koushik veldanda

Reputation: 1107

It may helps you

You can use this logic and work on your query

 declare @a date= '2014/2/3'
 select replace(@a,'-','')

Upvotes: 0

Jean-François Hl
Jean-François Hl

Reputation: 1

for mysql you can try this :

where date_format(date,'%Y%m%d') = '20150318'

@+jef

Upvotes: -2

Related Questions