Read file info via Transact SQL....

Hi there.
Any ideas how I could go about getting a file's "Created" date/time into a
datetime variable using T-SQL? I'm thinking along the lines of using the
results of a call to xp_cmdshell but as to what command I should call...
well...
Any help would be appreciated!Sure you can use any DOS command via xp_cmdshell. Here is a rough example
of reading datetimes.
CREATE TABLE #dirlist (FName VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
Andrew J. Kelly SQL MVP
"len" <len@.discussions.microsoft.com> wrote in message
news:74125E9D-6E97-46FE-855C-CA2913135FE3@.microsoft.com...
> Hi there.
> Any ideas how I could go about getting a file's "Created" date/time into a
> datetime variable using T-SQL? I'm thinking along the lines of using the
> results of a call to xp_cmdshell but as to what command I should call...
> well...
> Any help would be appreciated!|||Rather than placing this functionality in a stored procedure, consider a DTS
package and VBScript task.
http://msdn.microsoft.com/library/d...
flow_0793.asp
http://www.sqldts.com/?246
http://msdn.microsoft.com/library/d...efc4b9f49c7.asp
"len" <len@.discussions.microsoft.com> wrote in message
news:74125E9D-6E97-46FE-855C-CA2913135FE3@.microsoft.com...
> Hi there.
> Any ideas how I could go about getting a file's "Created" date/time into a
> datetime variable using T-SQL? I'm thinking along the lines of using the
> results of a call to xp_cmdshell but as to what command I should call...
> well...
> Any help would be appreciated!sql

Re Any ideas on this one ?

