SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Converting datetime format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rockstar283
Yak Posting Veteran

88 Posts

Posted - 03/11/2013 :  17:18:55  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
348 Posts

Posted - 03/11/2013 :  19:16:21  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/12/2013 :  00:39:42  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000