0

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

confusedMind
  • 2,573
  • 7
  • 33
  • 74
  • possible duplicate of [what is the correct way to format a datetime in SQL server datetime field](http://stackoverflow.com/questions/1931530/what-is-the-correct-way-to-format-a-datetime-in-sql-server-datetime-field) – dash Apr 02 '12 at 21:34

4 Answers4

0

Try running dbcc useroptions to see if your Language is set to us_englishand your dateformat is set to mdy

sazh
  • 1,792
  • 15
  • 20
  • The thing is once done here i have to upload this to my hosting which is in USA? Will it take effect only on my pc? Or this database and will show effect on where ever i upload it? i mean to say this command works on the sql server or the databases – confusedMind Apr 02 '12 at 21:09
  • and yes the date is set to mdy and languge to us_english – confusedMind Apr 02 '12 at 21:11
0

Why not the same format?

Select * 
from Contractor_Appointments 
where app_date  Between '2012-02-04' and '2012-02-04'

To find out what part is the month

select datepart(MONTH,app_date)
from from Contractor_Appointments
Adrian Iftode
  • 15,465
  • 4
  • 48
  • 73
0

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.

Community
  • 1
  • 1
dash
  • 89,546
  • 4
  • 51
  • 71
  • The date can be between two months aswell i just put this as an example :) – confusedMind Apr 02 '12 at 21:16
  • Can you please post the code you are using to perform the insert? Your problem is likely that you are using a datetime from a US machine. – dash Apr 02 '12 at 21:16
  • Between two months is simple - you take the first of your current month, and the first of the month after. So for all of April, the BETWEEN clause becomes: BETWEEEN '20120401 00:00:00' AND '20120501 00:00:00' – dash Apr 02 '12 at 21:18
0

To understand the format which the date is stored, try converting it to something more clear. Using the SQL Server CONVERT function:

SELECT TOP 10 
    CONVERT(VARCHAR(26), app_date, 109),
    *
FROM Contractor_Appointments

This will return a much more clear date, example: Apr 28 2006 12:32:29:253PM

See this reference for more conversion formats: http://www.sql-server-helper.com/tips/date-formats.aspx

Khan
  • 17,904
  • 5
  • 47
  • 59
  • Yes but i am having issue on putting the where condition on date :) – confusedMind Apr 02 '12 at 21:20
  • Personally I have always written my dates in `yyyy-MM-dd hh:mm:ss` as an input into sql server and have never run into an issue. But if you're not certain, convert it to a datetime, and convert it back and see if you get expected results. `SELECT CONVERT(VARCHAR(26), CONVERT(DATETIME, '2012-02-04 00:00:00.000'), 109)` – Khan Apr 02 '12 at 21:25