lankapala
asked on
SQL Server 2008 Date
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
If orderdate is a date field, then it is internally stored without formatting - that is a function of the display.
ASKER
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.
select Select convert(char(8), Orderdate, 3) from Orders; it will show only that time, i need to change Permently.
ASKER
is possible to use below code
ALTER TABLE Orders
ALTER COLUMN OrderDate convert(char(8), OrderDate, 3)
ALTER TABLE Orders
ALTER COLUMN OrderDate convert(char(8), OrderDate, 3)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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;
>>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.
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.
>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...
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...
ASKER
Not working
WHAT is not working?
WHAT is the error message?
WHAT is the error message?
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?
>>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?
ASKER
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
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
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
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,
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
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.
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.
ASKER
ok
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.
convert(char(8), Orderdate, 3)