I have a varchar field that contains date values like:
02042015
How do I convert it to DATE format that should return the value something like:
2 Apr 2015
Asked
Active
Viewed 220 times
-4
Zohar Peled
- 79,642
- 10
- 69
- 121
user6279935
- 15
- 4
-
What have you tried? The first thing you need to do is confirm that every record in your database follows this format. – Nick.Mc May 02 '16 at 07:18
-
@Nick.McDermaid Yes all the records in the database do contain the same format... – user6279935 May 02 '16 at 07:20
-
[Date have no display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) If you need a display format, you want to convert a string to another string. – Zohar Peled May 02 '16 at 07:21
-
@Nick.McDermaid Actuallt I haven't tried anything... I am very new to database just starting off learning DB these days.... Trying out the script mentioned in the below answer by Yousuf – user6279935 May 02 '16 at 07:21
-
What version of sql server are you working with? – Zohar Peled May 02 '16 at 07:24
-
SQL Server 2008 R2 – user6279935 May 02 '16 at 07:27
3 Answers
2
You could use CONVERT with 106 format in following:
declare @date varchar(60) = '02042015'
select convert(varchar(20),cast(right(@date,4) + substring(@date,3,2) + left(@date,2) as datetime), 106)
OUTPUT
02 Apr 2015
Stanislovas Kalašnikovas
- 9,203
- 4
- 32
- 59
0
I thought there would be some conversion format so you could put:
select(convert(datetime,'010109',<some magic number>))
and get the result, but I can't seem to find one that gives the right date
You can try this as well:
declare @dt varchar(6)
select @dt = '010109'
select convert(datetime,RIGHT(@dt,2) + SUBSTRING(@dt,3,2) + LEFT(@dt,2))
Yousuf Sultan
- 3,055
- 8
- 35
- 63
0
Try this
DECLARE @V_DATE VARCHAR(20) = '02042015'
SELECT CONVERT(varchar(11),
CONVERT(datetime,RIGHT(@V_DATE,4)+LEFT(@V_DATE,2)+SUBSTRING(@V_DATE,3,2))
,106)
Abdul Rasheed
- 6,486
- 4
- 32
- 48