asked on

SELECT DATETIME UK to US Format

I havent had to do a select where dattime =... for a long time now (strange), but it reminded me why I hate doing them (why does the WHERE [Date] has to be different from everything else??)

Anyway, I just want to return a count based on a selected day.
string strCountYesterday = "SELECT COUNT(OID) AS RMA FROM Table WHERE dtStamp =@dtStamp_3";
DateTime dtToday = DateTime.Today;
dtToday = dtToday - new TimeSpan(1, 0, 0, 0);
string strToday = Convert.ToString(dtToday.ToShortDateString());
CultureInfo ci;
ci = CultureInfo.CreateSpecificCulture("en-US");
DateTime dtYesterday = DateTime.Parse(strToday,ci);
ci.DateTimeFormat.ShortDatePattern = "MM/dd/YYYY";
CommandCommandText = strCountRMAYesterday;
Command.Connection = Connection;
Command.Parameters.AddWithValue("@dtStamp_3", Convert.ToDateTime(dtYesterday,ci));
//more code...
//dtYesterday format 06/10/2008 00:00:00

but im still not getting any records back, what have i done wrong?

ASKER CERTIFIED SOLUTION

Gautham Janardhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bruce_1975

Hi,
try to use yyyy instead of YYYY.

Regards,
Bruce

ziwez0

ASKER

gauthampj:, that did the trick... could you explain this

.. WHERE cast(floor(cast(dtStamp as float)) as datetime)=@dtStamp_3";

why so much work for just getting a date???

thanks

Gautham Janardhan

dtStamp might have time with it in the column so

first we convert datetime into float then floor it which will give us the date component and then convert back to datetime

ziwez0

ASKER

Thank-you