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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Converting datetime format

Author  Topic 

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2013-03-11 : 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
Aged Yak Warrior

550 Posts

Posted - 2013-03-11 : 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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-12 : 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/

Go to Top of Page
   

- Advertisement -