Solved

SELECT DATETIME UK to US Format

Posted on 2008-06-11
5
456 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Loop not working 29 48
MS SQLK Server multi-part identifier cannot be bound 5 28
Remove greater than sign 3 50
Not showing JavaScript in the list 5 40
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…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now