[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Convert CHAR to DATETIME

Posted on 2014-02-13
10
Medium Priority
?
326 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
[X]
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
10 Comments
 
LVL 41

Expert Comment

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

Accepted Solution

by:
Jim Horn earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 66

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 66

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 66

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 70

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

650 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