Date conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date conversion

Author

Topic

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-06 : 05:18:10

Hi Guys,

Hi just want to convert date from numeric format

I have results like this

JobName Status Run_Date
ADHOC Reset Monitor failed 20061124
ADHOC Reset Monitor failed 20061124
ADHOC Reset Monitor Success 20061124
ADHOC Reset Monitor Success 20061124
Audit Trace File Maint failed 20061124
Audit Trace File Maint failed 20061124

for this i m using the following coversion but it;s not working

select sysjobs.name, case run_status when 1 then 'Success' else 'failed' end, convert(varchar(10),run_date,105), run_time
from sysjobs, sysjobhistory
where sysjobs.job_id = sysjobhistory.job_id
order by sysjobs.name, convert(varchar(10),run_date, 105), run_time

Could you help me to convert date in dd-mm-yyyy format

Please

Yogesh V. Desai. | SQLDBA|

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-06 : 05:31:32

SELECT CONVERT(CHAR(10), CAST(Run_Date AS VARCHAR(8)), 105)
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 06:10:52

Ermmm ... I think you'll need to get:

INT -> CHAR
CHAR -> DATETIME
DATETIME -> CHAR format 105

thus:

SELECT CONVERT(CHAR(10), CAST(CAST(Run_Date AS CHAR(8)) AS DATETIME), 105)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 06:25:40

1 Always use proper DATETIME datatype to store dates
2 If not, you should create new column which is of DATETIME datatype and update that with proper values

Update table set new_datetime_column=cast(numeric_date_column as varchar(8))

and use this new_datetime_column in the queries

3 Let front end application format the dates

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 06:35:04

"Always use proper DATETIME datatype to store dates"

Hehehe ... trouble is MS has used an INT for this column in msdb.dbo.sysjobhistory.run_date

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 09:59:06

quote:
Originally posted by Kristen

"Always use proper DATETIME datatype to store dates"

Hehehe ... trouble is MS has used an INT for this column in msdb.dbo.sysjobhistory.run_date

Sysjobhistory table has two columns run_date and run_time(where date and time are seperated)
Note that crdate column of sysobjects is of DATETIME datatype

Madhivanan

Failing to plan is Planning to fail

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-06 : 12:09:48

hey thanks a lot guys

Yogesh V. Desai. | SQLDBA|

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-07 : 10:50:07

hey thanks guys it's done.

Yogesh V. Desai. | SQLDBA|

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-07 : 11:19:36