SQL - Convert to date

Question

I have a numeric column in SQL which I need to convert to a date. The field is currently coming into the database as: "20181226.00000".

I only need to the characters before the " . ". So i did a SUBSTRING - CHARINDEX looking for anything before the " . " . I then did a cast as NVARCHAR.

Now I'm getting 20181226 but I want this date field to populate like: "MM/DD/YYYY" .

My current SQL is:

CONVERT(VARCHAR(10),SUBSTRING(CAST('MYFIELD' AS NVARCHAR(50)) ,0, CHARINDEX('.', 'MYFIELD' , 0)),101)

Just change 101 to 103 CONVERT(VARCHAR(10),SUBSTRING(CAST('MYFIELD' AS NVARCHAR(50)) ,0, CHARINDEX('.', 'MYFIELD' , 0)),103) — sandeep rawat, Dec 27 '18 at 01:25
Why would you let a date value come into your database like that in the first place? Translate at the time it's inserted. — Joel Coehoorn, Dec 27 '18 at 02:02

score 0 · Answer 1 · edited Dec 27 '18 at 01:29

0

Just change 101 to 103

CONVERT(VARCHAR(10),SUBSTRING(CAST('MYFIELD' AS NVARCHAR(50)) ,0, CHARINDEX('.', 'MYFIELD' , 0)),101)

edited Dec 27 '18 at 01:29

Kamil Gosciminski

16,547
8
49
72

answered Dec 27 '18 at 01:26

sandeep rawat

4,797
1
18
36

score 0 · Answer 2 · answered Dec 27 '18 at 01:34

The easiest way is to convert it to what it actually is, a date, and then back to a varchar using the right style.

SELECT convert(varchar(max), convert(date, substring(convert(varchar(max), nmuloc), 1, charindex('.', convert(varchar(max), nmuloc)) - 1)), 101)
       FROM elbat;

I wasn't sure if it's a number or a string. If it's a string you don't need the convert(varchar(max), nmuloc)s.

However a side note: You should not store dates as numbers or strings. Use an appropriate data type like date.

score 0 · Answer 3 · answered Dec 27 '18 at 07:39

Or you could achieve it like this.

declare @v_value numeric(18,5)=20181226.00000

SELECT SUBSTRING(CAST(CAST(@v_value AS INT) AS VARCHAR),5,2)+'/'+SUBSTRING(CAST(CAST(@v_value AS INT) AS VARCHAR),7,2)+'/'+SUBSTRING(CAST(CAST(@v_value AS INT) AS VARCHAR),1,4) as V_OUTPUT

--Output
/*
V_OUTPUT
------------------
12/26/2018
*/

Best Regards,

Will

score 0 · Answer 4 · answered Dec 27 '18 at 09:10

0

First we have convert to varchar and then Date format

SELECT  CONVERT(varchar, CAST(CAST('20181206.00000' as VARCHAR(8)) AS DATE), 103); dd/mm/yyyy

SELECT  CONVERT(varchar, CAST(CAST('20181206.00000' as VARCHAR(8)) AS DATE), 101); mm/dd/yyyy

answered Dec 27 '18 at 09:10

Jasmy j.s

1
2

SQL - Convert to date

4 Answers4