Solved

Convert DateTime and Date Variables to Integers with Microsoft SQL Server

Posted on 2011-09-27
8
219 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 500 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql server lock cursor 13 48
Contained Database Collations 6 27
Is the client attempting to connect on the correct network interface? 1 26
SqlAdvisor 2016 3 27
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now