need help with the code

Question

need help with the code

sql2k8

Hall of Fame

Points: 3455
More actions
July 21, 2011 at 2:47 pm

#244550

Hi,
I had SQL Server 2008 r2 Enterprise edition
CREATE TABLE [dbo].[TableA](
[Server] [nvarchar](256) NULL,
[DatabaseName] [nvarchar](256) NULL,
[BackupStartDate] [varchar](20) NULL,
[BackupFinishDate] [varchar](20) NULL,
[Physical_File_Size] [float] NULL,
[Time] [numeric](10, 2) NULL,
[Physical_Device] [varchar](500) NULL,
[Backupset_Name] [nvarchar](356) NULL
)
I had values stored in TableA under Backupstartdate and Backupfinishdate columns in the below format.
2011-07-08 22:00:06
Jul 15 2011 11:24PM
But i needs all the values under Backupstartdate and Backupfinishdate columns in the below format
Jul 15 2011 11:24PM
I tried to use the below code
select
CONVERT(VARCHAR(20),BackupStartDate , 100),
CONVERT(VARCHAR(20), BackupFinishDate, 100)
from TableA
But that is not being converted into the format (Jul 15 2011 11:24PM) for all of the values.
Can you please let me know how to fix this.
Thank You,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

datetimes should be kept in the database as datetimes...otherwise you get this kind of issue, where you have to convert it to datetime,a dn then convert it back out to a specificed varchar format again.

if you can, change the definition for TableA to have datetime for those two fields...then, only when you need to display it should you try converting to a specified varchar format.

i think your issue is using convert on a varchar field doesn't convert it implicitly to datetime, and then convert it again to format 100.

this will probably work, but you need to fix the datatype of the field in your table.

select

CONVERT(VARCHAR(20),convert(datetime,BackupStartDate) , 100),

CONVERT(VARCHAR(20), convert(datetime,BackupFinishDate), 100)

from TableA

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql2k8 Hall of Fame Points: 3455 More actions · Answer 2

You code had worked.

Thank You very much for your help.

I appreciate it.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

I STRONGLY agree with Lowell. You are shooting yourself in the foot by storing the datetime information as varchar. You are losing the entire library of date/time functions by doing that. Not to mention the format you are choosing will make indexes on those values worthless.

You are better off to do the CONVERT when you query that table and display the results. Or create a view of the table that does the conversion for you whenever you query it. But DON'T store it as character data.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills