Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SELECT DATETIME UK to US Format

Posted on 2008-06-11
5
Medium Priority
?
486 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

704 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