How to use Convert function in SQL Server?

Question

Sl,No  Name      BirthDay
1      Jojin1    2013-05-12 00:00:00.000
2      jojin2    2012-06-12 00:00:00.000
3      jojin3    2015-04-12 00:00:00.000

I have table called Datefunction. In that Birthday in above display format but I need DD/MM/YYYY. I try to convert below Query but Updating successful message, but check with Table not happening changes

UPDATE DateFunction
SET BirthDay = CONVERT(VARCHAR(15),BirthDay,103)

SQL Server stores dates in an internal manner that doesn't *have* a format. And this is a good thing. Do formatting in your presentation layer, not the database. — Damien_The_Unbeliever, May 20 '13 at 08:51

score 1 · Accepted Answer · answered May 20 '13 at 09:35

1

Firstly, I completely agree with Diamond GreezeR and Damein.

But having said that i know OP can't implement those things right now.

What you can do?

ALTER Table temp
ALTER column Datecolumn varchar(15)

update temp
set Datecolumn=CONVERT(VARCHAR(15),getdate(),103)

answered May 20 '13 at 09:35

Prahalad Gaggar

11,389
16
53
71

IF have one column called date, in that value are 1999...but I need only 99,(Last two digit) how to convert that / – vcbgvhgf May 20 '13 at 10:25
**`SELECT RIGHT(convert(varchar(date), 2))`** – Prahalad Gaggar May 20 '13 at 10:29
Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'Date'. – vcbgvhgf May 20 '13 at 10:40
select RIGHT(Convert(varchar(Date),2)) – vcbgvhgf May 20 '13 at 10:43
**`select RIGHT(Convert(varchar,Date),2)`**. Now try. If you want we can have Discussion. – Prahalad Gaggar May 20 '13 at 10:45
Date Column 12-12-2013 , I need 12-12-13 how we do that – vcbgvhgf May 20 '13 at 11:00
**`SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '-') AS [MMDDYY]`** – Prahalad Gaggar May 20 '13 at 11:01
**`ALTER TABLE temp ALTER COLUMN datecolumn varchar(100) ; update temp set datecolumn= Convert(varchar(8),CONVERT(date,datecolumn,1),5) from temp`**. This will work. Please be exact to what you need at the start. **`:(`** – Prahalad Gaggar May 20 '13 at 11:15
not getting ? I created Column.. Update that Column with Date values using Replace but same value getting New column also – vcbgvhgf May 20 '13 at 12:06
@Jojin please repeat this two Query from Start **`ALTER TABLE temp ALTER COLUMN datecolumn varchar(100) ; update temp set datecolumn= Convert(varchar(8),CONVERT(date,datecolumn,1),5) from temp`**. They are seperated with **`;`** – Prahalad Gaggar May 20 '13 at 12:09
Msg 241, Level 16, State 1, Line 3 Conversion failed when converting date and/or time from character string. – vcbgvhgf May 20 '13 at 12:17
@Jojin You need to start freshly. You can do the changes after updating – Prahalad Gaggar May 20 '13 at 12:20

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

You cannot store the dates in that format, you can only display them.

SELECT CONVERT(varchar(10, BirthDay, 103)
FROM [Datefunction]

Other options (from my comments below):

You could create a text field and store the result of the CONVERT function. See Luv's answer: https://stackoverflow.com/a/16646539/909882
Another possibility is to use the DATEFORMAT option in SQL Server. However, I think this only sets the format for the session; it does not change it permanently.
Also, you could could use SET LANGUAGE to choose the date format that SQL Server expects in queries. See stackoverflow.com/questions/1187127/… as an example

edited May 23 '17 at 11:43

Community

1
1

answered May 20 '13 at 09:20

Ɖiamond ǤeezeƦ

3,223
3
28
40

Ok This We can view the things. I need to update the table with Convert function, that I am asking ? – vcbgvhgf May 20 '13 at 09:31
You could create a text field and store the result of the `CONVERT` function. – Ɖiamond ǤeezeƦ May 20 '13 at 09:33
Another possibility is to use the `DATEFORMAT` option in SQL Server See MSDN: http://msdn.microsoft.com/en-us/library/aa259188(v=sql.80).aspx. However, I think this only sets the format for the session; it does not change it permanently. – Ɖiamond ǤeezeƦ May 20 '13 at 09:38
And my final suggestion is that you could could use SET LANGUAGE to choose the date format that SQL Server expects in queries. See http://stackoverflow.com/questions/1187127/sql-server-datetime-issues-american-vs-british as an example – Ɖiamond ǤeezeƦ May 20 '13 at 09:40
First We need to add one Column alter table Datefunction add NewDate varchar (30) After that below Query will update update DateFunction SET NewDate = CONVERT(char(10),BirthDay, 105) – vcbgvhgf May 20 '13 at 09:46
That's what [Luv](http://stackoverflow.com/a/16646539/909882) answered below and I suggested above. – Ɖiamond ǤeezeƦ May 20 '13 at 09:53

How to use Convert function in SQL Server?

2 Answers2

Linked