CODEWITHSUNDEEP
sql-serverexcel
r.baton
r.baton

Reputation: 65

SQL Server to convert date as text into date format that is recognised in Excel

I've got data in SQL Server that is text formatted as yyyy-mm-dd I've converted this into UK date format as follows

convert (varchar (10), main.Tbl_ServiceOrder.SOCreatedOn, 103) as SOCreated

I'm then importing the data into Excel via a SQL Server table, but although the date appears correctly (dd/mm/yyyy), Excel still recognises it as text.

I can convert it in Excel via DateValue but as the dataset is large I'm trying to get as much done in SQL.

Upvotes: 1

Views: 4173

Answers (2)

Mike Myers
Mike Myers

Reputation: 39

I was just looking for a solution to get a date format 23 ( which is YYYY-MM-DD) to come into Excel as a date type field. It would come in as a text value of "2020-01-01" for example even though the cell format was date (MM/DD/YY). I have no choice but to run this macro to convert it to a date.

Cells.Replace What:="-", Replacement:="-", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Or if yours is coming in with backslash:

Cells.Replace What:="/", Replacement:="/", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

It wasn't a problem for me because I was already using a macro to run the query for the data in the first place.

Upvotes: 0

Vicky_Burnwal
Vicky_Burnwal

Reputation: 981

I had the same issue in past. For me, casting date column to smalldatetime worked.

CAST(convert (varchar (10), main.Tbl_ServiceOrder.SOCreatedOn, 103) AS smalldatetime)

Try it may work for you as well.

Upvotes: 1

Related Questions