Link to home
Start Free TrialLog in
Avatar of JB4375
JB4375Flag for United States of America

asked on

Convert CHAR to DATETIME

I've got a simple select statement that pulls columns from two tables using an Inner Join.

One of the columns is a key that represents the entry date and time we had to set it CHAR(14) because it wouldn't allow a key to be DATETIME.

Having said that, we want that field to be formatted as such from the select statement.

I'm kinda new large SQL queries so I wondered what's the best way to accomplish this? From within the select statement or using some type of function that returns a value?

Thanks in advance,

JB
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

see this link for the different formats:
http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JB4375

ASKER

@Jim

I'm using the following line that fits my formatting:

YYYY MM DD   2001-02-23 04:05:06  convert(varchar, your_data_here ,120)
where LOG_ENTRY_DATETIME is my column.

Whether I use:

Select LOG_ENTRY_DATETIME
Convert (varchar, LOG_ENTRY_DATETIME, 120)

-Or Just -

Convert (varchar, LOG_ENTRY_DATETIME, 120)

 it creates no errors, but doesn't change the format either. I've also tried char with no difference. At this point I'm just working with a test query that is selecting only this column. Any ideas?
Not entirely sure I'm following you.  The below T-SQL returns the YYYYMMDD of date @dt as a varchar.  date column.
Declare @dt datetime ='2001-02-23 04:05:06'
SELECT convert(varchar, @dt, 112)  as the_date

Open in new window

Avatar of JB4375

ASKER

I have a table with thousands of records, and the SELECT statement I'm using returns all of them, but displaying only the columns I'm interested in.

The column named LOG_ENTRY_DATETIME is a CHAR(14) with data that looks like 20010223040506 and I want the return from the query to look like 2001-02-23 04:05:06.

Maybe I'm missing the point, but my thinking is that I need to be calling the column in the convert statement, or assigning the value of each row of the column to a variable.

Also, in case I misstated myself, I don't want to convert this data permanently, but merely display the formatted result.

MB
>The column named LOG_ENTRY_DATETIME is a CHAR(14) with data that looks like 20010223040506 and I want the return from the query to look like 2001-02-23 04:05:06.

Okay, that's different.

afaik there's no single function to pull that off, so you're going to have to manually parse the string into its component parts
Declare @str char(14) = '20010223040506'

SELECT LEFT(@str, 4) + '-' +                        -- Year
   RIGHT('0' + SUBSTRING(@str, 5, 2),2) + '-' +     -- Month
   RIGHT('0' + SUBSTRING(@str, 7, 2),2) + ' ' +     -- Day
   RIGHT('0' + SUBSTRING(@str, 9, 2),2) + ':' +     -- Hour
   RIGHT('0' + SUBSTRING(@str, 11, 2),2) + ':' +    -- Minute
   RIGHT('0' + SUBSTRING(@str, 12, 2),2)            -- Second

Open in new window

Avatar of JB4375

ASKER

OK, I get how this would format a specific string, but now how it's implemented to work with a query returning a large number of results.
Just replace @str with your column name, then add the rest of the SELECT query.

I posted it that way so that you could copy-paste and test it in SSMS to make sure it works.
You can do the manipulation directly in every SELECT, but then you have to re-write or copy the code and it's impossible to change them all easily if you need to change the format.

Instead, I suggest adding a computed column to the table that every query can use:

ALTER TABLE dbo.tablename ADD
    LOG_ENTRY_DATETIME_EDITED AS
    CAST(LEFT(LOG_ENTRY_DATETIME, 4) + '-' +
    SUBSTRING(LOG_ENTRY_DATETIME, 5, 2) + '-' +
    SUBSTRING(LOG_ENTRY_DATETIME, 7, 2) + ' ' +
    SUBSTRING(LOG_ENTRY_DATETIME, 9, 2) + ':' +
    SUBSTRING(LOG_ENTRY_DATETIME,11, 2) + ':' +
    SUBSTRING(LOG_ENTRY_DATETIME,13, 2) AS varchar(14))


Then you can reference that just like any other column:

SELECT
    LOG_ENTRY_DATETIME_EDITED, ...
FROM dbo.tablename
ORDER BY
    LOG_ENTRY_DATETIME_EDITED
Avatar of JB4375

ASKER

Thanks for link and the additional help.

This is what I was able to come up with:

CONVERT(varchar(10), CONVERT(date, substring(LOG_ENTRY_DATETIME,1,8)), 101) + ' '
              + SUBSTRING(LOG_ENTRY_DATETIME,9,2) + ':'
              + SUBSTRING(LOG_ENTRY_DATETIME,11,2) + ':'
              + SUBSTRING(LOG_ENTRY_DATETIME,11,2) as EntryDateTime