-1

I want to transfer one Date Column with format '/' to '-', for example, one date data like '10/19/2018 2:49:47 PM', but I want the format '2018-10-19 14:49:47'. I have tried below actions, like using convert, but it seems does not work.

SELECT [LMDM_Data] ,convert(varchar(100),LMDM_Data,23) ,CAST(LMDM_Data as varchar(10)) FROM Table

Below is the result:

LMDM_Data   (No column name)    (No column name)
10/19/2018 2:49:47 PM   10/19/2018 2:49:47 PM   10/19/2018

To verify whether this column is Date, I use isDate() function to check, and they all return 1.

SELECT [LMDM_Data] ,ISDATE(LMDM_Data), convert(varchar(100),LMDM_Data,120) ,CAST(LMDM_Data as varchar(10)) FROM [ESS_NJ_LMDM_PDM].[dbo].[maintanence_workload]

Below is the result:

LMDM_Data   (No column name)    (No column name)    (No column name)
10/19/2018 2:49:47 PM   1   10/19/2018 2:49:47 PM   10/19/2018
Eric Gong
  • 107
  • 1
  • 1
  • 10
  • 3
    [Date has no display format.](https://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) Only string representations of date has display format. – Zohar Peled Jun 26 '19 at 07:15
  • And As GSerg wrote - if you are storing dates as string, stop doing that. Read Aaron Bertrand's [Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) for details. – Zohar Peled Jun 26 '19 at 07:18
  • `isDate()` returns 1 if the string it gets can be converted to date / datetime, or 0 otherwise. What's the actual data type of the column? – Zohar Peled Jun 26 '19 at 07:20
  • @ZoharPeled Bingo, it is varchar type and it can be transferred to Date. – Eric Gong Jun 26 '19 at 07:26
  • 1
    Then you really need to fix your data type @Zoeric and then have your presentation layer worry about the format. `10/19/2018 2:49:47 PM` is not "after" `06/26/2019 8:30:32 AM` nor is "before" `10/19/2018 9:21:01 AM`; but it is according to your data. – Thom A Jun 26 '19 at 07:31
  • @ZoharPeled Yes, I have changed this column type from Varchar to Datetime and it can work normally. – Eric Gong Jun 26 '19 at 08:06
  • Closed as duplicate, then. – Zohar Peled Jun 26 '19 at 08:12

1 Answers1

0

Use the style 120 (yyyy-mm-dd hh:mi:ss(24h))

CONVERT(VARCHAR,LMDM_Data,120)

Remember this will only covert to VARCHAR inside SELECT query and not actual data in table.

See CAST and CONVERT (Transact-SQL) for more format.

UPDATE:

In case the datatype of the field LMDM_Data is not DateTime, you will require to convert it to DateTime before changing the format like this:

CONVERT(VARCHAR,CAST(LMDM_Data AS DATETIME),120)

But as everyone suggested above, storing date in VARCHAR datatype is not a good idea at all. You should fix the datatype rather than converting to datetime.

Himanshu
  • 31,810
  • 31
  • 111
  • 133