Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Convert DateTime and Date Variables to Integers with Microsoft SQL Server

Using Microsoft Server Management Studio I am trying to work out the difference in days between 2 dates. The two dates are:
[BetDate] - datetime
[FirstPlacedDate] - date

I'm a bit of a SQL newbie. I've tried converting them to integers using Cast, Convert and DateDiff functions but haven't been able to get any of them to work
0
xyobi
Asked:
xyobi
  • 4
  • 4
1 Solution
 
ThomasianCommented:
Have you tried:
SELECT DATEDIFF(day,[FirstPlacedDate],[BetDate])

Open in new window


If that didn't work for you, can you post the sql query you tried and the error message you got?
0
 
xyobiAuthor Commented:
it tells me
Conversion failed when converting date and/or time from character string.

i do have it inside a ceiling function which may be impacting it???

Ceiling(DateDiff(day,[FirstPlacedDate],[BetDate]) / 25)

Open in new window

0
 
ThomasianCommented:
No, that should not cause the error.

Are you sure that the datatype for both fields are date/datetime? The error should be caused by trying to change an invalid char/varchar data to date/datetime data type.


Btw, you should change "/ 25" to "/ 25.0" otherwise it will always return an integer (rounded down).
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
xyobiAuthor Commented:
They are definitely dates.

Another thing I just thought of, would it have an impact if some of the fields were null?
0
 
ThomasianCommented:
>>Another thing I just thought of, would it have an impact if some of the fields were null?
It will return null if any of the fields is null.

Are you sure that that statement is the one that causes the error? Can you post the whole query you tried?
0
 
xyobiAuthor Commented:
SELECT [Datawarehouse].[dbo].[fct_AccountSummary].FirstPlacedDate
	, [Datawarehouse].[dbo].[fct_AccountSummary].FirstPlacedSport
	, Ceiling(DateDiff(day,[Datawarehouse].[dbo].[fct_AccountSummary].FirstPlacedDate,[Datawarehouse].[dbo].[fct_Bets].BetDate) / 25.0)
	, [Datawarehouse].[dbo].[fct_Bets].Sport
	, COUNT([Datawarehouse].[dbo].[fct_Bets].DWAccountID) as "Number of Bets"
FROM [Datawarehouse].[dbo].[fct_AccountSummary] (nolock)
Left Join [Datawarehouse].[dbo].[fct_Bets] (nolock)
On [Datawarehouse].[dbo].[fct_AccountSummary].DWAccountID = [Datawarehouse].[dbo].[fct_Bets].DWAccountID
Where ([Datawarehouse].[dbo].[fct_AccountSummary].FirstPlacedDate between '24-09-2008' and '25-09-2010')
	and ([Datawarehouse].[dbo].[fct_Bets].BetDate between '24-09-2008' and '25-09-2011')
	and [Datawarehouse].[dbo].[fct_AccountSummary].FirstPlacedDate is not null
	and [Datawarehouse].[dbo].[fct_Bets].BetDate is not null
Group By [Datawarehouse].[dbo].[fct_AccountSummary].FirstPlacedDate
	, [Datawarehouse].[dbo].[fct_AccountSummary].FirstPlacedSport
	, Ceiling(DateDiff(day,[Datawarehouse].[dbo].[fct_AccountSummary].FirstPlacedDate,[Datawarehouse].[dbo].[fct_Bets].BetDate) / 25.0)
	, [Datawarehouse].[dbo].[fct_Bets].Sport

Open in new window

0
 
ThomasianCommented:
The problem is you are entering the dates in 'dd-mm-yyyy' format while sql server interprets them as 'mm-dd-yyyy'. To avoid these errors, always enter dates in 'yyyy-mm-dd'

e.g.

'24-09-2008' and '25-09-2011'

should be

'2008-09-24' and '2011-09-25'
0
 
xyobiAuthor Commented:
hey... you are a legend. apologies for having to deal with the rookie mistake
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now