| Author |
 Topic  |
|
|
rockstar283
Yak Posting Veteran
73 Posts |
 Posted - 03/11/2013 : 17:18:55
|
I have a following table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl1](
[Old_Date] [datetime] NULL,
[New_Date] [datetime] NULL
) ON [PRIMARY]
INSERT INTO TBL1(Old_Date)values('2012-12-31')
INSERT INTO TBL1(Old_Date)values('2013-01-01')
INSERT INTO TBL1(Old_Date)values('2013-01-02')
INSERT INTO TBL1(Old_Date)values('2013-01-03')
with Old_Date getting the values from SSIS package with the date format 'YYYY-MM-DD'
Using T-SQL, I am trying to convert the Old_dATE into New_date column with the format 'MM-DD-YYYY', but for some reason it is not working out..
I tried following statements:
SELECT OLD_DATE,CONVERT(DATETIME,OLD_DATE,110) AS NEW_DATE FROM TBL1
But In sql server, I am seeing the same YYYY-MM-DD format in new_date instead of MM-DD-YYYY
Can anyone help me out here..
Thank you!! |
|
|
ScottPletcher
Yak Posting Veteran
USA
79 Posts |
Posted - 03/11/2013 : 19:16:21
|
Dates/datetimes are stored internally entirely as numbers.
What are you seeing is the default display format, which is determined by the local install settings.
You can use CONVERT to get different date formats displayed.
For example:
CONVERT(varchar(10), old_date, 105)
should give you "dd-mm-yyyy" as output. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 03/12/2013 : 00:39:42
|
you should try to do this in front end application if at all possible. Changing the datatype of dates just for formatting in SQL end is kind of overkill. It will also make any further date manipulations like sorting using these values difficult
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Converting datetime format
