Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Convert CHAR to DATETIME

Posted on 2014-02-13
10
322 Views
Last Modified: 2014-02-14
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
0
Comment
Question by:JB4375
10 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39856319
see this link for the different formats:
http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39856334
SQL expert PortletPaul wrote an excellent article on SQL Server Date Styles using Convert, that demonstrates around 50 or so ways to convert dates for display.

Just find the format you like, then copy what's in the Syntax column, paste it in your SELECT query, and modify the column names to meet your needs.
0
 
LVL 1

Author Comment

by:JB4375
ID: 39856541
@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?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 39856633
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

0
 
LVL 1

Author Comment

by:JB4375
ID: 39856727
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
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39856740
>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

0
 
LVL 1

Author Comment

by:JB4375
ID: 39856798
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.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39856815
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.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39857204
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
0
 
LVL 1

Author Closing Comment

by:JB4375
ID: 39859349
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
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question