convert date time into string

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.
karinos57Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
Patrick MatthewsCommented:
SELECT DateStamp, DATEADD(day, DATEDIFF(day, '2000-01-01', DateStamp), '2000-01-01') AS DateOnly,
    DATEPART(hour, DateStamp) AS TheHour
FROM SomeTable
0
 
karinos57Author Commented:
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
 
karinos57Author Commented:
thnx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.