Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

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?
0
ziwez0
Asked:
ziwez0
  • 2
  • 2
1 Solution
 
Gautham JanardhanCommented:
try changing query to
"SELECT COUNT(OID) AS RMA FROM Table WHERE cast(floor(cast(dtStamp as float)) as datetime)=@dtStamp_3";

and
Command.Parameters.AddWithValue("@dtStamp_3"
,DateTime.Today.AddDays(-1));

0
 
Bruce_1975Commented:
Hi,
try to use yyyy instead of YYYY.

Regards,
Bruce
0
 
ziwez0Author Commented:
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
0
 
Gautham JanardhanCommented:
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
0
 
ziwez0Author Commented:
Thank-you
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now