CODEWITHSUNDEEP
sqlsql-serversql-server-2005
DenaliHardtail
DenaliHardtail

Reputation: 28306

How do I create a datetime from a custom format string?

I have datetime values stored in a field as strings. They are stored as strings because that's how they come across the wire and the raw values are used in other places.

For reporting, I want to convert the custom format string (yyyymmddhhmm) to a datetime field in a view. My reports will use the view and work with real datetime values. This will make queries involving date ranges much easier.

How do I perform this conversion? I created the view but can't find a way to convert the string to a datetime.

Thanks!

Update 1 -

Here's the SQL I have so far. When I try to execute, I get a conversion error "Conversion failed when converting datetime from character string."

How do I handle nulls and datetime strings that are missing the time portion (just yyyymmdd)?

SELECT  
        dbo.PV1_B.PV1_F44_C1 AS ArrivalDT,

cast(substring(dbo.PV1_B.PV1_F44_C1, 1, 8)+' '+substring(dbo.PV1_B.PV1_F44_C1, 9, 2)+':'+substring(dbo.PV1_B.PV1_F44_C1, 11, 2) as datetime) AS ArrDT,

        dbo.MSH_A.MSH_F9_C2 AS MessageType,
        dbo.PID_A.PID_F3_C1 AS PRC,
        dbo.PID_A.PID_F5_C1 AS LastName, 
        dbo.PID_A.PID_F5_C2 AS FirstName,
        dbo.PID_A.PID_F5_C3 AS MiddleInitial,
        dbo.PV1_A.PV1_F2_C1 AS Score, 
        dbo.MSH_A.MessageID AS MessageId

FROM    dbo.MSH_A
        INNER JOIN dbo.PID_A ON dbo.MSH_A.MessageID = dbo.PID_A.MessageID
        INNER JOIN dbo.PV1_A ON dbo.MSH_A.MessageID = dbo.PV1_A.MessageID
        INNER JOIN dbo.PV1_B ON dbo.MSH_A.MessageID = dbo.PV1_B.MessageID

Upvotes: 0

Views: 2363

Answers (7)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

DECLARE @d VARCHAR(12);

SET @d = '201101011235';

SELECT CONVERT(SMALLDATETIME, STUFF(STUFF(@d,9,0,' '),12,0,':'));

Note that by storing date/time data using an inappropriate data type, you cannot prevent bad data from ending up in here. So it might be safer to do this:

WITH x(d) AS
(
    SELECT d = '201101011235'
        UNION SELECT '201101011267' -- not valid
        UNION SELECT NULL -- NULL
        UNION SELECT '20110101' -- yyyymmdd only
),
y(d, dt) AS 
(
    SELECT d,
        dt = STUFF(STUFF(LEFT(d+'000000',12),9,0,' '),12,0,':')
    FROM x
)
SELECT CONVERT(SMALLDATETIME, dt), ''
    FROM y
    WHERE ISDATE(dt) = 1 OR d IS NULL
UNION
SELECT NULL, d
    FROM y
    WHERE ISDATE(dt) = 0 AND d IS NOT NULL;

Upvotes: 1

Nicholas Carey
Nicholas Carey

Reputation: 74277

A one liner:

declare @datestring varchar(255)
set @datestring = '201102281723'

select convert(datetime, stuff(stuff(@datestring,9,0,' '),12,0,':') , 112 )

Result:

2011-02-28 17:23:00.000

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

declare @S varchar(12)
set @S = '201107062114'

select cast(substring(@S, 1, 8)+' '+substring(@S, 9, 2)+':'+substring(@S, 11, 2) as datetime)

Result:

2011-07-06 21:14:00.000'

This first changes your date string to 20110706 21:14. Date format yyyymmdd as a string is safe to convert to datetime in SQL Server regardless of SET DATEFORMAT setting.

Edit:

declare @T table(S varchar(12))

insert into @T values('201107062114')
insert into @T values('20110706')
insert into @T values(null)

select 
  case len(S)
    when 12 then cast(substring(S, 1, 8)+' '+substring(S, 9, 2)+':'+substring(S, 11, 2) as datetime)
    when 8 then cast(S as datetime)
  end   
from @T

Result:

2011-07-06 21:14:00.000
2011-07-06 00:00:00.000
NULL

Upvotes: 2

platon
platon

Reputation: 5340

Generally, you can use this code:

SELECT convert(datetime,'20110706',112)

If you need to force SQL Server to use a custom format string, use the following code:

SET DATEFORMAT ymd

SELECT convert(datetime,'20110706')

Upvotes: 1

JNK
JNK

Reputation: 65157

DECLARE @test varchar(100) = '201104050800'
DECLARE @dt smalldatetime

SELECT @dt = SUBSTRING(@test, 5, 2) 
             + '/' + SUBSTRING(@test, 7, 2) + '/' 
             + SUBSTRING(@test, 1, 4) + ' ' + SUBSTRING(@test, 9, 2) 
             + ':' + SUBSTRING(@test, 11, 2)

SELECT @dt

Output:

2011-04-05 08:00:00

Upvotes: -3

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

According to here, there's no out-of-the-box CONVERT to get from your yyyymmddhhmm format to datetime.

Your strategy will be parsing the string to one of the formats provided on the documentation, then convert it.

Upvotes: 3

Neil Knight
Neil Knight

Reputation: 48547

You can use CAST or CONVERT.

Example from the site:

G. Using CAST and CONVERT with datetime data

The following example displays the current date and time, uses CAST to change the current date and time to a character data type, and then uses CONVERT display the date and time in the ISO 8901 format.

SELECT 
   GETDATE() AS UnconvertedDateTime,
   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
   CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
GO

Here is the result set.

UnconvertedDateTime     UsingCast                      UsingConvertTo_ISO8601

----------------------- ------------------------------ ------------------------------

2006-04-18 09:58:04.570 Apr 18 2006 9:58AM             2006-04-18T09:58:04.570



(1 row(s) affected)

Upvotes: 1

Related Questions