Finding Date & time from a string

Question

Finding Date & time from a string

RadMan-494104

SSC Rookie

Points: 48
More actions
April 20, 2008 at 4:05 am

#75182

Hi all
I would be grateful if someone please help me to find date and time from a given string .
the string value is as below
'20081011192921703'
many Thanks
Rad

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Just checking... you mean you have no record layout information for this? We can guess that the first 8 digits are in the yyyymmdd format, and we can guess that the other information might in the format of hhmissmmm, but we wouldn't know for sure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RadMan-494104 SSC Rookie Points: 48 More actions · Answer 2

Hi Jeff

Thanks for the reply..

Yes you are right in your analysis that first 8 digit are are date and the rest is the time. I want to extract this information in a proper date & time format.

Rad

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Here are a couple of ways...

declare @x varchar(30)

set @x = '20081011192921703'

select cast(substring(@x, 1, 8) + ' ' + substring(@x, 9, 2) + ':' + substring(@x, 11, 2)

+ ':' + substring(@x, 13, 2) + '.' + substring(@x, 15, 3) as datetime) as dtm

select cast(stuff(stuff(stuff(stuff(@x, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'), 18, 0, '.') as datetime) as dtm

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

RadMan-494104 SSC Rookie Points: 48 More actions · Answer 4

RadMan-494104

SSC Rookie

Points: 48

April 21, 2008 at 3:55 am

#804819

Brilliant mate...

thanks a ton...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

This uses explicit conversion, rather than implicit:

DECLARE @CharDate CHAR(23)

SET @CharDate = '20081011192921703'

SET @CharDate = STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(@CharDate, 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':'), 17, 0, ':'), 20, 0, ':')

SELECT @CharDate

SELECT CONVERT(DATETIME, @CharDate, 121)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Chris used the method I would have. Ryan's is also a viable solution.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RyanRandall SSChampion Points: 13623 More actions · Answer 7

This uses explicit conversion, rather than implicit[/code]

Chris

I don't see any implicit conversion. Are you suggesting that what I posted is implicit?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

RyanRandall (4/21/2008)
This uses explicit conversion, rather than implicit[/code]
Chris
I don't see any implicit conversion. Are you suggesting that what I posted is implicit?

Hi Ryan

Not at all - CAST() is explicit conversion. Apologies for the confusion.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

RyanRandall SSChampion Points: 13623 More actions · Answer 9

Chris Morris (4/21/2008)
RyanRandall (4/21/2008)
This uses explicit conversion, rather than implicit[/code]
Chris
I don't see any implicit conversion. Are you suggesting that what I posted is implicit?
Hi Ryan
Not at all - CAST() is explicit conversion. Apologies for the confusion.
Cheers
ChrisM

Thanks for clarifying 🙂

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.