473,240 Members | 1,622 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,240 software developers and data experts.

Help needed for datediff function for SQL query

Hi Experts,
I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.

I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."

select case
when convert(datetime,Discharge_Date,103) = '' then
datediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())
else
datediff(day,CONVERT(datetime,Placement_Date,
103),CONVERT(datetime,Discharge_Date,103))
end NoOfDays
from Placement_Details
So please tell me where I am wrong?
Any help will be appriciated.
Regards
Dinesh

Apr 9 '07 #1
3 12153
Without having your table structure and sample data, it is a bit difficult
to troubleshoot, but a few notes that can help you:

- Based on the style 103 that you use for the CONVERT function, seems you
are converting date stored as string in format "dd/mm/yyyy" to a datetime
type. The error that you get indicates that the date string cannot be
converted, because the day, month, or year portion is out of the allowed
range. You can easily simulate the error if you run something like this:
SELECT CONVERT(datetime, '23/13/2007', 103). The month cannot be 13 so it
fails with the error you get. You can use the LEFT, RIGHT and SUBSTRING
functions to extract the day, month, and year portion of both columns
(Placement_Date and Discharge_Date) and check for invalid values, then clean
your data. For year the down side value is 1753 (that is the lower limit for
datetime data type).

- It is always best to keep date values in columns of datetime data type.
That way you do not have to worry about the format and can benefit of using
all datetime functions with no need to convert.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Apr 9 '07 #2
Dinesh (di*******@gmail.com) writes:
I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.

I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."

select case
when convert(datetime,Discharge_Date,103) = '' then
This does not really make sense. A datetime value cannot be the
empty string. By the default the empty string will convert to the
datetime value 1900-01-01 00:00:00.000, but I don't think that is
what you want.

Assuming a reasonably designed database, the test would be

WHEN Discharge_Date IS NULL THEN

Then again, since you seem to store dates in character values, this
may not be a reasonably designed database. :-)

Anyway, the problem appears to be that you have junk in your character
columns. As Plamen said, you should use the datetime data type to store
your dates instead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 9 '07 #3
Dinesh (di*******@gmail.com) writes:
I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.

I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."

select case
when convert(datetime,Discharge_Date,103) = '' then
datediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())
else
datediff(day,CONVERT(datetime,Placement_Date,
103),CONVERT(datetime,Discharge_Date,103))
end NoOfDays
from Placement_Details
So please tell me where I am wrong?
Oh, by the way, this SELECT should give you the rows with bad dates:

SET DATEFORMAT DMY
go
SELECT Discharge_Date, Placement_Date
FROM Placement_Details
WHERE isdate(Discharge_Date) = 0 OR
isdate(Placement_Date) = 0
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 9 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: rong.guo | last post by:
I am trying to use table 'Account' and table 'Balance' to get the ideal result table listed below, but my query is too cumbersome. If an account opened 10 years ago, I would have 12*10=120 rows in...
1
by: rsarath | last post by:
Hello, I have the following setup and I would appreciate any help in improving the performance of the query. BigTable: Column1 (indexed) Column2 (indexed) Column3 (no index) Column4 (no...
1
by: PMBragg | last post by:
ORINGINAL Post >Thank you in advance. I'm trying to pull all inventory items from December >of the previous year back to 4 years for my accountant. I know this can be >done, but I'm drawing a...
5
by: mcbill20 | last post by:
Hello all. I have a really basic question that I hope someone has a better answer for. I apologize in advance-- I know this is probably a really basic question but I am used to Oracle rathern than...
6
by: kevinjwilson | last post by:
I am trying to get the date difference between two dates but I don't want the function to include weekends in the calculation. Does anyone have an idea on how to make this work?
2
by: rdraider | last post by:
Hi, We have a lame app that uses 2 datetime(8) fields, 1 stores the date, the other the time. example query: select aud_dt, aud_tm from orders results: aud_dt ...
6
by: Twobridge | last post by:
I hope someone can help me out with my problem. I have found a sql statement that basically pulls all bills filed within a certain time period and the payments made on those bills with in the...
7
by: Techhead | last post by:
I have a date/time field with a sql format of "datetime" The actual date/time data format is MM/DD/YYYY^hh:mm:ss:pm or "1/25/2007 12:00:16 AM" Both the date and time are combined on the same field...
2
by: ankitmathur | last post by:
Hi Friends, I have been trying to use this query to get all records that will expire within the next 30 days from the current date. While I am getting records with the same query what is...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.