I am tired of working this out myself , i cannot understand the way the sql datetime field works its really so bad to understand , its been 2 hours now.
Problem:
I have a access database and i move it to sql server 2008 r2 table (import all).
Now i have dates in sql table like;
2012-04-03 00:00:00.000
2012-04-04 00:00:00.000
2012-04-05 00:00:00.000
2012-04-04 00:00:00.000
2012-04-05 00:00:00.000
2012-04-09 00:00:00.000
2012-04-09 00:00:00.000
And the how this sql works with date i cannot even figure out wheter its yyyy/mm/dd or yyyy/dd/mm.
Because when i run this query:
Select * from Contractor_Appointments where app_date Between '02/04/2012' and '02/04/2012'
It should return all results from 2/apr/2012 instead i get :
2012-02-04 00:00:00.000
2012-02-04 00:00:00.000
2012-02-04 00:00:00.000
2012-02-04 00:00:00.000
And when i insert into the database a new record it takes the date as:
2012-02-04 00:00:00.000
And then shows up in the above query :( please help i am tired of this sql date processing. My computer date is set to 02/04/2012 .
Any suggestion:
Thank you
You have two issues; one is understanding how SQL Server deals with dates. In your example above, your query is actually:
SELECT * from Contractor_Appointments where app_date Between '02/04/2012 00:00:00' and '02/04/2012 00:00:00'
You are asking for all the appointments between midnight and midnight, which isn't so bad, except for your second problem. You are using an ambiguous date time format. How SQL Server interprets this date will depend on a variety of settings - your locale settings, and the language the database is using for example. In your case, it's actually interpreting it as 4th Feb 2012, which suggests you are running the query on a US machine, or your database lanaguage is for a region where the date format is MM/dd/yyyy
Use an invariant date time format instead - this is unambiguous and should be independent of regional settings. An example of the invariant date time format is yyyyMMdd HH:mm:ss
So your query becomes:
SELECT * from Contractor_Appointments where app_date Between '20120402 00:00:00' and '20120403 00:00:00'
Which will give you all the appointments on the 2nd April 2012 (i.e. all those between midnight on 2nd April and midnight on the 3rd April).
You could just as easily write your query this way given your datetimes all appear to be midnight:
SELECT * FROM Contractor_Appointments WHERE app_date = '20120402 00:00:00'
See this answer for more information on how best to format dates when querying in SQL Server.
EDIT
For your insert, the same applies. You need to use an unambguous datetime format. See this question for more info, but effectively:
INSERT INTO yourtable (date_column) VALUES ('20120402') will give you what you want.
If you have a DateTime object, then calling DateTime.ToString("yyyyMMdd HH:mm:ss") will give you the correctly formatted string.