February 16, 2009 at 11:06 am
Hi,
I appolize if this question get duplicated as i lost the id when i was posting and tried to check again but couldn't see this question into forum.
I am using following query with DateDiff function to count No of Days but getting following error.
Unfortunatek my Start and Stop fileds data type is set up as nVarchar(50) instead of Data or DateTime.
This was working fine but after researching this forum and after that my table data, i found that user is not entering data into stadard format but they have entered data in following format.
Data:
2008-09-01T09:00:01
2009-01-27T14:32:37.7389657-08:00
Error:
Conversion failed when converting datetime from character string.
Query:
SELECT LTrack.JobCount,
datediff(day,Start,Stop)as "No Of Days"
FROM PTrack INNER JOIN
LTrack ON PTrack.PTrackID = LTrack.PTrackID INNER JOIN
STrack ON PTrack.STrackID = STrack.STrackID INNER JOIN
Track ON STrack.TrackID = STrack.TrackID
Thanks.
February 16, 2009 at 12:26 pm
[font="Verdana"]Whoa!
Those look like timezone dependent dates (I'm guessing the -8.00 on the end of one is GMT - 8 hours.)
You will need to use some sort of case statement and a pattern with a like to identify the dates in thsi format, and then construct some logic to pull the strings apart and assemble them as dates.
I'd suggest you do that on the source data, possibly while it is loaded, so that when it's stored into the database, it's in the correct format already.
[/font]
February 16, 2009 at 12:58 pm
Storing date/times in SQL server as a varchar (or nvarchar) data type is usually a very bad idea.
The following TSQL shows how you can convert your date/time string with time zone offset to a UTC datetime (almost synonymous with GMT). Note that the SQL Server datetime data type cannot store 7 decimal places of fractional seconds. It is only precise to 3.33 milliseconds, so 3 decimal places is more than sufficient.
DECLARE @dt nvarchar(50)
SELECT @dt = '2009-01-27T14:32:37.7389657-08:00'
SELECT CASE
WHEN @dt LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9][0-9][-+][0-9][0-9]:[0-9][0-9]' THEN
DATEADD(minute,
(CASE SUBSTRING(@dt, 28, 1) WHEN '-' THEN 1 ELSE -1 END)
* DATEDIFF(minute, 0, CONVERT(datetime, SUBSTRING(@dt, 29, 5))),
CONVERT(datetime, SUBSTRING(@dt, 1, 23))
)
ELSE /* try implict conversion: will work fine for format 'yyyy-MMddTHH:mm:ss' */
CONVERT(datetime, @dt)
END
EDIT: corrected sign of UTC correction
February 16, 2009 at 1:15 pm
Thanks much Andrew for oyur quick resposne.
I am not much SQL expert, could you please show me how to use in my query below?
Start and Stop is my actual date columns in varchar data type.
I am using this select in my sp_send_dbmail program to send extract in text format.
SELECT LTrack.JobCount,
datediff(day,Start,Stop)as "No Of Days"
FROM PTrack INNER JOIN
LTrack ON PTrack.PTrackID = LTrack.PTrackID INNER JOIN
STrack ON PTrack.STrackID = STrack.STrackID INNER JOIN
Track ON STrack.TrackID = STrack.TrackID
Thanks once again for your help!
February 16, 2009 at 1:50 pm
What are the different date/time formats you've already got in your table?
Is there any sort of control (e.g. front-end validation) over what strings can be inserted?
How is data inserted into the table, e.g. via a stored procedure?
I suggest that before you even think about trying to run your query you need to address the problem of the different date/time character string formats stored in the Start and Stop columns of your table.
If for some reason, you are unable to change the data type of the Start and Stop columns to datetime, then you must at least standardize the formatting of the date/time fields to something that SQL Server can implicitly convert to a datetime, and enforce this formatting via check constraints, triggers, or a stored procedure used to insert the data. The yyyy-MM-ddTHH:mm:ss is a good candidate for such a standard format.
February 16, 2009 at 2:19 pm
If all you need are dates and can do with out time of day and assuming that all rows have date first with similar format, You can try this:
datediff(d,convert(datetime,left(start,10)),convert(datetime,left(stop,10)))
However front end coding to handle formatting is the best method going forward.
February 16, 2009 at 2:54 pm
If all you need are dates and can do with out time of day and assuming that all rows have date first with similar format, You can try this:
datediff(d,convert(datetime,left(start,10)),convert(datetime,left(stop,10)))
This is not safe because of the time zone offset:
For instance the local time coresponding to
2009-01-27T07:32:37.7389657+08:00
is on the previous day, i.e.
2009-01-26T23:32:37.739
February 16, 2009 at 3:33 pm
You are correct, this seems to be an ugly situation to begin with.
There are a lot of assumptions to be made otherwise it will still fail. The first datetime example would fail.
My assumptions are the date is always first and in similar format, you only need a count of days and timezone offset is the same for both start and stop (will give the basic same day count as local time)
you can make it more accurate by doing the following based off the same assumptions
datediff(hour,convert(datetime,left(start,23),126),convert(datetime,left(start,23),126))/24
I would use this only as evaluation with the understanding that there is an factor of error and not as a data clean up.
Depending on how accurate the data needs to be and how much data there is, it maybe more beneficial to manually clean this up and enforce a datetime format going forward. Better to do it now before it gets worse.
February 16, 2009 at 3:38 pm
[font="Verdana"]I'm wondering whether the reason that the datetimes have been posted in the string form is that the application needs to preserve the timezone data.
I think the best bet would be to write a function that converts the timezoned entry into a normal SQL Server datetime. Then you can just use the function within the queries.
[/font]
February 17, 2009 at 9:03 am
Thanks much Andrew.
Please see the below resposne and my question.
What are the different date/time formats you've already got in your table?
==>We haven't not any idea as we are on primary stage and we jsut got the sample file.
Is there any sort of control (e.g. front-end validation) over what strings can be inserted?
==> Right now we aren to doing validation at Front end side
How is data inserted into the table, e.g. via a stored procedure?
==> Data is inserted via web service (incoming file is in XML format).
I suggest that before you even think about trying to run your query you need to address the
problem of the different date/time character string formats stored in the Start and Stop columns
of your table.
==> I have already addressed this issue
If for some reason, you are unable to change the data type of the Start and Stop columns to datetime,
then you must at least standardize the formatting of the date/time fields to something that
SQL Server can implicitly convert to a datetime, and enforce this formatting via
check constraints, triggers, or a stored procedure used to insert the data.
The yyyy-MM-ddTHH:mm:ss is a good candidate for such a standard format.
==> We can try to change the data type is possible.
What you suggest the best data type for both these fields?
Once i use DateTime, sql server allow to use DaeTime datatype into same table for both the fields?
After that i might use the Check constraint to enforce data as standard Date format, right?
Appreciate your time and help!
Regards,
Poratips
February 17, 2009 at 9:07 am
Thank you, I will ru nthis query and let you know.
February 17, 2009 at 9:16 am
Thanks Dom.
you are right that it will be a problem later if we don't change it right now.
I am waiting to see what kind of different format i am getting into incoming file and let see if i can ask our front end guy who is loading this kind of XML file and he can validate at his end so i can have clean data into DB.
If we can't validate at Front end side then what you suggest best to do on my side while running my query?
Appreciate your help!
Regards,
Pora
February 17, 2009 at 9:18 am
Thanks Bruce for your nice tips but i am not a SQL guru and if you don't mind can you provide me an example or function which can help me out?
Thanks once again!
February 17, 2009 at 11:18 am
Thanks Dom for making correction but while running your following query, i got the following error:
datediff(hour,convert(datetime,left(start,23),126),convert(datetime,left(start,23),126))/24
Do you mean to use here Stop instead of Start, right?
(datetime,left(start,23),126))/24
So your query should read like:
datediff(hour,convert(datetime,left(start,23),126),convert(datetime,left(start,23),126))/24
but its still same error.
Error:
Conversion failed when converting datetime from character string.
Thanks,
February 18, 2009 at 3:40 pm
yes, I meant start and then stop in the datediff...
You know, that could be a result of having different date formats (no T between date and time would be my first guess)
One thing you can try is to remove the input style of 126 and let SQL Server interpret the input itself, but this can be a little risky because if it comes back with dates they may not be the correct ones.
If you can find out the different styles of date formats in the columns then you can program to decipher it.
The problem with it being a text is that it will allow anything in it and there is a chance that one row has something that is not a date at all and is causing the conversion to fail.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply