0

I have been provided with a file whereby the dates are in the aforementioned format.

I have never seen this format before so am I going to have to separate the data out and convert each section or is this actually a known format?

Cheers

Lynchie
  • 1,077
  • 2
  • 20
  • 36

3 Answers3

2

Most databases have some sort of to_date() or parse_date() functionality . . . except SQL Server.

If you are using SQL Server, then this should work:

select cast(stuff('10JAN2000:00:00:00', 10, 1, ' ') as datetime)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hi Gordon, that works great - could you please elaborate on how this works - I'm not a man who likes to just cut and paste the solution, I'd like to understand how its the solution if you could please. Many Thanks. – Lynchie Jul 24 '18 at 08:12
  • @Lynchie . . . It simply replaces the first `:` with a space so SQL Server can convert the value. – Gordon Linoff Jul 24 '18 at 11:59
0

as suggested by others, knowing which database changes the answer. SQL Server isn't keen on supporting date parse function unlike oracle, please read through below threads, might help with better understanding-

Sql Server string to date conversion

to_date in SQL Server 2005

In oracle of course, we can do something like this-

select to_date('10JAN2000:00:00:00', 'ddmonyyyy:hh24:mi:ss') from dual;

or

select to_timestamp('10JAN2000:00:00:00', 'ddmonyyyy:hh24:mi:ss') from dual;

BlackCurrant
  • 126
  • 5
0
select to_char(to_date('10JAN2000:00:00:00', 'ddmonyyyy:hh24:mi:ss'),'yyyy-mm-dd : hh:mm:ss') from dual;
Aritra Bhattacharya
  • 720
  • 1
  • 7
  • 18