英文:
SQL Query to split date and time from a column
问题
下面是查询从'etime'列中拆分日期和时间的SQL查询：
SELECT TO_DATE(EDATE, 'DD-MM-YY') AS DATE, TO_TIMESTAMP(ETIME, 'DD-MM-YY HH:MI:SS.FF AM') AS TIME
FROM emp;
这个查询将'EDATE'列解释为日期，'ETIME'列解释为带有日期和时间的时间戳。
英文:
what will be the query to split date and time from 'etime' column? table name is 'emp'
| ID | ENAME | EDATE | ETIME |
|---|---|---|---|
| 1 | Kunal | 15-03-23 | |
| 2 | Rohit | 15-03-23 | 31-01-97 9:26:50.124000000 AM |
| 3 | Sakshi | 15-03-23 | 31-01-97 9:26:50.124000000 AM |
ID NUMBER
ENAME VARCHAR2(20)
EDATE DATE
ETIME TIMESTAMP(6)
i tried this query but it didn't work.
SELECT DATE(etime) AS date, TIME(etime) AS time
FROM emp;
答案1
得分: 2
你可以尝试使用以下查询来转换日期时间列：
SELECT CAST(etime AS date)[DATE], CAST(etime AS TIME) [TIME]
如果你想要格式化日期时间，可以使用以下查询：
SELECT FORMAT(CAST(etime AS date),'dd/MM/yyyy')[DATE], FORMAT(CAST(etime AS DATETIME),'hh:mm:ss') [TIME]
英文:
You can cast your Date Time Column once try below query
SELECT CAST(etime AS date)[DATE],CAST(etime AS TIME) [TIME]
If you want to format you format like below query
SELECT FORMAT(CAST(etime AS date),'dd/MM/yyyy')[DATE],FORMAT(CAST(etime AS DATETIME),'hh:mm:ss') [TIME]
答案2
得分: 0
你可以尝试这个示例代码。我不确定你在问题中使用的数据类型Timestamp(6)是否存在。我在ms-learn上的数据类型中找不到它，所以我使用了datetime2数据类型来展示解析：
declare @dateTimeToConvert as datetime2(6) = getdate();
select cast(@dateTimeToConvert as date) myDate, cast(@dateTimeToConvert as time) myTime;
英文:
you can try this sample code. I'm not sure if the datatype Timestamp(6) exists that you use in your question. I can't find it at datatypes at ms-learn so I used the datetime2 datatype to show the parsing:
> declare @dateTimeToConvert as datetime2(6) = getdate();
> select cast(@dateTimeToConvert as date) myDate, cast(@dateTimeToConvert as time) myTime;
答案3
得分: -1
以下是翻译好的部分：
For MS SQL:
SELECT SUBSTRING(CAST(etime AS VARCHAR), 1, 8) AS date,
SUBSTRING(CAST(etime AS VARCHAR), 10, 100) AS time
FROM emp
请注意，代码部分未被翻译。
英文:
For MS SQL:
SELECT SUBSTRING(CAST (etime AS VARCHAR),1,8) AS date,
SUBSTRING(CAST (etime AS VARCHAR),10,100) AS time
FROM emp
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论