-2

From DB2, we are getting one column value "2018-01-12-13.28.37.111972" like this format("YYYY-MM-DD-HH.MM.SS.MI"). I want to convert this one to "YYYY-MM-DD" in sql server. I used Cast(fieldname as date) is not working.

Subramanian
  • 29
  • 2
  • 10

4 Answers4

2

If you are interested in both the date and time, Larnu's answer is the way to go.
If it's only the date you care about, you can simply use left to get only the date part of the string:

SELECT CAST(LEFT('2018-01-12-13.28.37.111972', 10) As Date)

BTW, datetime isn't stored with a display format.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
0

I can't speak for DB2, but I suspect you're it's passing the value as a varchar, and not a datetime, and hence the problem. If DB2 has to pass the values as a varchar, you have use STUFF to fix the values:

SELECT DB2.DateString,
       CONVERT(datetime2(6),STUFF(STUFF(STUFF(DB2.DateString,11,1,'T'),14,1,':'),17,1,':'))
FROM (VALUES('2018-01-12-13.28.37.111972'))DB2(DateString);
Thom A
  • 88,727
  • 11
  • 45
  • 75
0

With a few string functions you can transform the timestamp text to a format that can be casted to a DATE or a DATETIME

For example this snippet:

select db2col, 
try_cast(left(db2col, 10)+' '+replace(substring(db2col,12,8),'.',':')+substring(db2col,20,4) as datetime) as col_as_datetime,
cast(left(db2col, 10) as date) as col_as_date
from (values 
 ('2018-01-12-13.28.37.111972'),
 ('2018-02-15-14.29.54.123456')
) as vals(db2col)

Returns: 

db2col                      col_as_datetime         col_as_date
--------------------------  ----------------------- ----------
2018-01-12-13.28.37.111972  2018-01-12 13:28:37.110 2018-01-12
2018-02-15-14.29.54.123456  2018-02-15 14:29:54.123 2018-02-15
LukStorms
  • 28,916
  • 5
  • 31
  • 45
0

select convert(varchar(10), '2018-01-12-13.28.37.111972', 5)

will produce: 2018-01-12

kams
  • 128
  • 11
  • That's basically the same as select `left('2018-01-12-13.28.37.111972', 10)`. try converting this string to `datetime` and you'll get Conversion failed error. – Zohar Peled Jul 17 '19 at 11:37