• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

SQL convert doesn't work

When I query the following:
SELECT     schdtime AS Before, CONVERT(datetime, schdtime, 120) AS After
FROM         sheet
I get:

            Before                                            After
1/29/2009 5:00:00 PM                    1/29/2009 5:00:00 PM


I'm trying to convert the time to a 24-hour format but it isn't working, any ideas?


0
dcmorrell
Asked:
dcmorrell
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This works fine for me..
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Can you provide some more details so that we can try to identify where exactly is the problem.

What about the datatype of the column?
0
 
SharathData EngineerCommented:
Its working fine as rrjegan17 said. Post what exactly you want?
declare @schdtime varchar(20)
set @schdtime = '1/29/2009 5:00:00 PM'
select @schdtime before ,convert(datetime,@schdtime,120) after
 
before	                  after
1/29/2009 5:00:00 PM	2009-01-29 17:00:00.000
 
declare @schdtime datetime
set @schdtime = '1/29/2009 5:00:00 PM'
select @schdtime before ,convert(datetime,@schdtime,120) after
 
before	                   after
2009-01-29 17:00:00.000	2009-01-29 17:00:00.000

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you wnat:
SELECT     schdtime AS Before, CONVERT(varchar(20), schdtime, 120) AS After
FROM         sheet

Open in new window

0
 
Göran AnderssonCommented:
A datetime value doesn't have a format at all, it only gets a format when it's converted into text. So, to get the datetime value in a specific format you have to convert it to a varchar, not a datetime.

To get a format like  2009-01-29 17:00:00 you use a varchar(19):

select schdtime as Before, convert(varchar(19), schdtime, 120) as After
from sheet

If you want it without the seconds, use a varchar(16) instead.
0
 
dportasCommented:
CONVERT has no control over how your client application displays datetime values. That's purely a client-side feature.

The purpose of CONVERT is to permit conversion between types. You could convert the datetime value to a string but in most cases it would be better to control formatting in your presentation tier.
0
 
dcmorrellAuthor Commented:
Thank you.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now