1

Have researched a lot, both on this site, and others, but still don't have a valid solution. I have a column of varchar datatype, it contains DateTime data. I need to store only the Date portion in a Date type column. Tried different ways of Cast, Convert, and other functions, but still haven't been able to make it work.

Basically I want to convert this 

Tue Apr 26 2016 13:54:53 GMT+0200 (CEST)

to 

04/26/2016
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ahpitre
  • 153
  • 1
  • 11
  • 2
    Possible duplicate of [sql server convert date to string MM/DD/YYYY](http://stackoverflow.com/questions/11854775/sql-server-convert-date-to-string-mm-dd-yyyy) – Fredrik Lundvall Nov 17 '16 at 20:32

3 Answers3

2

Assuming your Day and Month part is always 3 characters long this can be done simply as this:

DECLARE @d VARCHAR(100)='Tue Apr 26 2016 13:54:53 GMT+0200 (CEST)';
SELECT CONVERT(DATE,SUBSTRING(@d,4,12),109);

If this was to easy one would have to find the blank(s) with CHARINDEX, but I don't think so...

The format code 109 means mon dd yyyy hh:mi:ss:mmmAM (or PM) Details here.

And be aware that formats containing language depending parts are directly sent by the devil to create never ending pain... This will not work on a server with a different language setting!

Shnugo
  • 66,100
  • 9
  • 53
  • 114
1
Declare @String varchar(max) = 'Tue Apr 26 2016 13:54:53 GMT+0200 (CEST)'
Select cast(Substring(@String,12,4)+Substring(@String,4,7) as date)

Returns

2016-04-26
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • along with this to get the exact output `convert(varchar(10),cast(Substring(@String,12,4)+Substring(@String,4,7) as date),101)` – Searching Nov 17 '16 at 20:54
  • @Searching You are 100% correct in pointing this out. I just wasn't sure if OP wanted a date or string. – John Cappelletti Nov 17 '16 at 21:00
  • I agree with you. Just not in the question + I'm not sure if that format is even possible to be stored in sqlserver as `date` type anyways. (the converted part). Correct me if I'm wrong..Ta. – Searching Nov 17 '16 at 21:14
  • @Searching You can Insert/Update a date field in mm/dd/yyyy format, but it is still stored in the ISO format. create table #temp (date date); Insert Into #temp values ('04/26/2016') ;Select * from #temp – John Cappelletti Nov 17 '16 at 21:23
  • Yup that was my understanding.. It would just go to standard format. – Searching Nov 17 '16 at 21:29
  • @JohnCappelletti You last comment is not correct. Try your example with `SET LANGUAGE GERMAN;` and it will break. It is also not correct, that it will be stored `in the ISO format`. That you **see** the ISO format in your `SELECT` is just the string representation of the actual value and might depend on the system's settings too... – Shnugo Nov 17 '16 at 21:38
  • @Shnugo It was a leap of faith. The date was Tue Apr 26 not Di Apr 26. For the record your convert(...,109) is indeed smarter... In the tool-belt now. – John Cappelletti Nov 17 '16 at 21:54
1

Okay a few things here you have a field that looks to be psuedo ISO 8601 but is not the standard. The first question will be: "Where does this come from?" Typically you don't have the 'Tue' or 'GMT' or '(CEST)' in a standard and the offset from Greenwich Meantime is in the format (+/-)##:## NOT (+/-)####. SQL and many other formats can easily accept a standardized string in the ISO 8601 format. Good brief here: https://www.w3.org/TR/NOTE-datetime

That being said you can easily get what you want with a little know how:

DECLARE 
    @S VARCHAR(128) = 'Tue Apr 26 2016 13:54:53 GMT+0200 (CEST)'
,   @Valid VARCHAR(128)

--Legitimate ISO 8601 string:
SELECT @Valid = RTRIM(LTRIM(REPLACE(STUFF(STUFF(@S, 1, 4, ''), LEN(@S)-12, 12, ':00'), 'GMT', '')))
SELECT @Valid

--Legitimate DateTimeOffset
SELECT CAST(@Valid AS DATETIMEOFFSET)

--Now that I have a legimiate DateTimeOffset I can downconvert easily
SELECT CAST(CAST(@Valid AS DATETIMEOFFSET) AS DATE)

--AND... Now that I have a legimate Date I can format it many different ways
SELECT CONVERT(VARCHAR, CAST(CAST(@Valid AS DATETIMEOFFSET) AS DATE), 101)

The real thing to realize here is there is magical conversion of DateTime using the convert function. But you may be wondering 'what if I want it to look different?'. Try this page:

http://www.sql-server-helper.com/tips/date-formats.aspx

I would be leery though of just finding the placement of were things appear to be coming from a string even though I can parse your example. If you are getting things not following a standard you should know why. The main reason being you may be able to get this to work for a specific instance but not be able to repeat this pattern over and over.

djangojazz
  • 14,131
  • 10
  • 56
  • 94