November 15, 2011 at 9:54 am
OK, please no laughing first off. I have a datetime field displaying as:
2011-01-12 00:00:00.000
YYYY-DD-MM (err, and the time bits)
What I'd like to convert this too is:
YYYY-MM-DD (and the same timey bits).
A loud "hurrah" for anyone who can help me solve this. Attempts to date, even when converting to a CHAR string first, are miserably failing.
Regards, Greg
November 15, 2011 at 9:59 am
If it's a datetime column, won't this work?
CONVERT(datetime,MyCol,120)
John
November 15, 2011 at 10:29 am
Nope, it has no effect. It's definately a datetime field though.
I've also tried preconverting it a char string, and then reconverting back a styled datetime. Still no effect.
Greg
November 15, 2011 at 11:03 am
So the date is stored as an offset, not as '2011-11-12'.
Is the date stored as Nov 12 or Dec 11? That's what matters. The display is separate, but if you receive Nov 12, and are trying to convert that to Dec 11, you would need to actually change the value.
DECLARE @d DATETIME, @newd DATETIME, @C VARCHAR(12)
SELECT @d = 'Nov 12, 2011'
SELECT @d
, CONVERT( DATETIME, @d, 120)
SELECT @C = CONVERT( VARCHAR(4), YEAR(@d)) + '/' + CONVERT( VARCHAR(2), day( @d)) + '/' + CONVERT( VARCHAR(2), MONTH(@d))
SELECT @newd = CONVERT( DATETIME, @C, 120)
SELECT @newd
November 15, 2011 at 12:17 pm
I'm wondering how you are seeing a datetime field returned as YYYY-DD-MM. But let's just say that's true and you are asking how to show it to the user as YYYY-MM-DD, with the time still intact. You'd need to do something like Steve suggested and rebuild the string yourself.
DECLARE @d DATETIME, @newd DATETIME, @C VARCHAR(20)
SELECT @d = '2011-01-12 13:45:16'
SELECT @C = CONVERT( VARCHAR(4), YEAR(@d)) + '/' + CONVERT( VARCHAR(2), DAY( @d)) + '/' + CONVERT( VARCHAR(2), MONTH(@d)) + ' ' + CONVERT(varchar(2), DATEPART(hh, @d)) + ':' + CONVERT(varchar(2),DATEPART(mi, @d)) + ':' + CONVERT(varchar(2),DATEPART(ss, @d))
SELECT @newd = CONVERT( DATETIME, @C)
SELECT @newd
Here is a neat site that gives a table of T-SQL that yields different results. Oh, it would be great to have a format function built in to SQL Server like String.Format.
Cliff
November 15, 2011 at 12:28 pm
OK fellows thanks - I'll go for the string rebuild.
November 15, 2011 at 12:58 pm
Oh, it would be great to have a format function built in to SQL Server like String.Format.
I have to disagree with that sentiment. Display formatting should be left to the front end. SQL is not a presentation tool. That is exactly why there are slick things like String.Format in .NET, that is where presentation belongs.
_______________________________________________________________
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/
November 15, 2011 at 1:12 pm
You're right. The presentation layer is the place for formatting. But why even have the convert(varchar, date, style) as it if formatting? Legacy perhaps?
November 15, 2011 at 1:15 pm
Could be. There are certainly a number of other things in sql server to avoid unless it is absolutely necessary (cursors, sql variant, xml, etc). As with all things sql server, "It depends". 😛 There are always reasons that pop up where you need these things.
_______________________________________________________________
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/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply