You can use the STUFF() method to insert characters into your string to format it in to a value SQL Server will be able to understand:
DECLARE @datestring NVARCHAR(20) = '20120225143620'
-- desired format: '20120225 14:36:20'
SET @datestring = STUFF(STUFF(STUFF(@datestring,13,0,':'),11,0,':'),9,0,' ')
SELECT CONVERT(DATETIME, @datestring) AS FormattedDate
Output:
FormattedDate
=======================
2012-02-25 14:36:20.000
This approach will work if your string is always the same length and format, and it works from the end of the string to the start to produce a value in this format: YYYYMMDD HH:MM:SS
For this, you don't need to separate the date portion in anyway, as SQL Server will be able to understand it as it's formatted.
Related Reading:
STUFF (Transact-SQL)
Answer from Tanner on Stack OverflowThe STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
STUFF ( character_expression , start , length , replaceWith_expression )
You can use the STUFF() method to insert characters into your string to format it in to a value SQL Server will be able to understand:
DECLARE @datestring NVARCHAR(20) = '20120225143620'
-- desired format: '20120225 14:36:20'
SET @datestring = STUFF(STUFF(STUFF(@datestring,13,0,':'),11,0,':'),9,0,' ')
SELECT CONVERT(DATETIME, @datestring) AS FormattedDate
Output:
FormattedDate
=======================
2012-02-25 14:36:20.000
This approach will work if your string is always the same length and format, and it works from the end of the string to the start to produce a value in this format: YYYYMMDD HH:MM:SS
For this, you don't need to separate the date portion in anyway, as SQL Server will be able to understand it as it's formatted.
Related Reading:
STUFF (Transact-SQL)
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
STUFF ( character_expression , start , length , replaceWith_expression )
DECLARE @datestring NVARCHAR(20) = '20220822190000330';
DECLARE @dateint BIGINT = CONVERT(BIGINT,@datestring);
SELECT CONVERT(DATETIME,FORMAT(@dateint, '####-##-## ##:##:##:###')) AS FormatDate;
Output
FormatDate
-----------------------
2022-08-22 19:00:00.330
Videos
Just use a to_date
select to_date(MyDate, 'yyyymmdd')
from mytable
Test with:
select to_date('20170831','yyyymmdd')
from dual
Also, to concatenate in Oracle, use a double pipe ||
select 'Chicken'||'Food'
from dual
If pdate has other characters after yyyymmdd, and yyyymmdd is in the beginning of the whole text, you can just use
SELECT TO_DATE(SUBSTR(pdate,1,8), 'yyyymmdd')
FROM yourtable;
Example
SELECT TO_DATE(SUBSTR('20170831 10:30am',1,8), 'yyyymmdd')
FROM dual;
Otherwise, you can directly use TO_DATE() as suggested by most that replied
Since SQL Server Version 2012 you can use:
CopySELECT format(getdate(),'yyyyMMddHHmmssffff')
This seems to work:
Copydeclare @d datetime
set @d = '2014-04-17 13:55:12'
select replace(convert(varchar(8), @d, 112)+convert(varchar(8), @d, 114), ':','')
Result:
Copy20140417135512
Do some string manipulation to get your string to the format YYYYMMDD HH:mm:SS and you can cast/convert to datetime.
Something like this:
declare @S varchar(50)
set @S = '20120606122012'
select cast(left(@S, 8)+' '+substring(@S, 9, 2)+':'+substring(@S, 11, 2)+':'+substring(@S, 13, 2) as datetime)
You could also add this as a computed column or make it part of a view, so that you don't have to perform the calculation over and over in every query.
As a computed column:
ALTER TABLE dbo.whatever ADD dt
AS CONVERT(DATETIME, STUFF(STUFF(STUFF(col,9,0,' '),12,0,':'),15,0,':'), 120);
Since the calculation is deterministic, it can be persisted and/or indexed.
Using a view:
CREATE VIEW dbo.view_whatever
AS
SELECT /* other columns, */
dt = CONVERT(DATETIME, STUFF(STUFF(STUFF(col,9,0,' '),12,0,':'),15,0,':'))
FROM dbo.whatever;
Then you can simply say:
WHERE dt < DATEADD(MINUTE, -20, CURRENT_TIMESTAMP)
Though I have to agree with the others - you need to fix the model. There is no advantage whatsoever to storing this as a string, except that your users don't get pesky error messages when they enter non-date junk like whosawatsa or even 20120230000000.
ORACLE and SQL Server both are different, I assume its SQL Server. try this
select
convert(datetime,stuff(stuff(stuff('20171116115733', 9, 0, ' '), 12, 0, ':'),
15, 0, ':')) ConvertedDate
there are many possible ways given here
https://rdineshkumar.wordpress.com/tag/how-to-convert-yyyymmddhhmmss-to-datetimedatetime-in-sql-server/
It's my variant from my comment
SELECT CONVERT(datetime,STUFF(STUFF(STUFF(STUFF(STUFF('20171116115733',5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':'),120) -- yyyy-mm-dd hh:mm:ss