get date from varchar string

Question

Post reply

get date from varchar string

Rock

Old Hand

Points: 367
More actions
April 15, 2020 at 8:10 am

#3743040
I have a varchar string format like below. I need to get only date from it in the format of YYYY/MM/DD.
Wed, 15 Apr 2020 02:00:00 -0400
Tue, 14 Apr 2020 23:00:01 +0000
I used cast > It throws the below error:
CAST(abovecolumnvalue as datetime)
Conversion failed when converting date and/or time from character string.
- This topic was modified 4 years ago by Rock.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Have you tried stripping the day name from the beginning of the string?

John

Rock Old Hand Points: 367 More actions · Answer 2

Rock

Old Hand

Points: 367

April 15, 2020 at 9:00 am

#3743046

I didn't do that. How will I do that?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Use STUFF to replace the first five characters with a blank, or LEN and RIGHT to return all but the first five characters. Have a go and post back with what you've tried if you're struggling.

John

Rock Old Hand Points: 367 More actions · Answer 4

14 Apr 2020 23:00:01 +0000

15 Apr 2020 03:58:49 -0400

15 Apr 2020 02:00:00 -0400

15 Apr 2020 03:00:04 -0400

14 Apr 2020 12:00:00 -0400

I used the stuff to replace the first 4 char in the column.. However I'm unable to print only date field from this.

DECLARE @thedatetimeoffset datetimeoffset;

SET @thedatetimeoffset = 'Wed, 15 Apr 2020 02:00:00 -0400';

SET @thedatetimeoffset = Stuff('Wed, 15 Apr 2020 02:00:00 -0400', 1, 5, '')

PRINT @thedatetimeoffset

SELECT

@thedatetimeoffset AS 'datetimeoffset',

CAST(@thedatetimeoffset AS datetime)

Conversion failed when converting date and/or time from character string.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

The datetimeoffset data type isn't as flexible in the range of string formats it supports as datetime is. Please see the documentation. I think you'll need to remove the offset from the end of the string and add it back on once you're converted to datetime. This is an object lesson in why it's important to store dates as dates and not as strings. That's not a criticism of you, since you may not have any control over the design of your system. It's also an opportunity for you to learn a bit about string manipulation and date formats!

John

Rock Old Hand Points: 367 More actions · Answer 6

DECLARE @Texxt Varchar(50);

SET @Texxt = 'Wed, 15 Apr 2020 02:00:00 -0400';

SELECT @Texxt as original,

SUBSTRING(STUFF(@Texxt,1,5,''),1,DATALENGTH(STUFF(@Texxt,1,5,''))-5) convertedone

Done!!!

jonathan.crawford SSCertifiable Points: 6610 More actions · Answer 7

I think you might still want to consider the timezone in those. If your date needs to all be converted to the same timezone, then the date it occurred on can change. for example, the first row is +0000, but the rest are -0400, and they are all within four hours of the start of the day. One way or the other those dates are not the same calendar day.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses