How to select string as datetime

Question

0.00/5 (No votes)

See more:

Hi everyone,
Im trying to do a SQL query on a tabel where the timestamp is stored as a string.

I want to convert that string to a datetime format so I can do query based on time and year. How do I do that?

The columen CREATED is having a timestamp like this:
2014102116

Im using SQL server 2014

What I have tried:

Nothing - i simply don't know how to convert it

Posted 18-Mar-17 3:38am

Kristian_dk

Updated 19-Mar-17 23:51pm

Add a Solution

Comments

[no name] 18-Mar-17 9:44am

2014102116
yyyymmdd??

Please be more precise

Kristian_dk 18-Mar-17 11:33am

I believe that it is yyyymmddhh
I hope that is better.

[no name] 18-Mar-17 11:40am

It is very bad that you only belive it. It would be much more better you know about (instead of belive) the data you Need to process.

Ok let us assume the part
20141021
YYYYMMDD
is the part you are sure it is right.

Now use Google how to get the answer ;) Meanwhile I also Google on this, but don't Count on me :)

2 solutions

Solution 1

Simple: change your DB.
Don't store timestamps as strings: store them as DATETIME values - anything else just gives you problems down the line, and wastes space.

While it's possible to convert the timestamp, it needs a lot of dull code to do it:

SQL

SELECT CAST(SUBSTRING(TimeStampColumn, 1, 4) + '-' + SUBSTRING(TimeStampColumn, 5, 2) + '-' + SUBSTRING(TimeStampColumn, 7, 2) + ' ' + SUBSTRING(TimeStampColumn, 9, 2) + ':00' AS DATETIME)

And you have to do that every time you want to use it!

Change the DB: always store values in the most appropriate datatype. It saves you a lot of time and effort later.

Posted 18-Mar-17 5:10am

OriginalGriff

Comments

Kristian_dk 18-Mar-17 11:37am

I do unfortunatly not have access to change anything on the database since it is an old application i have to do some reporting on. But I will clearly try to get the storing of dates changed to datetime for the future.

Kristian_dk 18-Mar-17 11:43am

I cant really get your code to work. CREATED is my timestamp with format yyyymmddhh.
SELECT CAST(SUBSTRING(CREATED, 1, 4) + '-' + SUBSTRING(CREATED, 5, 2) + '-' + SUBSTRING(CREATED, 7, 2) + ' ' + SUBSTRING(CREATED, 9, 2) + ':00' AS DATETIME)
,[USERNAME]
,[USERTYPE]
,[CREATED]
,[USED]
,[USERNAME2]
,[EMAIL]
FROM [EDW].[User]

Kristian_dk 18-Mar-17 11:43am

This code throws a:
Msg 8116, Level 16, State 1, Line 2
Argument data type int is invalid for argument 1 of substring function.

OriginalGriff 18-Mar-17 11:51am

Check your DB design: I'm not sure that the timestamp is a string at all. The error message implies it's an INT - in which case the code gets even more boring! :laugh:

Kristian_dk 18-Mar-17 18:00pm

Of yes it is true. Just checked the columne. Its a Interger.

OriginalGriff 19-Mar-17 4:48am

So double check exactly what the content is: integers could be "ticks since ... " or the interpretation you already have. I'd start by looking at the source code for the legacy app that manipulates it and make absolutely sure I understood what it does with it, rather than guessing.

Kristian_dk 20-Mar-17 5:50am

Found the solution finally:
,Convert(DATE, LEFT(CREATED, 8)) AS CREATIONDATE
That did the trick..

Thanks for helping out.. I have never thought about the Int vs. String confusion..

OriginalGriff 20-Mar-17 6:04am

You're welcome!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kristian_dk · Accepted Answer · 2017-03-19T23:51:00

Solution 2

,Convert(DATE, LEFT(CREATED, 8)) AS CREATIONDATE

Posted 19-Mar-17 23:51pm

Kristian_dk