How to convert Date to nvarchar

Question

have a search screen and I want to show results by searching specific data.My Filter is "show all date after '7/5/05' date".

As this is a JSF form, so 7/5/05 gets stored as Date.And again this is getting converted to Long as '1120501800000'.

Now the query becomes like this

SELECT * FROM   ABC  WHERE  due_date BETWEEN '1120501800000' AND '1952498291808'  ORDER  BY trstart_date DESC 

But the problem is that due_date column is of "nvarchar(50)" , and values are stored as "7/5/05".

Can someone please tell me how can I convert "1120501800000" to "7/5/05".Even I dont know what is fastTime as shown in image?

Thanks in advance.

I am using Jsf,Spring,IBatis,sql server

Answer 1

Those numbers seem to represent unix timestamps, with millisecond precision.

SELECT DATEADD( SECOND
              , CONVERT(BIGINT, '1120501800000') / 1000
              , '1970/01/01')

This converts to 2005/07/04 (yyyy/mm/dd), at 18:30.

In your example, you get 2005/07/05, which may be due to a UTC timezone offset in your local time. Let's say you're 6 hours ahead; that translates to:

SELECT DATEADD( HOUR
              , 6
              , DATEADD( SECOND
                       , CONVERT(BIGINT, '1120501800000') / 1000
                       , '1970/01/01')
              )

This converts to 2005/07/05, as you would expect.

To get the mm/dd/yy formatting you pretend:

SELECT CONVERT(VARCHAR(2), DATEPART(MONTH, date)) + '/'
     + CONVERT(VARCHAR(2), DATEPART(DAY, date))   + '/'
     + RIGHT(CONVERT(VARCHAR(4), DATEPART(YEAR, date)), 2)
  FROM ( SELECT DATEADD(HOUR, 6, DATEADD(SECOND, CONVERT(BIGINT, '1120501800000') / 1000, '1970/01/01'))
       ) AS T (date)