-1

Want to update from data type of Date which is now 1980-12-17 00:00:00.000 to 17-Dec-80 in Microsoft SQL Server management studio

and I m trying to do update command 

UPDATE emp SET HIREDATE = 17-Dec-80 WHERE EMPNO= 7369;

But I'm getting error.

Msg 207, Level 16, State 1, Line 1 Invalid column name 'Dec'

Diwakar Yadav
  • 5
  • 1
  • 10
  • Please add error that you get. – Tek Nath Acharya Dec 19 '19 at 09:07
  • @TekNath Msg 207, Level 16, State 1, Line 1 Invalid column name 'Dec'. – Diwakar Yadav Dec 19 '19 at 09:08
  • The problem is you are trying to change the data type date to a string format. – Buchiman Dec 19 '19 at 09:11
  • `DATE` columns don't have "a format" so you can't change the way the value is saved. Just apply the formatting you want when you _display_ (or retrieve) the values. –  Dec 19 '19 at 09:13
  • @a_horse_with_no_name how to apply formatting? you means to say insert new sql ? – Diwakar Yadav Dec 19 '19 at 09:16
  • This link will help you convert date [LINK](https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/) – Buchiman Dec 19 '19 at 09:17
  • In older SQL Server versions use `convert()` newer ones have a [`format()` function](https://learn.microsoft.com/de-de/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15) –  Dec 19 '19 at 09:18
  • Does this answer your question? [How to format date on MS SQL Server 2008](https://stackoverflow.com/questions/38394002/how-to-format-date-on-ms-sql-server-2008) –  Dec 19 '19 at 09:19
  • [*DateTime* does not have a display format](https://stackoverflow.com/a/30033028/3094533). Only string representations of datetime values have a display format. – Zohar Peled Dec 19 '19 at 09:26
  • 17 minus DEC minus 80? And you have no DEC column. – jarlh Dec 19 '19 at 09:37
  • the column name is HIREDATE and not DEC – Diwakar Yadav Dec 19 '19 at 09:42
  • @a_horse_with_no_name [Format is nice and all, but...](https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but) – Zohar Peled Dec 19 '19 at 09:49
  • @ZoharPeled This is what i want .....may this link will help me...thanks you – Diwakar Yadav Dec 19 '19 at 09:54

3 Answers3

0

You can try something like this:

UPDATE emp
SET HIREDATE = '17-Dec-80'
WHERE EMPNO= 7369;

For more info visit this LINK

Buchiman
  • 320
  • 5
  • 18
0

As a_horse_with_no_name says in the comments, a DATE column doesn't have a format, what you're seeing when you SELECT it is a default human readable format of the date. If you want to see something different you specify your required format.

SELECT FORMAT(HIREDATE, 'dd-MMM-yy')
  FROM emp

Should do the trick.

OTTA
  • 1,071
  • 7
  • 8
0

To set the date use:

UPDATE emp SET HIREDATE = '17-Dec-80' WHERE EMPNO= 7369;

To retrieve it in the "dd-MMM-yy" format:

select 
   format(HIREDATE, 'dd-MMM-yy') as HIREDATE  
from emp 
WHERE 
   EMPNO= 7369
wnutt
  • 519
  • 3
  • 5