Last post Sep 14, 2017 01:40 PM by limno
Sep 12, 2017 04:05 PM|johnzee|LINK
I have a column called ProdDate that's has a format of varchar(50) type. Due to limitation of the external datasource where this date comes from I can't change the column format to date. After inserting data When I do a select, the data shows up in column
in a mixed format like this:
3/23/2017 3:19:00 PM
3/24/2017 1:19:31 PM
Sep 10 2017 3:32PM
Sep 11 2017 6:56PM
Is there any way to make it consistent either for all to show as 3/23/2017 3:19:00 PM or Sep 23 2017 3:32PM format? I tried different commands like cast, convert, 101, 103, DATE, datetime but nothing works.
Sep 12, 2017 04:11 PM|PatriceSc|LINK
How does it fail ? I tried :
SET language us_english
SELECT CAST('3/23/2017 3:19:00 PM' AS DATETIME)
SELECT CAST('3/24/2017 1:19:31 PM' AS DATETIME)
SELECT CAST('Sep 10 2017 3:32PM' AS DATETIME)
SELECT CAST('Sep 11 2017 6:56PM' AS DATETIME)
which seems to work.
Edit: a bit unclear. I would do the conversion as soon as possible ie I would convert the external value when reading the external data source and inserting values inside my own database. For now it seems you keep VARCHAR(50) values and convert them only
when your app read those values from your own db ?
Edit 2: ah or if the issue is formatting final values just do that on the ASP.NET side "as usual".
Sep 12, 2017 04:22 PM|johnzee|LINK
You are right, May be I didn't try this one but this is perfect. Thanks
CAST([ProdDate] AS DATETIME)
Sep 14, 2017 01:40 PM|limno|LINK
Use Try_CAST([ProdDate] AS DATETIME) if you can.