1

How do I convert varchar into date in MS sql Server.?

eg - there is a number like this --> 19690409. This is of type varchar. It has to converted into a date format like 1969-04-09(yyyy-mm-dd). After converting how do I subtract this date from 2015-07-01(yyyy-mm-dd).

Please help

Rigerta
  • 3,959
  • 15
  • 26
shailuk
  • 11
  • 5
  • What database are you using? Which one and which version? – Rigerta Jun 19 '17 at 18:40
  • ms SQL SERVER 2008 – shailuk Jun 19 '17 at 18:42
  • Possible duplicate of [Sql Server string to date conversion](https://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion) – Yosef Weiner Jun 19 '17 at 18:46
  • The best answer is that you shouldn't. If you used the correct datatype in the first place you wouldn't have to worry about converting is all over the place. You wouldn't store somebody's salary as varchar would you? I don't know why so many people store dates as character data. It makes no sense. – Sean Lange Jun 19 '17 at 19:37

2 Answers2

2

You could try to use the convert() and the datediff() functions as below:

select convert(date, '19690409') -- the date you need
select datediff(day, convert(date, '19690409'), '2015-07-01') as diff_in_days

The difference between those two dates is in days, but you can chose from many possible options as stated in the documentation.

Rigerta
  • 3,959
  • 15
  • 26
0

For Oracle SQL..

To subtract the date which is varchar2 from another date which is in date format:

Date 1 -> 19690409 which is in varchar2, Date 2 -> 2015-07-01(yyyy-mm-dd) which is in Date format

SELECT to_date(2015-07-01, 'YYYY-MM-DD') - to_date(19690409, 'YYYY-MM-DD') diff_in_days FROM dual;

MahadevPatil_OracleDeveloper
  • 1