CODEWITHSUNDEEP
sqlsql-server-2014
Alec.
Alec.

Reputation: 5525

Convert to british date format issue

I am trying to convert a date passed to a stored procedure through a parameter.

I am unable to get this date in to British format, here's what I've tried and the results:

select 
        N'2015-01-17 11:49:54.253',
        left(N'2015-01-17 11:49:54.253',10),
        cast(left(N'2015-01-17 11:49:54.253',10) as datetime),
        convert(datetime,convert(datetime, cast(left(N'2015-01-17 11:49:54.253',10) as datetime)),103),
        convert(datetime,convert(datetime, cast(left(N'2015-01-17 11:49:54.253',10) as datetime)),113),

RESULTS:

2015-01-17          11:49:54.253             2015-01-17         2015-01-17 00:00:00.000 2015-01-17 00:00:00.000               2015-01-17 00:00:00.000

Upvotes: 2

Views: 5494

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44326

I don't see a british format with datetime, so guessing you want this format:

dd/mm/yyyy hh:mi:ss:mmm

Convert syntax (can be used on sqlserver 2012+):

SELECT FORMAT(cast('2015-01-17 11:49:54.253' as datetime),'dd/MM/yyyy hh:mm:ss.fff')

Result:

17/01/2015 11:49:54.253

Upvotes: 2

Christian Barron
Christian Barron

Reputation: 2755

You need to convert it into a varchar as datetime will always be stored / displayed in format 'yyyy-mm-dd hh:mi:ss.mmm'

So use:

Select Convert(varchar, Cast('2015-01-17 11:49:54.253' as datetime), 103) as GBDate

You can check the MS website for further help with this too: https://msdn.microsoft.com/en-us/library/ms187928.aspx

Upvotes: 1

asontu
asontu

Reputation: 4659

You're converting to a datetime, you wanna be converting to a varchar (or char etc.) to do any formatting:

select convert(varchar(255), cast(left(N'2015-01-17 11:49:54.253',10) as datetime), 103)

Upvotes: 1

Related Questions