February 6, 2015 at 5:15 am
Hi,
The following code is pulling back odd date for 'TEST' column - why would that be? Basically I just want to make sure that I convert the "dia.entry_date+dia.entry_time 'TEST1'" column in my select statement to a date time.
SELECT *
FROM
(
SELECTDIA.reference
,CONCAT(DIA.entry_text,DIAE.entry_text) AS 'Callback Details'
,ROW_NUMBER() OVER (PARTITION BY DIA.reference ORDER BY dia.entry_date+dia.entry_time) 'LoggedNote'
,dia.entry_date+dia.entry_time 'TEST1'
,CAST(ROW_NUMBER() OVER (PARTITION BY DIA.reference ORDER BY dia.entry_date+dia.entry_time) AS datetime) 'TEST'
FROM [dbo].[em_diary] dia
INNER JOIN
[dbo].[em_diary_ext] diae
ON DIA.reference = DIAE.reference
)as sub
where sub.LoggedNote = 1
AND SUB.reference = '26175'
So the date in TEST1 is - 2015-02-04 09:21:00.000
The date for TEST is - 1900-01-02 00:00:00.000
The reason I want to convert to date time is when I just have the TEST1 column with the SUB.reference = '26175' removed from WHERE clause, I get the following error -
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
February 6, 2015 at 5:20 am
Can you explain what you are expecting this row to do please?
CAST(ROW_NUMBER() OVER (PARTITION BY DIA.reference ORDER BY dia.entry_date+dia.entry_time) AS datetime) 'TEST'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 6, 2015 at 5:22 am
If I use TEST1 I get the error in my original post.
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Therefore I am trying to use TEST in order to CAST the two fields added together to ensure that the result is a datetime.
February 6, 2015 at 5:34 am
The issue looks to be the with dia.entry_time column.
In the database that I'm working on - it's set to varchar instead of a time. I can't change it at database level so what I need to do is convert that varchar of the time to a time.
Example we have enteries in the dia.entry_time like - 1044. Instead of 10:44.
So I need to know how to convert them to a proper time
February 6, 2015 at 5:50 am
If you only need the time portion for ordering, you should be OK just converting to int:
ROW_NUMBER() OVER (PARTITION BY DIA.reference ORDER BY dia.entry_date,CAST(dia.entry_time AS int))
Of course, it all depends on the quality of the data in that column. The above will work for the example you gave. If you've got other values with colons and such like in, you'll need something a bit more sophisticated.
John
February 6, 2015 at 5:58 am
Hi Guys,
All sorts of shit in that column - grrrrr.
Examples - 1700, 8-00 ,9.30AM
No change of converting all of that.
February 6, 2015 at 6:03 am
Then I'm afraid there's no easy answer. You're going to need to go through that column and clean it up. Once you've done that, change the data type to time so you don't get in the same position again.
John
February 6, 2015 at 6:07 am
Totally appreciate that guys - so frustrating that a massive company still manage to write useless databases.
February 8, 2015 at 8:54 am
@ SQL TRYER
this happens everywhere. I just went through a major clean up where the previous developer defined a classic datetime field as nvarchar(50) and over the years people typed in all kind of stuff to include "see above" to reference the value that shows above that field on the form.
Beyond frustrating, but short of manual clean up there is nothing else that you can do grammatically.
I ended up using ISDATE function to identify the ones that are not valid dates and passed it back to business for manual correction.
I think you should let whoever created the mess fix it (assuming they are still with the company).
Petr
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply