Solved

SQL Server 2008 Date

Posted on 2010-11-16
17
618 Views
Last Modified: 2012-05-10
in  SQL server 2008 date values is comming yyyy-dd-mm (eg:2010-11-16). i need to convert it to DD/MM/YY . i need to changed the Date filed Called Orderdate
0
Comment
Question by:lankapala
  • 6
  • 5
  • 2
  • +2
17 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34151027
Is it coming in as a varchar?
convert(char(8), Orderdate, 3)
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34151053
If orderdate is a date field, then it is internally stored without formatting - that is a function of the display.
0
 

Author Comment

by:lankapala
ID: 34151154
Orderdate in my database is datetime, i need to permantly convert to my Orderdate date type DD/MM/YY, if i used
select Select convert(char(8), Orderdate, 3) from Orders; it will show only that time, i need to change Permently.
0
 

Author Comment

by:lankapala
ID: 34151193
is possible to use below code
ALTER TABLE Orders
ALTER COLUMN  OrderDate  convert(char(8), OrderDate, 3)

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34151206
see http:#a34151053
If the field is datetime, there is NO inherent formatting.  It is stored internally as some numeric value, not a string in yyyy-mm-dd nor dd/mm/yy or any text.

You need to specifically format it on display, be it in SSMS or other.
The only other option is to change the field to Char(8) for dd/mm/yy but doesn't sound like what you want.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34151260
If you want to permanently change the column type to char containing dd/mm/yy then

alter table orders add orderdate2 char(8);
update orders set orderdate2 = convert(char(8), orderdate, 3);
alter table orders alter column orderdate char(8);
update orders set orderdate = orderdate2;
alter table orders drop column orderdate2;
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34151597
>>in  SQL server 2008 date values is comming yyyy-dd-mm<<
As cyberkiwi has pointed out that is simply not correct.  You are confusing SQL Server 2008 with SSMS.  SSMS is the application used to view data in MS SQL Server.  The date data type has no interal formatting whatsoever.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34153616
>it will show only that time, i need to change Permently.

can you please explain why you need that?

I presume that a computed column would be the "best" solution, as having a date stored in varchar field with that format you request is highly ... unprofessional, as it WILL result in unefficient queries...
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:lankapala
ID: 34256325
Not working
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34257033
WHAT is not working?
WHAT is the error message?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34257861
lankapala,

>>Not working<<
If you honestly want to get a solution to your question consider providing more feedback.

Supposing your took your car to be repaired and told your mechanic that it was "Not working".  What do you think would be his/her response?
0
 

Author Comment

by:lankapala
ID: 34329027
Appologies for puting not working,
I need to permentaly change date type dd/mm/yyyy ,
below coding is working perfectly,but its not DATE type
update orders set orderdate2 = convert(char(8), orderdate, 3);
alter table orders alter column orderdate char(8);
update orders set orderdate = orderdate2;
alter table orders drop column orderdate2;

I need to change it UK format.

i try using below coding its not working
USE master
GO
ALTER LOGIN Ryan WITH DEFAULT_LANGUAGE = British
GO
SET DATEFORMAT dmy;
EXEC sp_defaultlanguage 'Ryan', 'british'
SELECT * FROM sys.syslanguages


SET DATEFIRST 1

i try below coding
ALTER TABLE Orders
ALTER COLUMN  OrderDate  convert(char(8), OrderDate, 3)

error showing
Incorrect syntax near the keyword 'convert'.


Can any one help me to solve this issue
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34330102
What is the datatype of the column OrderDate?
If it is CHAR containing yyyy-mm-dd, and you want it changed to DATE then

alter table orders add orderdate2 datetime; -- or DATE for sql 2008
update orders set orderdate2 = case when isdate(orderdate) then orderdate end;
alter table orders alter column orderdate datetime;
update orders set orderdate = orderdate2;
alter table orders drop column orderdate2;

If it is ALREADY DATE(TIME), then you're going up a steep hill.  There is no inherent format to DATE fields.
SSMS will ALWAYS show a datetime (unless you convert it to some specific char format) as "yyyy-mm-dd hh:mm:ss.zzz" because you CANNOT set a "display format" against a DATE(TIME) field.
If you want you can add a computed column that will always display dd/mm/yy though.

ALTER TABLE orders ADD OrderDateDisplay convert(varchar,orderdate,3)

Finally, you also have a very confused question
yyyy-dd-mm (eg:2010-11-16)
Is it DD-MM or MM-DD ???

If it is REALLY coming in as varchar yyyy-dd-mm, AND the field orderdate is currently VARCHAR, then add the dateformat before using the first block of code in this comment

set dateformat ydm
-- remember to set it back to dmy after the operation
-- set dateformat dmy
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34330105
I really have to repeat this

There is no inherent format to DATE(TIME) fields.
SSMS will ALWAYS show a datetime (unless you convert it to some specific char format) as "yyyy-mm-dd hh:mm:ss.zzz" because you CANNOT set a "display format" against a DATE(TIME) field.
0
 

Author Comment

by:lankapala
ID: 34452116
ok
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 34709669
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now