Solved

convert date time into string

Posted on 2011-09-09
4
391 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

696 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