Database Backup

I want to add a datetime stamp on my backup file names but what I have is not
adding to the filename.
Does anyone know how to do this?
It would help if you gave a little more background or at least showed what
you were doing now. Here is an example of adding a datetime to a backup
device name but I don't know hot it fits your situation.
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT Catalog_Name
FROM Information_Schema.Schemata
-- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
PRINT 'Backing up database ' + @.DBName
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
PRINT '--- '
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>I want to add a datetime stamp on my backup file names but what I have is
>not
> adding to the filename.
> Does anyone know how to do this?
|||I also want to do this for the transaction log files.
"Andrew J. Kelly" wrote:

> It would help if you gave a little more background or at least showed what
> you were doing now. Here is an example of adding a datetime to a backup
> device name but I don't know hot it fits your situation.
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> -- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> SET @.Name = @.DBName + N' Full Backup'
> PRINT 'Backing up database ' + @.DBName
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> PRINT '--- '
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>
>
|||Sorry. Currently I am performing complete backups to a network drive. I want
to modify the backups by appending the date and time stamp to the backup
filenames. Then run either a batch file or DTS package to clean-up the folder
by deleting SQL backups after 7 days.
"Andrew J. Kelly" wrote:

> It would help if you gave a little more background or at least showed what
> you were doing now. Here is an example of adding a datetime to a backup
> device name but I don't know hot it fits your situation.
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> -- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> SET @.Name = @.DBName + N' Full Backup'
> PRINT 'Backing up database ' + @.DBName
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> PRINT '--- '
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>
>
|||Andrew,
Thanks worked. How can I add the time to it as well. I am going to create a
DTS package to run this query.
"Andrew J. Kelly" wrote:

> It would help if you gave a little more background or at least showed what
> you were doing now. Here is an example of adding a datetime to a backup
> device name but I don't know hot it fits your situation.
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>
> DECLARE cur_DBs CURSOR STATIC LOCAL
> FOR SELECT Catalog_Name
> FROM Information_Schema.Schemata
> -- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
> WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB')
>
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Full_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> SET @.Name = @.DBName + N' Full Backup'
> PRINT 'Backing up database ' + @.DBName
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
> RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
> PRINT '--- '
> FETCH NEXT FROM cur_DBs INTO @.DBName
> END
> CLOSE cur_DBs
> DEALLOCATE cur_DBs
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:7500995A-A50E-4C69-BF45-FA9CDB70AEBF@.microsoft.com...
>
>
|||Just change the CONVERT to use an different format and make the variable
large enough to handle the extra characters. You will have to look at
CONVERT in BOL to see which suites your requirement best. The only thing is
that when you add the time portion you usually have to use a format that
inserts dashs or slashes. See if this works for you:
LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
GETDATE(),120),'-',''),':',''),' ',''),12)
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...[vbcol=seagreen]
> Andrew,
> Thanks worked. How can I add the time to it as well. I am going to create
> a
> DTS package to run this query.
> "Andrew J. Kelly" wrote:
|||That worked thank you.
"Andrew J. Kelly" wrote:

> Just change the CONVERT to use an different format and make the variable
> large enough to handle the extra characters. You will have to look at
> CONVERT in BOL to see which suites your requirement best. The only thing is
> that when you add the time portion you usually have to use a format that
> inserts dashs or slashes. See if this works for you:
> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
> GETDATE(),120),'-',''),':',''),' ',''),12)
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
>
>
|||Andrew,
Do you know how to access a network drive within Enterprise Mgr?
I'm trying to restore a db where the backups are located on the network. I
can see other network drives but cannot view the I need. I've already checked
the permissions and made sure the drive was shared. Do you have any
suggestions?
"Andrew J. Kelly" wrote:

