Solved

convert date time into string

Posted on 2011-09-09
4
388 Views
Last Modified: 2012-05-12
Hi,
i have a table that has a date stamp column like this:
2011-09-04 00:59:31.000
2011-09-04 00:59:31.000
2011-09-04 00:59:31.000
2011-09-04 01:01:22.000
2011-09-04 01:01:22.000
2011-09-04 01:01:22.000
2011-09-04 01:01:22.000
2011-09-04 01:01:22.000

the date time goes like from 12: AM to 23:29 so all what i am trying to do is to create a query that splitts the time and the date into 2 seperate columns. But in the time code i only need to use the first 2 digits only.  For instance, if it is 1:00 pm then i want it to look like this 13 if it is 6:00 pm then i want it to look like this 18 and so forth.  so basically to convert into milatary time but also keep it only the first 2 digits.  thanks for your help.
0
Comment
Question by:karinos57
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36514088
SELECT DateStamp, DATEADD(day, DATEDIFF(day, '2000-01-01', DateStamp), '2000-01-01') AS DateOnly,
    DATEPART(hour, DateStamp) AS TheHour
FROM SomeTable
0
 

Author Comment

by:karinos57
ID: 36519652
thanks Patrick,
is there a way to get rid of the Zero's in the new colomn after the Date?  I would like to show nothing after the Dates.  Here is what i have now after i run the query:


TimeStamp                                            DateOnly                        TheHour
2011-09-04  03:56:22.000           2011-09-04 00:00:00.000      3
2011-09-04 03:56:22.000           2011-09-04 00:00:00.000      3
2011-09-04 03:56:22.000       2011-09-04 00:00:00.000      3
2011-09-04 03:56:22.000           2011-09-04 00:00:00.000      3
2011-09-04 03:56:22.000           2011-09-04 00:00:00.000      3
2011-09-04 03:59:32.000           2011-09-04 00:00:00.000      3

In the DateOnly columns, i have Zero's and would like to get rid of them.  Any help would be appreciated.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36519794
A datetime data always includes both the date and the time.

In any version of SQL Server, you could use:

SELECT DateStamp, CONVERT(varchar, DATEADD(day, DATEDIFF(day, '2000-01-01', DateStamp), '2000-01-01'), 102) AS DateOnly,
    DATEPART(hour, DateStamp) AS TheHour
FROM SomeTable 

Open in new window


Of course, that returns a string and not a date.

In SQL Server 2008 you can try:

SELECT DateStamp, CONVERT(date, DATEADD(day, DATEDIFF(day, '2000-01-01', DateStamp), '2000-01-01')) AS DateOnly,
    DATEPART(hour, DateStamp) AS TheHour
FROM SomeTable 

Open in new window


I do not have SQL 2008 to test that; the date data type is new to that version.
0
 

Author Closing Comment

by:karinos57
ID: 36519855
thnx
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
debug user defined function in visual studio 2015 2 50
T-SQL: problem comparing datetime 4 80
2008 to 2016 jump (SQL Server) 1 31
SQL query 45 40
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

734 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