0

I have a column name Endtime of type nvarchar(50) and i am trying to get the max(Endtime) by using the below query:

select COALESCE(CONVERT(nVARCHAR(50), MAX(EndTime), 103), '-') AS EndTime from dbo.vwJobHistory

The results are fine as long as Year is same (12/31/2015) but as soon as i put some data with year (01/22/2016) , the query still shows the Max. Date of 2015 instead of 2016.

I have tried Max(cast(endtime as DateTime)) but this also gives conversion error How can I resolve this? Thanks in advance.

Ankur Ghelani
  • 659
  • 4
  • 16
Emma
  • 1
  • 1

1 Answers1

0

I can't find an error in your above query, but here's an alternative way how you could write the query:

select top 1 COALESCE(CONVERT(nVARCHAR(50), EndTime, 103), '-') AS EndTime 
from dbo.vwJobHistory
order by endtime desc

Update 1 (clarifying table structure):

It is currently unclear if your datetime data is currently stored inside a nvarchar(50) field or if you want to cast your datetime data as nvarchar(50). Could you run the following query and update your question with the result?

SELECT  
COLUMN_NAME, DATA_TYPE, col.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS col WHERE TABLE_NAME LIKE 'vwJobHistory'

Could you also post a sample row from your table using

SELECT endtime FROM dbo.vwJobHistory

Update 2 (convert string to datetime, apply sort on converted field)

Taken from your comment, it seems your date strings are stored with SQL format 109, so let's try to convert the nvarchar back to datetime, apply sort to it and output the result:

SELECT * FROM (
  SELECT 
    convert(datetime, EndTime, 109) endtimeconverted, 
    * 
  FROM vwJobHistory
) xyz
ORDER BY endtimeconverted DESC
SaschaM78
  • 4,376
  • 4
  • 33
  • 42
  • I returns the same value 12 31 2015 1:02:07:000AM. But it is supposed to return the latest date i.e 01/25/2016 – Emma Jan 25 '16 at 11:38
  • I assume there is something wrong with your data in your vwJobHistory table, i.e. that the data with the 2016 date have not been committed yet. How many rows does your table contain? – SaschaM78 Jan 25 '16 at 11:45
  • The table contains around 1715 rows. – Emma Jan 25 '16 at 12:40
  • @Emma I just updated my answer, could you post the requested data to narrow the problem down? – SaschaM78 Jan 25 '16 at 12:53
  • the datetime data is currently stored in nvarchar. when i query to get the max endtime it gives the latest date of 2015. It is supposed to give the result as today i.e 1/27/2016. – Emma Jan 27 '16 at 03:41
  • My sample row output should show 1 27 2016 1:00:07:000AM – Emma Jan 27 '16 at 03:41
  • @Emma I don't need to know how the data _should_ look like, I need to know how it looks like now :-) – SaschaM78 Jan 27 '16 at 08:39
  • The data looks like 1 27 2016 1:00:07:000AM only now :) – Emma Jan 27 '16 at 08:46
  • select max(endtime) from dbo.vwJobHistory it gives 12 31 2015 1:02:07:000AM but it should give 1 27 2016 1:00:07:000AM(latest date) – Emma Jan 27 '16 at 08:48
  • Just added a new SQL statement to my answer, I hope it'll work as expected. – SaschaM78 Jan 27 '16 at 08:49
  • Conversion failed when converting date and/or time from character string. – Emma Jan 27 '16 at 08:50
  • One general advice: you can also edit your comments instead of cluttering the comments list ;-) Have a look at the following [SQL Convert](https://msdn.microsoft.com/de-de/library/ms187928(v=sql.120).aspx) article, it contains all datetime formats supported for conversion, one should hopefully fit your datetime representation within your `EndTime` field. And if you need to cast from a custom datetime format, have a look [here](http://stackoverflow.com/questions/202243/custom-date-time-formatting-in-sql-server) – SaschaM78 Jan 27 '16 at 08:53