Solved

SELECT DATETIME UK to US Format

Posted on 2008-06-11
5
463 Views
Last Modified: 2010-04-21
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
Comment
Question by:ziwez0
  • 2
  • 2
5 Comments
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 500 total points
ID: 21759070
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
 
LVL 6

Expert Comment

by:Bruce_1975
ID: 21759133
Hi,
try to use yyyy instead of YYYY.

Regards,
Bruce
0
 
LVL 2

Author Comment

by:ziwez0
ID: 21759251
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
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 21759306
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
 
LVL 2

Author Closing Comment

by:ziwez0
ID: 31466100
Thank-you
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question