0

I have this kind of date in my sql table : 1/24/2018 10:34:23 PM

And I want to extract the date with this format : YYYYMMDD that is to say I would like to have this 20180124

How could I do this ?

I tried this :

try_parse([date] as date using 'en-US')

but the format is wrong ...

I precise the name of my column is date

Mad Dog Tannen
  • 7,129
  • 5
  • 31
  • 55
Paul
  • 43
  • 3
  • 2
    Column data type? – jarlh Jan 24 '19 at 14:21
  • I tried this `update '1/24/2018 10:34:23 PM' set format('1/24/2018 10:34:23 PM','yyyyMMdd')` but it does not work ... – Paul Jan 24 '19 at 14:28
  • Assuming that the type of your date column is an actual date type, why not let your consuming application handle the formatting? – Zack Jan 24 '19 at 16:58

3 Answers3

2

use the below to format dates in the YYYYMMDD format;

    SELECT
    CONVERT(VARCHAR(8), [DateField],112) AS DateField
ChrisCarroll
  • 473
  • 2
  • 8
2

Added as answer as requested...

Dates don't have formats in SQL Server, how you display them is the format. Strings have formats.

You can use FORMAT or CONVERT depending on your version. 

convert(varchar(8),yourColumn,112) 
format(yourCOlumn,'yyyyMMdd')

DEMO

declare @table table (yourColumn datetime)
insert into @table
values
(getdate())

select
    yourColumn
    ,format(yourColumn,'yyyyMMdd')
    ,convert(varchar(8),yourColumn,112)
from @table

If your column is a string, then you need to convert that to a date first or just parse it a different way.

select 
    convert(varchar(8),cast('1/24/2018 10:34:23 PM' as datetime),112)
   ,format(cast('1/24/2018 10:34:23 PM' as datetime),'yyyyMMdd')
S3S
  • 24,809
  • 5
  • 26
  • 45
0

I usually refer to a question already answered on Stack Overflow in the below link. How to convert DateTime to VarChar

The answer by Colin really works for me and a source of reference eveytime I have confusions around datetimes and formats in SQL Server.

Kashyap MNVL
  • 603
  • 4
  • 13