February 17, 2006 at 5:58 am
Hi,
If i execute the following:
SELECT TransactionDate FROM Orders
I will get something back like this:
2006-02-10T17:30:00.0000000-00:00
Is there anyway of reformatting that date in the SQL somehow?
I'm using .Net, and exporting the resulting dataset straight to Excel.
And Excel doesnt really like that date format.
Andy
February 17, 2006 at 6:30 am
declare @table table (col1 varchar(50))
insert into @table values ('2006-02-10T17:30:00.0000000-00:00')
select col1 original, cast(substring(replace(col1, 'T', ' '), 1, 19) as datetime) new
from @table
original new
-------------------------------------------------- ------------------------------------------------------
2006-02-10T17:30:00.0000000-00:00 2006-02-10 17:30:00.000
(1 row(s) affected)
February 17, 2006 at 6:48 am
Look up CONVERT (item "CAST and CONVERT") in BOL. With CONVERT, you will receive the output as varchar, and there is a possibility to choose style. There is a nice table of available styles to choose from.
Example:
SELECT
CONVERT(varchar(10), GETDATE(),101),
CONVERT(varchar(10), GETDATE(),102),
CONVERT(varchar(10), GETDATE(),103)
Result:
02/17/2006 2006.02.17 17/02/2006
February 17, 2006 at 7:01 am
Yes!!!
you should use Cast or Convert funtion for this??
February 17, 2006 at 8:05 am
aaha, fantastic. Thankyou
Andy
February 20, 2006 at 4:32 am
You can use Cast or Convert to specify a given date format. If you want to do anything more then Convert gives you, then do it in .Net code. It is far more efficient to do even a single Substring-type function in .Net than in T-SQL.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 20, 2006 at 9:28 am
Small correction - only CONVERT supports display styles, CAST does not.
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply