Im having to convert dates for an mainframe file. 3/3/2005 to 03032005. So I
datepart each portion of the date and then add zeros when needed and string
it back together.
When trying to do some of the checks on the dates
set @.Month=convert(varchar(2),DATEPART(month
, @.myDate))
if(@.@.ERROR=0)
begin
--set @.Day
--set @.Year
end
else
begin
set @.NewDate=@.myDate
end
the SQL will blow up on the first line SOMETIMES
Its like it doesnt bother to exec the if(@.@.ERROR) Yet other time even with
the error it keeps on chugging along
Whats the deal?
JP
.NET Software DevelperIn case that @.myDate is type of varchar.
Datepart will raise an error only when you pass a value which cannot be
recognized as valid datetime format. Datepart of null returns null and,
consequently, Conversion of null returns null => no error.
MSDN Quote:
SQL Server recognizes date and time data enclosed in single quotation marks
(') in these formats:
-Alphabetic date formats (for example, 'April 15, 1998')
-Numeric date formats (for example, '4/15/1998', 'April 15, 1998')
-Unseparated string formats (for example, '19981207', 'December 12, 1998')
In your specific case Convert function cannot raise an error.
Regards,
Marko Simic
"JP" wrote:
> Im having to convert dates for an mainframe file. 3/3/2005 to 03032005. So
I
> datepart each portion of the date and then add zeros when needed and strin
g
> it back together.
> When trying to do some of the checks on the dates
> set @.Month=convert(varchar(2),DATEPART(month
, @.myDate))
> if(@.@.ERROR=0)
> begin
> --set @.Day
> --set @.Year
> end
> else
> begin
> set @.NewDate=@.myDate
> end
> the SQL will blow up on the first line SOMETIMES
> Its like it doesnt bother to exec the if(@.@.ERROR) Yet other time even with
> the error it keeps on chugging along
> Whats the deal?
> --
> JP
> .NET Software Develper
Showing posts with label date. Show all posts
Showing posts with label date. Show all posts
Tuesday, March 27, 2012
Saturday, February 25, 2012
.mdf and .ldf file modified date
SQL Server 2000
Looking for some explanation for when the file modified date gets changed
for .ldf and .mdf files.
Windows Explorer does not seem to be the date I would expect. For instance
when a tran log gets dumped the date modified would change.
Thanks for any information on this issue.
While the files are open I don't think you'll see the file date change in
any way that's predictable or useful. When the db is closed, detached or SQL
Server shutdown then the date will be updated. Why do you care?
David Portas
SQL Server MVP
|||Well,
I know that the dates are not updated when SQL server is shutdown and then
restarted.
Why do I care? Curiosity, or perhaps a determination to understand what is
happening.
Joe
"David Portas" wrote:
> While the files are open I don't think you'll see the file date change in
> any way that's predictable or useful. When the db is closed, detached or SQL
> Server shutdown then the date will be updated. Why do you care?
> --
> David Portas
> SQL Server MVP
> --
>
>
Looking for some explanation for when the file modified date gets changed
for .ldf and .mdf files.
Windows Explorer does not seem to be the date I would expect. For instance
when a tran log gets dumped the date modified would change.
Thanks for any information on this issue.
While the files are open I don't think you'll see the file date change in
any way that's predictable or useful. When the db is closed, detached or SQL
Server shutdown then the date will be updated. Why do you care?
David Portas
SQL Server MVP
|||Well,
I know that the dates are not updated when SQL server is shutdown and then
restarted.
Why do I care? Curiosity, or perhaps a determination to understand what is
happening.
Joe
"David Portas" wrote:
> While the files are open I don't think you'll see the file date change in
> any way that's predictable or useful. When the db is closed, detached or SQL
> Server shutdown then the date will be updated. Why do you care?
> --
> David Portas
> SQL Server MVP
> --
>
>
.mdf and .ldf file modified date
SQL Server 2000
Looking for some explanation for when the file modified date gets changed
for .ldf and .mdf files.
Windows Explorer does not seem to be the date I would expect. For instance
when a tran log gets dumped the date modified would change.
Thanks for any information on this issue.While the files are open I don't think you'll see the file date change in
any way that's predictable or useful. When the db is closed, detached or SQL
Server shutdown then the date will be updated. Why do you care?
David Portas
SQL Server MVP
--|||Well,
I know that the dates are not updated when SQL server is shutdown and then
restarted.
Why do I care? Curiosity, or perhaps a determination to understand what is
happening.
Joe
"David Portas" wrote:
> While the files are open I don't think you'll see the file date change in
> any way that's predictable or useful. When the db is closed, detached or S
QL
> Server shutdown then the date will be updated. Why do you care?
> --
> David Portas
> SQL Server MVP
> --
>
>
Looking for some explanation for when the file modified date gets changed
for .ldf and .mdf files.
Windows Explorer does not seem to be the date I would expect. For instance
when a tran log gets dumped the date modified would change.
Thanks for any information on this issue.While the files are open I don't think you'll see the file date change in
any way that's predictable or useful. When the db is closed, detached or SQL
Server shutdown then the date will be updated. Why do you care?
David Portas
SQL Server MVP
--|||Well,
I know that the dates are not updated when SQL server is shutdown and then
restarted.
Why do I care? Curiosity, or perhaps a determination to understand what is
happening.
Joe
"David Portas" wrote:
> While the files are open I don't think you'll see the file date change in
> any way that's predictable or useful. When the db is closed, detached or S
QL
> Server shutdown then the date will be updated. Why do you care?
> --
> David Portas
> SQL Server MVP
> --
>
>
.mdf and .ldf file modified date
SQL Server 2000
Looking for some explanation for when the file modified date gets changed
for .ldf and .mdf files.
Windows Explorer does not seem to be the date I would expect. For instance
when a tran log gets dumped the date modified would change.
Thanks for any information on this issue.While the files are open I don't think you'll see the file date change in
any way that's predictable or useful. When the db is closed, detached or SQL
Server shutdown then the date will be updated. Why do you care?
--
David Portas
SQL Server MVP
--|||Well,
I know that the dates are not updated when SQL server is shutdown and then
restarted.
Why do I care? Curiosity, or perhaps a determination to understand what is
happening.
Joe
"David Portas" wrote:
> While the files are open I don't think you'll see the file date change in
> any way that's predictable or useful. When the db is closed, detached or SQL
> Server shutdown then the date will be updated. Why do you care?
> --
> David Portas
> SQL Server MVP
> --
>
>
Looking for some explanation for when the file modified date gets changed
for .ldf and .mdf files.
Windows Explorer does not seem to be the date I would expect. For instance
when a tran log gets dumped the date modified would change.
Thanks for any information on this issue.While the files are open I don't think you'll see the file date change in
any way that's predictable or useful. When the db is closed, detached or SQL
Server shutdown then the date will be updated. Why do you care?
--
David Portas
SQL Server MVP
--|||Well,
I know that the dates are not updated when SQL server is shutdown and then
restarted.
Why do I care? Curiosity, or perhaps a determination to understand what is
happening.
Joe
"David Portas" wrote:
> While the files are open I don't think you'll see the file date change in
> any way that's predictable or useful. When the db is closed, detached or SQL
> Server shutdown then the date will be updated. Why do you care?
> --
> David Portas
> SQL Server MVP
> --
>
>
Subscribe to:
Posts (Atom)