> Just change the CONVERT to use an different format and make the variable
> large enough to handle the extra characters. You will have to look at
> CONVERT in BOL to see which suites your requirement best. The only thing is
> that when you add the time portion you usually have to use a format that
> inserts dashs or slashes. See if this works for you:
> LEFT(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),
> GETDATE(),120),'-',''),':',''),' ',''),12)
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:3661E6F5-DECE-4A20-BFD1-70927898E1C7@.microsoft.com...
>
>
|||Don't use EM, use a script instead. EM has some limitations and things like
this can be much more controlled through scripts. The restore syntax is
pretty simple and there are examples in BOL. If you have troubles with it
post your script and we can help.
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:B202FDF2-5301-4E8E-9558-F2E0505F563C@.microsoft.com...[vbcol=seagreen]
> Andrew,
> Do you know how to access a network drive within Enterprise Mgr?
> I'm trying to restore a db where the backups are located on the network. I
> can see other network drives but cannot view the I need. I've already
> checked
> the permissions and made sure the drive was shared. Do you have any
> suggestions?
> "Andrew J. Kelly" wrote:

Data validation for datetime parameter in SSRS

Hi,

I wanted to know more about validation of SSRS parameters. I have a simple report which has a parameter called startdate of DateTime datatype. The datetime parameter in SSRS takes manual input as well. So, the user can enter any junk value. I want to ensure that the input parameter is in correct format and I want to display an error msg when the format is incorrect. My report has the following VB code for validation:

Public Function Validate( ByVal startdate As String) As Boolean
If IsDate(startdate) = True Then
Return True
Else
Return False
End If
End Function

And my report has a textbox which has the expression property set to;

=Code.Validate(Parameters!startdate.Value)

the textbox on the report has to display if the entered date is valid or not.

But, when i enter an erroneous date, SSRS doesn't render the report and throws a generic error. This happens even before the code written for validating the parameter executes.

Also couldn't find a way to disable the manual input for the datetime parameter. Even that would solve the problem.

Another alternative was to make the startdate parameter as string, but i want the calendar control button to be provided for the user.

The function below checks for the Start and End date ranges .

Function:

Function CheckDateParameters(StartDate as Date, EndDate as Date) as Integer
Dim msg as String
msg = ""
If (StartDate > EndDate) Then
msg="Start Date should not be later than End Date"
End If
If msg <> "" Then
MsgBox(msg, 16, "Report Validation")
Err.Raise(6,Report) 'Raise an overflow
End If
End Function

Steps:

1.) Go the Report Parameters and add a parameter with the datatype is string.

2.) Check the Hidden checkbox and Allow blank value ckeckbox.

3.) From Default Values choose Non-Queried radio button and then press the FX button and paste this code.

=CODE.CheckDateParameters(<parameterStartdate>.Value,<parameterEnddate>.Value)

Then press OK.

Hope this helps......|||

In the sample that you provided, if we enter an improper date as 02/31/2003 [mm/dd/yyyy], then the report execution fails and an error is thrown right away "error occured during the processing of report parameter". I was talking of handling such errors.

I feel that reporting services, initially validates the entered value matches the datatype of the parameter and then proceeds with execution of any VB code and finally renders the report.

When I said validation, I wanted a functionality similar to the client side validation in an asp page, where improper date formats like 12/31/235668 etc can be taken care of.

Data types question - Varbinary and timestamps

Greetings once again SSIS friends,

I have some source tables which contain timestamp fields (that's timestamp data type not datetime). My dimension table holds the maximum timestamp value as a varbinary(8).

I want my package to have a variable that holds that value but I don't know which data type to use for this. The reason for this is because I want to use that variable to then retrieve all records from my source table that have a timestamp value greater than the value stored in the variable.

Please advise on what data type is suitable.

Thanks for your help in advance.

Greetings once again guys,

I found the following article which gives a workable solution to my problem, but I am still interested to find out the equivalent of timestamp data type in SSIS.

http://solidqualitylearning.com/blogs/erik/archive/2005/12/09/1499.aspx

|||

DT_BYTES

binary, varbinary, timestamp

binary, varbinary, timestamp

BigBinary, VarBinary

RAW

RAW

You will want to use DT_BYTES. See this article on MSDN.

http://msdn2.microsoft.com/en-us/library/ms141036.aspx

Does this answer your question?

data type to store time

Hi all ,

What datatype should I take to store time in a table -- datetime , float or decimal?

my requirement is to store "Worked Hours in a day by an employee" in the field say, 9 hrs and 30 mins.

I should be able to manipulate data in this field such as total hours present in the month, extra hours worked in a day (considering 9 hrs as standard time),less hours worked in a day, and so on

I would suggest the builtin DATETIME datatype. The advantage of this datatype is that it gives you the ability to directly use the builtin functions that come with TSQL.|||

As Kent indicated, using a datetime is most likely the best option. Yes, it will put the default date of Jan 1st, 1900, but you are interested in the hours.

You will be able to use datetime math functions, AND you don't have to remember to convert 20 minutes to .333333 hr, etc.

IF you are not concerned with seconds and milliseconds, you might consider a smalldatetime datatype.

|||Thanks Kent and Arnie for the quick response. I 'm new to this forum and sql server. Hope many more responses from you guys

Data Type timestamp

Hi,

I am a bit confused...

is the data type in sql express similar to DateTime? in other words can i convert this timestamp to DateTime or can i use this data type instead of the data type DateTime?

regards,

rnv

Even they store both 8 bytes, they are not the same, read more for this in the BOL.

timestamp

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

Remarks

The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ta-tz_6fn4.asp

If you want to use something like rowversioning, use ROWVERSION not datetime, as datetime has only the limitation to a precision of 3ms.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

thanks a lot.

|||Could you please mark the post as solved then, thanks.

Jens.

data type DateTime

Hi all!!
I'm developing a project using VS 2005 (C# code) and SQL Server 2005.
I have some problems with the data type 'DateTime'.

In some parts of my project, I have used DataSets and DataAdapters and I can insert in a dataRow a data type 'DateTime' and it is inserted as dd/mm/yyyy hh:mm:ss. That's OK.

But in other parts I'm not using Datatables and DataAdapters and I insert new rows in the database using ExecuteNonQuery, but the data type 'DateTime' in the format dd/mm/yyyy hh:mm:ss is not valid. It only allows formats like mm/dd/yyyy hh:mm:ss, but I want the other format.

Why is dd/mm/yyyy hh:mm:ss working with DataAdapters? Why is dd/mm/yyyy hh:mm:ss not working with ExecuteNonQuery? Is there any way to specify in the Database that I want the format dd/mm/yyyy hh:mm:ss??

Thanks in advance folks,
Javier.

Try to add a setting for DATEFORMAT just before you insert statement, for example:

SET DATEFORMAT dmy; insert into ...

|||Thanks Iori_Jay,
But I've checked that it is necessary to write "SET DATEFORMAT dmy" every time I want to insert a new row in the table that has DateTime attribute. Is there another way of keeping this value within the DB's life??

thanks again|||Since the DATEFORMAT option is a session(means connection to SQL) option, you have to set it in each connection. And within the connection, the option is effective.

Data Type Convertion Error

Hello,
I need to convert one character field to datetime and after make the
differenc between two fields.
The problem is that if in the output result appears one value that is
biggest then 23:59:59 hours its shown the
following error:
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
Example:
select convert(datetime, field, 108) from table_name
or
select convert (datetime, '24:00:00', 108) from table_name
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
I need to use all the results and know the difference between them in hour
or minutes.
Example:
select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
field_B, 108))
from table_name
Can you help me?
Thanks and best regards
Hi
24:00:00 is not a valid time. In effect, it is 00:00:00.000 the next day.
00:00:00.000 to 23:59:59.999 is
You need to fix your data to a ISO valid date value.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CD38A959-9022-4BA9-80B6-1AFCDE0AD8FB@.microsoft.com...
> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards
|||try...
select datediff(mi,
dateadd(ss,cast(substring(field1,1,2) as
int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,2)
as int),cast(0 as datetime)),
dateadd(ss,cast(substring(field2,1,2) as
int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,2)
as int),cast(0 as datetime))
)
-- example
select datediff(mi,
dateadd(ss,cast(substring('34:15:10',1,2) as
int)*60*60+cast(substring('34:15:10',4,2) as int)*60+
cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
dateadd(ss,cast(substring('24:18:10',1,2) as
int)*60*60+cast(substring('24:18:10',4,2) as int)*60+
cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
)
"CC&JM" wrote:

> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards
|||Thanks everybody.
Best regards
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> try...
> select datediff(mi,
> dateadd(ss,cast(substring(field1,1,2) as
> int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,2)
> as int),cast(0 as datetime)),
> dateadd(ss,cast(substring(field2,1,2) as
> int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,2)
> as int),cast(0 as datetime))
> )
>
>
> -- example
> select datediff(mi,
> dateadd(ss,cast(substring('34:15:10',1,2) as
> int)*60*60+cast(substring('34:15:10',4,2) as int)*60+
> cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
> dateadd(ss,cast(substring('24:18:10',1,2) as
> int)*60*60+cast(substring('24:18:10',4,2) as int)*60+
> cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
> )
>
>
> "CC&JM" wrote:

Data Type Convertion Error

Hello,
I need to convert one character field to datetime and after make the
differenc between two fields.
The problem is that if in the output result appears one value that is
biggest then 23:59:59 hours its shown the
following error:
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
Example:
select convert(datetime, field, 108) from table_name
or
select convert (datetime, '24:00:00', 108) from table_name
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
I need to use all the results and know the difference between them in hour
or minutes.
Example:
select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
field_B, 108))
from table_name
Can you help me?
Thanks and best regardsHi
24:00:00 is not a valid time. In effect, it is 00:00:00.000 the next day.
00:00:00.000 to 23:59:59.999 is
You need to fix your data to a ISO valid date value.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CD38A959-9022-4BA9-80B6-1AFCDE0AD8FB@.microsoft.com...
> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards|||try...
select datediff(mi,
dateadd(ss,cast(substring(field1,1,2) as
int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,2)
as int),cast(0 as datetime)),
dateadd(ss,cast(substring(field2,1,2) as
int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,2)
as int),cast(0 as datetime))
)
-- example
select datediff(mi,
dateadd(ss,cast(substring('34:15:10',1,2
) as
int)*60*60+cast(substring('34:15:10',4,2
) as int)*60+
cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
dateadd(ss,cast(substring('24:18:10',1,2
) as
int)*60*60+cast(substring('24:18:10',4,2
) as int)*60+
cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
)
"CC&JM" wrote:

> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards|||Thanks everybody.
Best regards
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> try...
> select datediff(mi,
> dateadd(ss,cast(substring(field1,1,2) as
> int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,
2)
> as int),cast(0 as datetime)),
> dateadd(ss,cast(substring(field2,1,2) as
> int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,
2)
> as int),cast(0 as datetime))
> )
>
>
> -- example
> select datediff(mi,
> dateadd(ss,cast(substring('34:15:10',1,2
) as
> int)*60*60+cast(substring('34:15:10',4,2
) as int)*60+
> cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
> dateadd(ss,cast(substring('24:18:10',1,2
) as
> int)*60*60+cast(substring('24:18:10',4,2
) as int)*60+
> cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
> )
>
>
> "CC&JM" wrote:
>

Data Type Convertion Error

Hello,
I need to convert one character field to datetime and after make the
differenc between two fields.
The problem is that if in the output result appears one value that is
biggest then 23:59:59 hours its shown the
following error:
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
Example:
select convert(datetime, field, 108) from table_name
or
select convert (datetime, '24:00:00', 108) from table_name
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
I need to use all the results and know the difference between them in hour
or minutes.
Example:
select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
field_B, 108))
from table_name
Can you help me?
Thanks and best regardsHi
24:00:00 is not a valid time. In effect, it is 00:00:00.000 the next day.
00:00:00.000 to 23:59:59.999 is
You need to fix your data to a ISO valid date value.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CD38A959-9022-4BA9-80B6-1AFCDE0AD8FB@.microsoft.com...
> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards|||try...
select datediff(mi,
dateadd(ss,cast(substring(field1,1,2) as
int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,2)
as int),cast(0 as datetime)),
dateadd(ss,cast(substring(field2,1,2) as
int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,2)
as int),cast(0 as datetime))
)
-- example
select datediff(mi,
dateadd(ss,cast(substring('34:15:10',1,2) as
int)*60*60+cast(substring('34:15:10',4,2) as int)*60+
cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
dateadd(ss,cast(substring('24:18:10',1,2) as
int)*60*60+cast(substring('24:18:10',4,2) as int)*60+
cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
)
"CC&JM" wrote:
> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards|||Thanks everybody.
Best regards
"Aleksandar Grbic" wrote:
> try...
> select datediff(mi,
> dateadd(ss,cast(substring(field1,1,2) as
> int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,2)
> as int),cast(0 as datetime)),
> dateadd(ss,cast(substring(field2,1,2) as
> int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,2)
> as int),cast(0 as datetime))
> )
>
>
> -- example
> select datediff(mi,
> dateadd(ss,cast(substring('34:15:10',1,2) as
> int)*60*60+cast(substring('34:15:10',4,2) as int)*60+
> cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
> dateadd(ss,cast(substring('24:18:10',1,2) as
> int)*60*60+cast(substring('24:18:10',4,2) as int)*60+
> cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
> )
>
>
> "CC&JM" wrote:
> > Hello,
> >
> > I need to convert one character field to datetime and after make the
> > differenc between two fields.
> > The problem is that if in the output result appears one value that is
> > biggest then 23:59:59 hours its shown the
> > following error:
> >
> > (xxxxxxx row(s) affected)
> > Server: Msg 242, Level 16, state 3, line 1
> > The convertion of a char data type to a datetime data type resulted in an
> > out-of-range datetime value
> >
> > Example:
> >
> > select convert(datetime, field, 108) from table_name
> > or
> > select convert (datetime, '24:00:00', 108) from table_name
> >
> >
> > (xxxxxxx row(s) affected)
> > Server: Msg 242, Level 16, state 3, line 1
> > The convertion of a char data type to a datetime data type resulted in an
> > out-of-range datetime value
> >
> > I need to use all the results and know the difference between them in hour
> > or minutes.
> > Example:
> >
> > select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> > field_B, 108))
> > from table_name
> >
> > Can you help me?
> >
> > Thanks and best regards

data type conversion in stored procedure

Hi,

I have a stored procedure a portion of which looks like this:

IF (CAST (@.intMyDate AS datetime)) IN (
SELECT DISTINCT SHOW_END_DATE
FROM PayPerView PP
WHERE PP_Pay_Indicator = 'N' )
BEGIN
--ToDo Here
END

where:
@.intMyDate is of type int and is of the form 19991013
SHOW_END_DATE is of type datetime and is of the form 13/10/1999

however when I run the procedure in sql query analyzer as:

EXEC sp_mystoredproc param1, param2

i get the error:

Server: Msg 242, Level 16, State 3, Procedure usp_Summary_Incap, Line 106
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

what is the proper way of doing the conversion from int to datetime in stored procedure?

thank you!

cheers,
g11DBPost some sample data, along with the datetime values you expect them to be converted to.|||Hi blindman,

Below are the sample data:

@.intMyDate is of type int and is of the form 19991013
SHOW_END_DATE is of type datetime and is of the form 13/10/1999

I just want to convert @.intMyDate of type int which is of the form 19991013 to a type datetime which is of the form 13/10/1999

I'm thinking of converting int to varchar and then concatenating the three parts and adding "/" such that "13/10/1999".
Then using the function Convert

Convert(datetime, "13/10/1999", 103)

Is this the best way to go about it?

Thank you again.

cheers,
g11DB|||I'm thinking of converting int to varchar and then concatenating the three parts and adding "/" such that "13/10/1999".
Then using the function Convert

Convert(datetime, "13/10/1999", 103)

Is this the best way to go about it?
Yes - except convert it to ISO format:
YYYY-MM-DD
Get into that habit and no matter where you find yourself working you never need to worry if SQL Server thinks 08/04/2006 is the 8th of April or the 4th of August.

HTH|||Yes, convert it to char(8) and then deal with it as a string.
And when you find some spare time, go and shoot the idiot who decided to store date values that way. You'll be glad you did.|||In case you are interested (and even if you are not :)) casting 19991013 as a date was basically saying to SQL Server:
Please add 19, 991, 013 days to 1st Jan 1900 and give me the date. SQL Server obviously isn't future proofed because it can't handle dates 54 thousand years into the future.

That's why Blindman shoots people like that :)

probably the most exciting thing you'll read about SQL Server dates this week:
http://www.dbforums.com/showthread.php?t=1212546|||Actually, I just enjoy shooting people.|||Hi,
try this
DECLARE @.intMyDate INT
DECLARE @.FFDATE VARCHAR(50)
DECLARE @.TDATE DATETIME
SET @.intMyDate = 19991013

SET @.FFDATE = SUBSTRING(CAST(@.intMyDate AS VARCHAR(8)),5,2) + '/' + SUBSTRING(CAST(@.intMyDate AS VARCHAR(8)),7,2) + '/' + SUBSTRING(CAST(@.intMyDate AS VARCHAR(8)),1,4)
SET @.TDATE = CAST(@.FFDATE AS DATETIME)

PRINT @.TDATE|||That's jolly good code however I would still recommend you use the same technique but output the date in ISO (YYYY-MM-DD) format. If you don't believe me then check the link I provided and you'll see Pat making the same point :)|||I Agree, But the poster wants in MM/dd/yyyy format.|||No, he just wants to convert it to a datetime datatype, so the preference for formatting the intermediary string as YYYY-MM-DD is valid. How the resulting datetime value is displayed is a secondary issue. Please revue the section on datetime datatype in Books Online.|||Yes - except convert it to ISO format:
YYYY-MM-DD
Get into that habit and no matter where you find yourself working you never need to worry if SQL Server thinks 08/04/2006 is the 8th of April or the 4th of August.

HTH

and you always have the chance of use words for months anyway, so '08/april/2006' never would be 4th of august ;)

declare @.d datetime
set @.d = '08/april/2006'
select @.d

--> 2006-04-08 00:00:00.000

Data Type (Time Format)

Hi all,
I have a colume assigned to datetime format, where the value is say
"06:31:31" rather than 20/05/2004 06:31:31. When I retrieve the value from
the table, the result were 30/12/1899 06:31:31. Is there a way to change the
format so that I only need to put the time variable into the field as
opposed to the full datetime format?
Thanks
There is no datatype in SQL Server for just time. However, display is a
client issue; you should be able to pull just the time element out in
whatever language you're using for reporting/application development. You
can also use CONVERT to pull the time out as a text string:
SELECT CONVERT(CHAR(8), YourDateTimeCol, 8) AS TheTime
FROM YourTable
If, as I suspect, what you're storing is actually a duration, you might
consider storing it as number of seconds in a column of integer datatype
instead of using datetime. This will make calculations (if necessary) much
easier.
"VampireJ" <jwlchung@.yahoo.com.hk> wrote in message
news:ujXbOHmPEHA.556@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a colume assigned to datetime format, where the value is say
> "06:31:31" rather than 20/05/2004 06:31:31. When I retrieve the value from
> the table, the result were 30/12/1899 06:31:31. Is there a way to change
the
> format so that I only need to put the time variable into the field as
> opposed to the full datetime format?
> Thanks
>
|||Thanks very much, I will give that a try.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:e1WsrfoPEHA.2580@.TK2MSFTNGP09.phx.gbl...
> There is no datatype in SQL Server for just time. However, display is a
> client issue; you should be able to pull just the time element out in
> whatever language you're using for reporting/application development. You
> can also use CONVERT to pull the time out as a text string:
> SELECT CONVERT(CHAR(8), YourDateTimeCol, 8) AS TheTime
> FROM YourTable
> If, as I suspect, what you're storing is actually a duration, you might
> consider storing it as number of seconds in a column of integer datatype
> instead of using datetime. This will make calculations (if necessary)
much[vbcol=seagreen]
> easier.
>
> "VampireJ" <jwlchung@.yahoo.com.hk> wrote in message
> news:ujXbOHmPEHA.556@.tk2msftngp13.phx.gbl...
from
> the
>
|||hi
i have the same problem that you have.
can you please let me know if you find the solution.
thanks
alina
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Data Type (Time Format)

