?
Solved

Convert DateTime and Date Variables to Integers with Microsoft SQL Server

Posted on 2011-09-27
8
Medium Priority
?
228 Views
Last Modified: 2012-08-14
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
Comment
Question by:xyobi
  • 4
  • 4
8 Comments
 
LVL 22

Expert Comment

by:Thomasian
ID: 36714261
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
 

Author Comment

by:xyobi
ID: 36714287
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
 
LVL 22

Expert Comment

by:Thomasian
ID: 36714299
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
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.

 

Author Comment

by:xyobi
ID: 36714319
They are definitely dates.

Another thing I just thought of, would it have an impact if some of the fields were null?
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36714337
>>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
 

Author Comment

by:xyobi
ID: 36714346
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
 
LVL 22

Accepted Solution

by:
Thomasian earned 2000 total points
ID: 36714355
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
 

Author Comment

by:xyobi
ID: 36714359
hey... you are a legend. apologies for having to deal with the rookie mistake
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

594 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