November 20, 2012 at 11:38 am
I need to output datetime data in this format: 11/19/2012 8:54:59 AM, so mm/dd/yyyy (h)h:mm:ss AM/PM
I didn't think it would be so hard...
Is there a simpler way than
DECLARE @dDate DATETIME = GETDATE()
SELECT CONVERT(VARCHAR, @dDate, 103) + ' ' + LTRIM(STUFF(SUBSTRING(CONVERT(VARCHAR, @dDate, 109), 13, 14), 9, 4, ' '))
Thanks!
P
November 20, 2012 at 1:18 pm
How about SELECT CONVERT(VARCHAR(25), GETDATE(), 22)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 21, 2012 at 12:55 am
MyDoggieJessie (11/20/2012)
How aboutSELECT CONVERT(VARCHAR(25), GETDATE(), 22)
That actually formats the date as mm/dd/yy (h)h:mm:ss AM(PM), so you end up with a shortend year, which when it comes to converting back you have a bit of a headache in deciding if 10 is 1810, 1910, or 2010, or even 2110, as it depends on what the date is.
however using this you would get mm/dd/yyyy (h)h:mm:ss AM(PM)
SELECT CONVERT(VARCHAR(25), GETDATE(), 101)+' '+Ltrim(Right(CONVERT(VARCHAR(25), GETDATE(), 22),11))
Except for a BCP export into a file I dont know why you would want to format the date in SQL, I personally leave it to the frontend to do it as they generally have significantly better functions for doing this than SQL (SQL 2012 excepted with the format function now embeded).
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 21, 2012 at 3:34 am
...
Is there a simpler way than
DECLARE @dDate DATETIME = GETDATE()
SELECT CONVERT(VARCHAR, @dDate, 103) + ' ' + LTRIM(STUFF(SUBSTRING(CONVERT(VARCHAR, @dDate, 109), 13, 14), 9, 4, ' '))
Thanks!
P
There is in SQL2012...
Before that, it will still be variation of what you do anyway. One of such variation is here:
DECLARE @dDate DATETIME = GETDATE()
SELECT CONVERT(VARCHAR, @dDate, 103) + ' ' +
STUFF(CONVERT(VARCHAR,CAST(@dDate AS TIME(0)),109),9,0,' ')
Is any particular reason for not formatting datetime in a "client" layer?
November 21, 2012 at 3:40 am
...
...CONVERT(VARCHAR(25), GETDATE(), 22)
...
...
Looks like MS forgot to document this style :w00t:...
November 21, 2012 at 5:11 am
Wow, SELECT CONVERT(VARCHAR(25), GETDATE(), 22) is soooo close. And undocumented.
Reason for this is, we export the data to an Excel spreadsheet, which is converted by our customer to another version of Excel, before being imported into Oracle. Their import puked when we upgraded to Excel 2010. Our customer is NOT tech savvy, and things -- like date formats -- can *never* change. I know.:rolleyes:
Thanks all.
November 21, 2012 at 7:01 am
If it's imported to Excel why not leave the format alone as you pull it out of SQL Server and have a macro in Excel to do all the formatting you need? :w00t:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 21, 2012 at 7:10 am
We did not foresee this problem arising before we went to prod.
We should have tested it with the customer -- except they have neither the means nor the expertise to test stuff.
The Excel files are auto-generated. And I don't know what happens when they are down-converted to an earlier version (would the formatting be lost?).
I suppose what you propose is doable, I will look into it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply