Update date format in SQL from 1980-12-17 00:00:00.000 to 17-Dec-80 in SQL

Question

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'

@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. — a_horse_with_no_name, 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://docs.microsoft.com/de-de/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15) — a_horse_with_no_name, 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) — a_horse_with_no_name, 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
@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

Buchiman · Answer 1 · 2019-12-19T09:27:26.287

0

You can try something like this:

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

For more info visit this LINK

edited Dec 19 '19 at 09:27

answered Dec 19 '19 at 09:20

Buchiman

310
5
18

Why bother converting a string to `varchar`? this makes no sense. – Zohar Peled Dec 19 '19 at 09:24
@DiwakarYadav that happens because your data type is datetime so it adds time in the date. – Buchiman Dec 19 '19 at 09:29
@Buchiman so what should be data type so that i will get date as 17-Dec-80 – Diwakar Yadav Dec 19 '19 at 12:58

score 0 · Answer 2 · answered Dec 19 '19 at 14:45

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.

score 0 · Answer 3 · answered Dec 19 '19 at 15:17

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

answered Dec 19 '19 at 15:17

wnutt

409
3
4

Update date format in SQL from 1980-12-17 00:00:00.000 to 17-Dec-80 in SQL

3 Answers3