Solved

Convert CHAR to DATETIME

Posted on 2014-02-13
10
319 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 39

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

708 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

15 Experts available now in Live!

Get 1:1 Help Now