Still have some problems with the last thing on my first report ! Not able
to get the timedate field to show without the time.
If your RS parameter is set to a string, the original
> "convert(datetime,period,105)" should give you the listing you desire.
When
> this parameter is then passed to SQL it "should" automatically be
recognized
> as a date, but if not, you could pass the parameter as as string, and
then
> declare and set a new SQL parameter to the cast(@.param as datetime).
This doesn't seem to work, if I set it to string and do the convert it
still
shows the time.
I'm trying to do it the other way around but get syntax error, what could
be
wrong here:
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1,
[dbo].[udf_MyDate]
(periodestart,'/') AS pstart
FROM DEBSTAT
WHERE (DAY(PERIODESTART) <> '31')
ORDER BY PERIODESTART
DROP FUNCTION [dbo].[udf_MyDate]
Jack
--
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 44893 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dkRight mouse click on the field, properties. Select the date format you want
(assuming I correctly understand what you are looking for. It sounds like
you are concerned about displaying it versus sending a parameter to a query
without a time.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
news:e41LODJjFHA.3472@.TK2MSFTNGP10.phx.gbl...
> Still have some problems with the last thing on my first report ! Not able
> to get the timedate field to show without the time.
> If your RS parameter is set to a string, the original
>> "convert(datetime,period,105)" should give you the listing you desire.
> When
>> this parameter is then passed to SQL it "should" automatically be
> recognized
>> as a date, but if not, you could pass the parameter as as string, and
> then
>> declare and set a new SQL parameter to the cast(@.param as datetime).
> This doesn't seem to work, if I set it to string and do the convert it
> still
> shows the time.
> I'm trying to do it the other way around but get syntax error, what could
> be
> wrong here:
> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> RETURNS Nchar(20)
> AS
> BEGIN
> RETURN
> CONVERT(Nvarchar(20), datepart(mm,@.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> END
> GO
> SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1,
> [dbo].[udf_MyDate]
> (periodestart,'/') AS pstart
> FROM DEBSTAT
> WHERE (DAY(PERIODESTART) <> '31')
> ORDER BY PERIODESTART
> DROP FUNCTION [dbo].[udf_MyDate]
> Jack
>
>
> --
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at få 44893 spam-mails.
> Betalende brugere får ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>|||It's a dataset containing timedate fields, used as a parameter list, in
the
list I can only choose timedate not how to show it, tried a couple of
things
but it doesn't seem to work out as planned.
It shows up like this 05/08/05 00:00:00 and I don't need the time only the
date, Chris has tried to help me out with a userdef. function but I just
can't get it right, see the statement below.
Jack
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i en meddelelse
news:eMNLe1JjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Right mouse click on the field, properties. Select the date format you
> want (assuming I correctly understand what you are looking for. It
sounds
> like you are concerned about displaying it versus sending a parameter to
a
> query without a time.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
> news:e41LODJjFHA.3472@.TK2MSFTNGP10.phx.gbl...
>> Still have some problems with the last thing on my first report ! Not
>> able
>> to get the timedate field to show without the time.
>> If your RS parameter is set to a string, the original
>> "convert(datetime,period,105)" should give you the listing you desire.
>> When
>> this parameter is then passed to SQL it "should" automatically be
>> recognized
>> as a date, but if not, you could pass the parameter as as string, and
>> then
>> declare and set a new SQL parameter to the cast(@.param as datetime).
>> This doesn't seem to work, if I set it to string and do the convert it
>> still
>> shows the time.
>> I'm trying to do it the other way around but get syntax error, what
could
>> be
>> wrong here:
>> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
>> RETURNS Nchar(20)
>> AS
>> BEGIN
>> RETURN
>> CONVERT(Nvarchar(20), datepart(mm,@.indate))
>> + @.separator
>> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
>> + @.separator
>> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
>> END
>> GO
>> SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1,
>> [dbo].[udf_MyDate]
>> (periodestart,'/') AS pstart
>> FROM DEBSTAT
>> WHERE (DAY(PERIODESTART) <> '31')
>> ORDER BY PERIODESTART
>> DROP FUNCTION [dbo].[udf_MyDate]
>> Jack
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 44893 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Here is the issue. If you have the data type of the report parameter as a
datetime you have no choice, it will show the time. If you don't want to
show the time then you need to have it as string datatype for the report
parameter parameter.
select convert(varchar(10),getdate(), 101) as Param
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
news:%23kYqIZKjFHA.320@.TK2MSFTNGP09.phx.gbl...
> It's a dataset containing timedate fields, used as a parameter list, in
> the
> list I can only choose timedate not how to show it, tried a couple of
> things
> but it doesn't seem to work out as planned.
> It shows up like this 05/08/05 00:00:00 and I don't need the time only the
> date, Chris has tried to help me out with a userdef. function but I just
> can't get it right, see the statement below.
> Jack
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i en meddelelse
> news:eMNLe1JjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>> Right mouse click on the field, properties. Select the date format you
>> want (assuming I correctly understand what you are looking for. It
> sounds
>> like you are concerned about displaying it versus sending a parameter to
> a
>> query without a time.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
>> news:e41LODJjFHA.3472@.TK2MSFTNGP10.phx.gbl...
>> Still have some problems with the last thing on my first report ! Not
>> able
>> to get the timedate field to show without the time.
>> If your RS parameter is set to a string, the original
>> "convert(datetime,period,105)" should give you the listing you desire.
>> When
>> this parameter is then passed to SQL it "should" automatically be
>> recognized
>> as a date, but if not, you could pass the parameter as as string, and
>> then
>> declare and set a new SQL parameter to the cast(@.param as datetime).
>> This doesn't seem to work, if I set it to string and do the convert it
>> still
>> shows the time.
>> I'm trying to do it the other way around but get syntax error, what
> could
>> be
>> wrong here:
>> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
>> RETURNS Nchar(20)
>> AS
>> BEGIN
>> RETURN
>> CONVERT(Nvarchar(20), datepart(mm,@.indate))
>> + @.separator
>> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
>> + @.separator
>> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
>> END
>> GO
>> SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1,
>> [dbo].[udf_MyDate]
>> (periodestart,'/') AS pstart
>> FROM DEBSTAT
>> WHERE (DAY(PERIODESTART) <> '31')
>> ORDER BY PERIODESTART
>> DROP FUNCTION [dbo].[udf_MyDate]
>> Jack
>
> --
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at få 44893 spam-mails.
> Betalende brugere får ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>|||This is great, it works !
Thanks to all for your help, really appreciate it !
Jack
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i en meddelelse
news:OezEyjKjFHA.4000@.TK2MSFTNGP12.phx.gbl...
> Here is the issue. If you have the data type of the report parameter as a
> datetime you have no choice, it will show the time. If you don't want to
> show the time then you need to have it as string datatype for the report
> parameter parameter.
> select convert(varchar(10),getdate(), 101) as Param
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
> news:%23kYqIZKjFHA.320@.TK2MSFTNGP09.phx.gbl...
> > It's a dataset containing timedate fields, used as a parameter list, in
> > the
> > list I can only choose timedate not how to show it, tried a couple of
> > things
> > but it doesn't seem to work out as planned.
> >
> > It shows up like this 05/08/05 00:00:00 and I don't need the time only
the
> > date, Chris has tried to help me out with a userdef. function but I just
> > can't get it right, see the statement below.
> >
> > Jack
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i en meddelelse
> > news:eMNLe1JjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> >> Right mouse click on the field, properties. Select the date format you
> >> want (assuming I correctly understand what you are looking for. It
> > sounds
> >> like you are concerned about displaying it versus sending a parameter
to
> > a
> >> query without a time.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
> >> news:e41LODJjFHA.3472@.TK2MSFTNGP10.phx.gbl...
> >> Still have some problems with the last thing on my first report ! Not
> >> able
> >> to get the timedate field to show without the time.
> >>
> >> If your RS parameter is set to a string, the original
> >> "convert(datetime,period,105)" should give you the listing you
desire.
> >> When
> >> this parameter is then passed to SQL it "should" automatically be
> >> recognized
> >> as a date, but if not, you could pass the parameter as as string, and
> >> then
> >> declare and set a new SQL parameter to the cast(@.param as datetime).
> >>
> >> This doesn't seem to work, if I set it to string and do the convert it
> >> still
> >> shows the time.
> >>
> >> I'm trying to do it the other way around but get syntax error, what
> > could
> >> be
> >> wrong here:
> >>
> >> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> >> RETURNS Nchar(20)
> >> AS
> >> BEGIN
> >> RETURN
> >> CONVERT(Nvarchar(20), datepart(mm,@.indate))
> >> + @.separator
> >> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> >> + @.separator
> >> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> >> END
> >> GO
> >>
> >> SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1,
> >> [dbo].[udf_MyDate]
> >> (periodestart,'/') AS pstart
> >> FROM DEBSTAT
> >> WHERE (DAY(PERIODESTART) <> '31')
> >> ORDER BY PERIODESTART
> >>
> >> DROP FUNCTION [dbo].[udf_MyDate]
> >>
> >> Jack
> >
> >
> >
> > --
> > Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> > Den har indtil videre sparet mig for at få 44893 spam-mails.
> > Betalende brugere får ikke denne besked i deres e-mails.
> > Hent gratis SPAMfighter her: www.spamfighter.dk
> >
> >
>