Hi all,
I have a colume assigned to datetime format, where the value is say
"06:31:31" rather than 20/05/2004 06:31:31. When I retrieve the value from
the table, the result were 30/12/1899 06:31:31. Is there a way to change the
format so that I only need to put the time variable into the field as
opposed to the full datetime format?
ThanksThere is no datatype in SQL Server for just time. However, display is a
client issue; you should be able to pull just the time element out in
whatever language you're using for reporting/application development. You
can also use CONVERT to pull the time out as a text string:
SELECT CONVERT(CHAR(8), YourDateTimeCol, 8) AS TheTime
FROM YourTable
If, as I suspect, what you're storing is actually a duration, you might
consider storing it as number of seconds in a column of integer datatype
instead of using datetime. This will make calculations (if necessary) much
easier.
"VampireJ" <jwlchung@.yahoo.com.hk> wrote in message
news:ujXbOHmPEHA.556@.tk2msftngp13.phx.gbl...
> Hi all,
> I have a colume assigned to datetime format, where the value is say
> "06:31:31" rather than 20/05/2004 06:31:31. When I retrieve the value from
> the table, the result were 30/12/1899 06:31:31. Is there a way to change
the
> format so that I only need to put the time variable into the field as
> opposed to the full datetime format?
> Thanks
>|||Thanks very much, I will give that a try.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:e1WsrfoPEHA.2580@.TK2MSFTNGP09.phx.gbl...
> There is no datatype in SQL Server for just time. However, display is a
> client issue; you should be able to pull just the time element out in
> whatever language you're using for reporting/application development. You
> can also use CONVERT to pull the time out as a text string:
> SELECT CONVERT(CHAR(8), YourDateTimeCol, 8) AS TheTime
> FROM YourTable
> If, as I suspect, what you're storing is actually a duration, you might
> consider storing it as number of seconds in a column of integer datatype
> instead of using datetime. This will make calculations (if necessary)
much
> easier.
>
> "VampireJ" <jwlchung@.yahoo.com.hk> wrote in message
> news:ujXbOHmPEHA.556@.tk2msftngp13.phx.gbl...
from[vbcol=seagreen]
> the
>|||hi
i have the same problem that you have.
can you please let me know if you find the solution.
thanks
alina
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

Data Type - DateTime

After adding four datetime related parameters, the following error is
displayed when attempting to run the report. The first two datetime
parameters causes no challenges until the third datetime parameter is added.
An error occurred during local report processing.
An error has occurred during report processing.
Cannot read next data row for the data set IPC_VISION_BCL.
Conversion failed when converting datetime from character string.Hi,
do the parameters are configured as datetime on the VS'
How do u get your dataset by SP'
usually i configure the date parameters as string and in the sp convert them
to the wright format...
"Terry" wrote:
> After adding four datetime related parameters, the following error is
> displayed when attempting to run the report. The first two datetime
> parameters causes no challenges until the third datetime parameter is added.
> An error occurred during local report processing.
> An error has occurred during report processing.
> Cannot read next data row for the data set IPC_VISION_BCL.
> Conversion failed when converting datetime from character string.
>
>