Converting datetime format

Question

Post reply

Converting datetime format

rockstar283

SSCrazy

Points: 2704
More actions
March 11, 2013 at 3:18 pm

#272100

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!!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Brian.cs Default port Points: 1407 More actions · Answer 1

This may come in handy.

http://www.sql-server-helper.com/tips/date-formats.aspx

I think you want this:

SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]

Brian.cs Default port Points: 1407 More actions · Answer 2

Or in other words, converting it to a DATETIME rather than a VARCHAR(10) is undermining what you're trying to do.

rockstar283 SSCrazy Points: 2704 More actions · Answer 3

Thank you for the reply..but you are converting the date type to varchar..I want to keep it datetime

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

The internal datetime format is irrelevant to the display format. Both those datetime columns will have the same internal format regardless of which format you use to insert the date data. Take a look at the

SET DATEFORMAT commands to alter the display format to be used to convert the date.

SET DATEFORMAT mdy

with a select like:

select top 10 cast(mydatecolumn as varchar(20)) from sometable

will display the dates like:

Nov 19 2011 12:00AM

The options on the convert() will also allow for different display formats for dates.

And then again, I might be wrong ...
David Webb

rockstar283 SSCrazy Points: 2704 More actions · Answer 5

rockstar283

SSCrazy

Points: 2704

March 11, 2013 at 3:38 pm

#1595751

sorry but not getting..

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

The datetime datatype holds date data in an internal format that doesn't look like a date. I'd don't remember the exact format, but for purposes of illustration, pretend that the actual value is the number of milliseconds since 1900-01-01. No matter what date format you use in an insert statement, the server will convert that to the number of milliseconds since 1900-01-01 and store that number in the datetime column. When you select it, the server will decide (based on settings like DATEFORMAT and any convert options, what you will see as a result of the select. Taking a datetime column, reformatting it, and re-storing it does absolutely nothing.

And then again, I might be wrong ...
David Webb

Brian.cs Default port Points: 1407 More actions · Answer 7

Here's a sample code you can run which will show you what we're talking about. Notice you're also using only the date portion of the datetime, so there may be additional data there you're missing. But bottom line, you can convert what the date output format is however you want using CONVERT, but that's not at all how the database actually stores it, which this code should help demonstrate.

declare @dates table (i int, sampledate datetime);

insert into @dates (i, sampledate) values

(1, GETDATE()),

(2, '3/11/2013'),

(3, '2013-03-11'),

(4, CONVERT(DATETIME,GETDATE(),110)),

(5, CONVERT(VARCHAR(10),GETDATE(),110));

select * from @dates;

--Notice how the formatting didn't matter

rockstar283 SSCrazy Points: 2704 More actions · Answer 8

so I think the bottom line is..if I am using datetime data type, to show the values in mm-dd-yyyy I have to convert it into varchar..right?

Brian.cs Default port Points: 1407 More actions · Answer 9

If you want to display the result that way in the SQL output, then yes.

rockstar283 SSCrazy Points: 2704 More actions · Answer 10

Thank you all for the replies..this has helped a lot 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

A little late to the party but just wanted to share my 2¢. You should leave the formatting to the front end. Have your queries return a datetime datatype and let your report or webpage or whatever handle the formatting.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/