Sql Modify Date Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sql Modify Date Help

Author

Topic

BrendanM1900
Starting Member

8 Posts

Posted - 2010-11-29 : 12:58:59

Ok i have a lot of records in my database with a feild called DateOfBirth, the date is in the default format and what i wanting is to update all the date's to DDMMMYYYY

I have tried a few querys but i got nowhere.

Anyhelp will be good thanks.

B.Mullan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-29 : 13:06:39

What data type is DateOfBirth? If it's datetime, then you can not change the way it gets stored. You can only change how you display the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

BrendanM1900
Starting Member

8 Posts

Posted - 2010-11-30 : 12:58:44

Its a varchar.

B.Mullan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-30 : 13:02:17

Then you can use CONVERT function with the appropriate style. Please see CONVERT in BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

BrendanM1900
Starting Member

8 Posts

Posted - 2010-11-30 : 13:07:21

Sorry im new to SQL Server and dont know much about it, what is BOL?

B.Mullan

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-11-30 : 13:39:35

BOL = Book Online (The Help facility that comes with Sql Management Studio)

=======================================
No matter what side of the argument you are on, you always find people on your side that you wish were on the other. -Jascha Heifetz, violinist (1901-1987)

BrendanM1900
Starting Member

8 Posts

Posted - 2010-11-30 : 16:24:24

Ok i have had a look and tried the following query

SELECT convert(DateOfBirth,DD/MM/YYYY);

Butr no luck, i had a programmer code my website for me and he could not do this, if someone could show me the correct query i would realy apprciate it.

B.Mullan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-30 : 16:33:00

It doesn't appear that you checked the syntax in BOL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

BrendanM1900
Starting Member

8 Posts

Posted - 2010-12-01 : 00:20:06

I dont know where to start i am looking for a quick fix, so can someone show me the correct query?

B.Mullan

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-12-01 : 00:38:11

UPDATE <TABLENALE> SET <COLUMNNAME>=CONVERT(VARCHAR,GETDATE(),103).
This should work.if iam not wrong

BrendanM1900
Starting Member

8 Posts

Posted - 2010-12-01 : 04:44:44

Do i need to change the 103? or is that part of what i need

B.Mullan

BrendanM1900
Starting Member

8 Posts

Posted - 2010-12-01 : 15:13:39

this only changes the feild to todays date, not the ddmmyyyy format.

B.Mullan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-01 : 16:54:28

He is just showing you an example of how to use it. You need to switch out GETDATE() for your column. His solution will include the slashes, so you'll need to remove those via REPLACE function.

And here is the documentation, please read it: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

BrendanM1900
Starting Member

8 Posts

Posted - 2010-12-01 : 22:02:51

So this will be the corect query?

UPDATE tablename SET DateOfBirth=CONVERT(VARCHAR,DateOfBirth,103).

B.Mullan

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-12-02 : 00:48:42

yes...

Kristen
Test

22859 Posts

Posted - 2010-12-02 : 03:10:22

No!

CONVERT(varchar(10), DateOfBirth, 103)

will give you mm/dd/yyyy - as Tara said. What the OP asked for was ddMMMyyyy

Kristen
Test

22859 Posts

Posted - 2010-12-02 : 03:15:41

Do a test first using SELECT before you do an update

SELECT TOP 10 DateOfBirth AS OriginalData, CONVERT(varchar(10), DateOfBirth, 103) AS NewData
FROM YourTable
WHERE DateOfBirth IS NOT NULL

Beware:

This will fail if DateOfBirth does not contain a valid date

DateOfBirth date formatting will be "guessed" by SQL according to the server setting, the Language your connection logon is using, and the country, and whether you have any preferences set up (in SQL, not on your PC) for the date.

I recommend you NEVER store dates in a VARCHAR datatype column. Use the DATETIME datatype instead which will stored dates in a special way, it will only store validate dates, and you can format the date however you like - either by using the CONVERT function, or in your application (which is much preferred)

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-12-02 : 07:09:34

But krsiten 103 format will give ddmmyyyy format.right

quote:
Originally posted by Kristen

No!

CONVERT(varchar(10), DateOfBirth, 103)

will give you mm/dd/yyyy - as Tara said. What the OP asked for was ddMMMyyyy

Kristen
Test

22859 Posts

Posted - 2010-12-02 : 08:48:31

"But krsiten 103 format will give ddmmyyyy format.right"

No, it doesn't. It gives dd/mm/yyyy as I already explained.

And even if it did give ddmmyyyy that is not what the O/P asked for - he wants ddMMMyyyy as I explained earlier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources