SQL DateDIFF Trouble - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL DateDIFF Trouble

Author

Topic

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2009-01-08 : 08:06:25

Hi All

I am running SQL 2005 DB and based in UK

My code belows shows me the datediff using month instead of day
Do you know how I can force SQL 2005 to calculate it in UK format instead of US format

DATEDIFF(day, dbo.Inbox.DateAdded, GETDATE()) AS day,

If it is that easy, everybody will be doing it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 08:11:46

15 days are 15 day both in US and UK. There is no special format for a day interval.
Also read this article carefully how DATEDIFF behaves http://www.sqlteam.com/article/datediff-function-demystified.
I know I have suggested this link to you before, but it can't hurt to have a reminder, can it?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 08:21:35

quote:
Originally posted by OBINNA_EKE

Hi All

I am running SQL 2005 DB and based in UK

My code belows shows me the datediff using month instead of day
Do you know how I can force SQL 2005 to calculate it in UK format instead of US format

DATEDIFF(day, dbo.Inbox.DateAdded, GETDATE()) AS day,

If it is that easy, everybody will be doing it

Is field DateAdded datetime?

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2009-01-08 : 08:31:36

Yes dbo.Inbox.DateAdded is of type DATETIME , I have read Peso's article, it is not my case.
My problem is that, it is treating my date as a US date instead of UK date

If it is that easy, everybody will be doing it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 08:33:49

No, if the DateAdded column really is a DATETIME column the DATEDIFF function will work!
A column of DATETIME datatype is nothing more than a numeric serial number.

Also, the DATEDIFF function returns a number, not a date.
You are not telling us the whole picture here.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 08:34:32

quote:
Originally posted by OBINNA_EKE

Yes dbo.Inbox.DateAdded is of type DATETIME , I have read Peso's article, it is not my case.
My problem is that, it is treating my date as a US date instead of UK date

If it is that easy, everybody will be doing it

so are you trying to pass date values through parameters? i dont think DATEDIFF(day, dbo.Inbox.DateAdded, GETDATE()) will chabge based on your local date settings as both the date values DateAdded and GETDATE() will be interpreted in same format and you will get correct result.
perhaps you could explain with some data what discrepancy you're facing.

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2009-01-08 : 08:41:14

Ok this is the evidence here, a picture says a thousand words thanks ppl
http://www.virginigeria.com/BulkSMS/newfolder1/Evidence.jpg

If it is that easy, everybody will be doing it

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-01-08 : 08:46:50

a picture, in this case, stinks. How about some DDL and sample data instead?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 08:49:06

Seeing picture i dont think DateAdded is of datetime datatype

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 08:49:52

quote:
Originally posted by OBINNA_EKE

Ok this is the evidence here, a picture says a thousand words thanks ppl
http://www.virginigeria.com/BulkSMS/newfolder1/Evidence.jpg

Exactly! Your DATEDIFF calculation is named NoOfDays (which is presented in numeric value, as we have told you) and is the column prior to your DateAdded column.

If you persist in displaying your DateAdded column in a specific date format, see or read Books Online for examples how to format the datevalue accordingly.

SELECT CONVERT(VARCHAR(30), DateAdded, 103),
CONVERT(VARCHAR(30), DateAdded, 101)
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2009-01-08 : 08:52:38

Well, Peso, I understand. It was working on SQL 2000 until I migrated to SQL 2005
Here is my DDL

CREATE TABLE [dbo].[Inbox](
[InboxID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [uniqueidentifier] NULL,
[Message] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Sender] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateAdded] [datetime] NULL CONSTRAINT [DF_Inbox_DateAdded] DEFAULT (getdate()),
[Froms] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsDeleted] [bit] NULL,
[KeywordID] [int] NULL,
[HasRead] [bit] NULL CONSTRAINT [DF_Inbox_HasRead] DEFAULT ((0))
) ON [PRIMARY]

If it is that easy, everybody will be doing it

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-08 : 08:54:25

formatting should be done at the client.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 08:58:19

Filling for Madhi, are you?

E 12°55'05.63"
N 56°04'39.26"

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2009-01-08 : 08:59:47

Spirit, I need to do this inside sql as per spec

If it is that easy, everybody will be doing it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 09:12:17

quote:
Originally posted by OBINNA_EKE

Spirit, I need to do this inside sql as per spec

So what is wrong wiht my suggestion made 01/08/2009 : 08:49:52 ?

E 12°55'05.63"
N 56°04'39.26"

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2009-01-08 : 09:17:48

Hi Peso, I implemented your suggestion and I still got the same result.
I think this is to do with SQL 2005 Language setup. I am investigating/googling now

Thank u very much

If it is that easy, everybody will be doing it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 09:20:54

It's called Regional Setting in your operating system.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 05:10:57

Ah..! Those memories http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69421

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources