Solved

Convert DateTime and Date Variables to Integers with Microsoft SQL Server

Posted on 2011-09-27
8
221 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

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 is using more virtual memory. 5 83
Replace Dates in query 14 38
Sql Server group by 10 23
Whats wrong in this query - Select * from tableA,tableA 11 28
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

815 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

10 Experts available now in Live!

Get 1:1 Help Now