Solved

convert date time into string

Posted on 2011-09-09
4
349 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
  • 2
  • 2
4 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thnx
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now