如何选择字符串作为日期时间 [英] How to select string as datetime

问题描述

大家好，

我试图在表格上进行SQL查询，时间戳存储为字符串。



I想要将该字符串转换为日期时间格式，以便我可以根据时间和年份进行查询。我怎么做？



columen CREATED有这样的时间戳：

2014102116



我正在使用SQL Server 2014



我尝试了什么：



没什么 - 我根本就不知道如何转换它

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

推荐答案

简单：更改你的数据库。

Don不要将时间戳存储为字符串：将它们存储为DATETIME值 - 其他任何东西只会给你带来问题，浪费空间。



虽然可以转换时间戳，它需要很多枯燥的代码来执行它：

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:

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.

，转换（DATE，LEFT（CREATED，8））AS CREATIONDATE

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

这篇关于如何选择字符串作为日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！