CODEWITHSUNDEEP
sql-serverdatetimems-access
Arthur Verrept
Arthur Verrept

Reputation: 45

C# SQL WHERE DateTime Today

I have a database table in Access that looks like this:

database

I have a problem with my SQL, I want a 2 WHERE's in my SQL Statement which I have tried, and it looks like this in server explorer

SELECT Created, [Action], 
       ConnectionLoc, 
       ConnectionSystem, 
       Resource, 
       [Text], 
       RecordId, 
       ToVal, 
       ClientName    
  FROM tblAudit
 WHERE (ClientName = '*Variable*') AND 
       ('WHERE Created'  = '[DateTime].Today')

I want only the records with today's date to show.... Hence this bit. Yet nothing is returned. I know the first WHERE is correct since on its own it outputs fine. It's the second one which is wrong.

Upvotes: 1

Views: 3596

Answers (6)

Miguel
Miguel

Reputation: 26

This is so simple. if you have column on database that named "date" this code works. this using mysql server. 

private void getdatawheredatenow()
{
MySqlConnection connect = new MySqlConnection(con);
connect.Open();
try
{
string sql="SELECT * FROM DATE WHERE date(date) = date(now())";
MySqlDataAdapter da = new MySqlDataAdapter(sql, connect);
DataTable ds = new DataTable();
da.Fill(ds);
datagrid.Datasource = ds;
}
catch(Exception aa)
{
Console.WriteLine(aa.ToString());
}
}

Upvotes: 0

Arthur Verrept
Arthur Verrept

Reputation: 45

Hating to answer my own question... Firstly thanks to

  • Kevin Brechbühl

    and

  • shawnt00

the problem was the date format, in my access database it was dd/MM/yyyy but visual studio was looking for mm/DD/yyyy hence the errors

Upvotes: 2

Gustav
Gustav

Reputation: 55816

As this is Access, you can do:

FROM 
    tblAudit
WHERE 
    (ClientName Like '*Variable*') AND (Created = Date())

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123484

For a query against an Access table you can use

... WHERE ... AND [Created]=Date()

if the [Created] column contains just date values (without a time component).

Note also that [ClientName] = '*sometext*' will perform a literal search, not a wildcard search. To do a wildcard search from C# you need to use [ClientName] LIKE '%sometext%'.

Upvotes: 0

Kevin Brechbühl
Kevin Brechbühl

Reputation: 4727

Try this using GETDATE() function:

SELECT
    Created,
    [Action],
    ConnectionLoc,
    ConnectionSystem,
    [Resource],
    [Text],
    RecordId,
    ToVal,
    ClientName
FROM tblAudit
WHERE ClientName = 'Variable'
    AND CAST(Created AS DATE) = CAST(GETDATE() AS DATE)

Upvotes: 1

Akshey Bhat
Akshey Bhat

Reputation: 8545

  WHERE ClientName = 'Variable' AND cast( Created as date) = cast(getdate() as date)

Try this. Where clause is required only once

Upvotes: 2

Related Questions