Solved

Convert CHAR to DATETIME

Posted on 2014-02-13
10
320 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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:ScottPletcher
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

896 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now