how to get file created date and time using xp_getfiledetails stored procedure

Question

Post reply

how to get file created date and time using xp_getfiledetails stored procedure

nitincaet05

SSC Enthusiast

Points: 132
More actions
December 23, 2009 at 10:54 am

#215057

Hi,
I am using extended stored procedure to read file details and inserting those values in the tabel call filedetails table. Here is the structure of the table
CREATE TABLE [filedetails ] (
[alt_name] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[file_size] [numeric](18, 0) NULL ,
[creation_date] [numeric](18, 0) NULL ,
[creation_time] [numeric](18, 0) NULL ,
[last_written_date] [numeric](18, 0) NULL ,
[last_written_time] [numeric](18, 0) NULL ,
[last_acc_date] [numeric](18, 0) NULL ,
[last_acc_time] [numeric](18, 0) NULL ,
[attributes] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
How I can file created date and time from filedetails table since extended stored procedure is inserting creation_date as "20091215" & creation_time as "173736"?
I am looking for format something like "12/15/2009 09:30:00 AM"
Thank you
Nitin

Viewing 7 posts - 1 through 7 (of 7 total)

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

Here is the related contents of that XP

// Set the Creation Date column in YYYYMMDD format

record.SetSqlString(2,f.CreationTime.ToString("yyyyMMdd"));

// Set the Creation Time column in HHMMSS

record.SetSqlString(3,f.CreationTime.ToString("HHmmss"));

From there you would be able to do your string manipulations, cast, convert, concatenation, etc.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

nitincaet05 SSC Enthusiast Points: 132 More actions · Answer 2

Thank you for your response. I am trying to convert it into SQL statement though. How do I convert iti in SQL statement?

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

One of two methods come to mind:

1. Use this command in an SSIS task and then do some transformations on it prior to inputting the data into the table.

2. Use an intermediary table and then use convert or cast in an insert/select statement.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

nitincaet05 SSC Enthusiast Points: 132 More actions · Answer 4

OK. This code runs on the server over the ASP, Not sure how to use SSIS task..

How do I convert creation_time ("173736") to time format?

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

nitincaet05 (12/23/2009)
OK. This code runs on the server over the ASP, Not sure how to use SSIS task..
How do I convert creation_time ("173736") to time format?

17h37m36s

5:37:36 PM

HH = left(creation_time,2)

SS = right(creation_time,2)

MM = SUBSTRING ( creation_time ,3 , 2 )

sample

declare @time char(6)

set @time = '173736'

Select HH = left(@time,2),MM = SUBSTRING ( @time ,3 , 2 ),SS = right(@time,2)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

nitincaet05 SSC Enthusiast Points: 132 More actions · Answer 6

Got it. Thank you so much Jason. Appreciate your help.