Solved

convert date time into string

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
A short film showing how OnPage and Connectwise integration works.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

911 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

24 Experts available now in Live!

Get 1:1 Help Now