Converting Date Data type in stored procedure

HI Experts...

I am using SQL SERVER 2005 standard edition

I have encountered a problem regarding converting date data type in stored procedure

As i was having problem taking date as input parameter in my stored procedure, so, then I changed to varchar (16) i.e.

CREATE PROCEDURE sp_CalendarCreate
@.StDate VARCHAR(16) ,
@.EDate VARCHAR(16),

then I am converting varchar to date with following code

DECLARE @.STARTDATE DATETIME
DECLARE @.ENDDATE DATETIME

SELECT @.STARTDATE = CAST(@.STDATE AS DATETIME)
SELECT @.ENDDATE = CAST(@.EDATE AS DATETIME)

When I try to execute the procedure with following code

execute sp_CalendarCreate @.stdate='12-1-06',@.edate='20-1-06'

but it gives me following error

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

Can any one tell me the solution of that problem (at ur earliest)

regards,

Anas

execute sp_CalendarCreate @.stdate='1-12-06',@.edate='1-20-06'

|||

Thanx for your reply....

yes i had sorted that out b4 u replied me... i.e. sql server uses date format mmddyy or yyyyddmm (american format)

and i was using UK/european standards

btw thanx very much for support

regards,

Anas

|||It is best to use ISO unseparated date format (YYYMMDD) or ISO 8601 (YYYY-MM-DDThh:mm:ss.nnn) datetime format for specifying values. This way you don't have to worry about DATEFORMAT or language settings of the server. Also, using varchar and converting to datetime is not a good and clean approach. Use the correct data type for the values so you can get the best benefit in terms of type checking, domain validations etc. Additionally, with your approach you will get into performance problems because parameter sniffing of the variables used in the SELECT statement will not work due to use of local variables and not parameters. See the whitepaper on compilation, caching in MSDN for more details.

Converting Date

I am unable to convert following date format in seconds (ss). Plz provide me query for the same.
Date Available :
2007-03-27 09:55:00.000select convert(datetime, '2007-03-27 09:55:00.000')

??|||select convert(datetime, '2007-03-27 09:55:00.000')
Yes, and the seconds are extracted by:
select datepart(ss, convert(datetime, '2007-03-27 09:55:00.000'))|||even the convet part of

select datepart(ss, convert(datetime, '2007-03-27 09:55:00.000'))

can be avoided and used as

select datepart(ss,'2007-03-27 09:55:00.000')|||but select datepart(ss,'2007-03-27 09:55:00.000') yields 0

i think perhaps milind wanted the answer to be 3383974800, which is that datetime converted in seconds in SQL Server

note that in unix timestamp format, the value would be 1175003700|||Perhaps, yes. Then the query he asked would be:

SELECT 3383974800|||no, not really, because you don't get that answer without doing a conversion

for example, what is the answer for '2006-09-09 09:37'?|||'2006-09-09 09:37' is not one of the available dates listed in the OP. ;)

Seriously, I think we need Milind to clarify what s/he wants.|||'2006-09-09 09:37' is not one of the available dates listed in the OP. ;)

good one :cool:

converting Datatypes:

Hi All,

how do you convert from a date to an int ? as well as converting from Varchar to and Int

in SQL server 2000 ?

I am retrieving the GetDate() which i store in column as Varchar, i then want to use it within my select statement to calulate data which i want to return i.e

DECLARE @.DateValue AS VARCHAR(20)

SELECT @.DateValue = ApplicationSettingValue FROM ApplicationSettings WHERE ApplicationSettingKey = 'ProcessDate'

print convert(int,@.DateValue) - 5
print GetDate() - 35

SELECT ccy_code, NULL, xrate_date, sterling_xrate
FROM SylvanTrans.dbo.SIADHP_XRate_Hist
WHERE xrate_date >= (CONVERT(int, @.DateValue) - 35 )
ORDER BY xrate_date

now my as you can see in my WHERE CLAUSE i want to calculate what is returned using the GETDATE() stored in my @.DateValue Variable, but the conversion throws a syntax error:

Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'GetDate()' to a column of data type int.

what am idoing wrong? i know it is something simple, unless there is no conversion from varchar to int?, i also tried setting the datatype for my variable as datetime but i got the same sort of error with DATETIME replacing the VARCHAR in the error!!!

thanks

regards

Hi,

why don′t use use the associated function for dealing with datetime like DATEADD ? You can also add negative dateparts to a datetime like
DATEADD(dd,-35,SomeDate)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

thanks for your reply,

yes i did see that function, but i neet to have a seperate table which stores the GetDate() so that in my sotred procs

i can do a select on its location and do things like :

select * from tablename

where @.valueDate - 1 > 5

where @.valueDate has the getDate () from my Applicationsettings Table, if i use the DateAdd() i wont be able to do this you see...

|||I think I did not got your point. COuld you explain this in more detail ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Converting DataType=TEXT to DataType=STR

Hi,

I have an input text file which contains line(s) which can have more than 8000 characters.

I am able to read the lines in this file by specifying that the input column is data type = Text Stream [DT_TEXT] and I can then write the lines to another text file.

But, what I want to do is to use only the right-most 8000 characters. When I try to convert the input column to data type = String [DT_STR] using the Data Conversion transformation then I get the error:-

The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.

I cannot use the string manipulation functions e.g. RIGHT or SUBSTRING in a Derived Column transformation because these functions do not work with DT_TEXT data.
Any Ideas?!

Thanks.You'd only need *1* line of custom code in a Script component transformation to extract your rightmost 8000 characters before moving on to the data type conversion.

-Doug|||Thanks Doug ... Did you mean a Script Component which looks something like:-

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim ascii As Text.Encoding

Dim asciiChars(10) As Char

ascii.GetChars(Row.InputColumn.GetBlobData(1, 1), 0, 1, asciiChars, 0)

Dim asciiString As New String(asciiChars)

MsgBox(asciiString)

End Sub

End Class

When I run the package it fails at the ascii.getchars line with the following error message:-

Error: 0xC0047062 at Data Flow Task, Script Component [106]: System.NullReferenceException: Object reference not set to an instance of an object.

All I am trying to do is extract the first byte and convert it to an ascii char (before I go on to work out the lengths to extract the right-most 8000 chars etc.)

Is this what you had in mind? Or, is there another way to do it?

Many Thanks.

|||This should do what you want. You will need to import System.Text.Encoding.

Dim Start As Integer = Max(0, CInt(Row.InputColumn.Length) - 8001)
Dim Length As Integer = Min(8000, CInt(Row.InputColumn.Length))
Dim asciiString As String = ASCII.GetString(Row.InputColumn.GetBlobData(Start, Length)

|||Just to confirm that Jay's suggestion works. Many Thanks.sqlsql

Converting datatype on column...

What impact will changing the datatype of a column from
smalldatetime to datetime? Currently, I need to allow for record insertion
where the date may exeed year 2079.Eric wrote:
> What impact will changing the datatype of a column from
> smalldatetime to datetime? Currently, I need to allow for record
> insertion where the date may exeed year 2079.
That should be fine. The column will require twice the number of bytes
of storage in order to support datetime (8 bytes total). Newly inserted
values will show additional precision unless you account for this.
create table ABC (MyDate smalldatetime)
insert into dbo.ABC values (getdate())
insert into dbo.ABC values ('2005-01-10T14:22:22')
Select * from dbo.ABC
MyDate
--
2005-09-01 17:45:00
2005-01-10 14:22:00
Alter Table dbo.ABC
ALTER COLUMN MyDate DATETIME
Select * from dbo.ABC
MyDate
--
2005-09-01 17:45:00.000
2005-01-10 14:22:00.000
insert into dbo.ABC values (getdate())
insert into dbo.ABC values ('2005-01-10T14:22:22')
Select * from dbo.ABC
MyDate
--
2005-09-01 17:45:00.000
2005-01-10 14:22:00.000
2005-09-01 17:46:57.827
2005-01-10 14:22:22.000
drop table ABC
David Gugick
Quest Software
www.imceda.com
www.quest.com

Converting DataSet to new SQL Table

Hello Everyone,

I have a dataset that I created from an external database using an ODBC connection. I would like to take that dataset and create a new table in an SQL 2005 database. Can anyone point me in the right direction. The problem that I am having right now is getting the object types etc.

Thank you!!

The first method is to use ExecuteNoneQuery in ADO.NET to create you table, try the links below for code samples. Hope this helps.

http://forums.asp.net/thread/1385209.aspx

http://www.functionx.com/csharp/adonet/Lesson04.htm

Converting database to MS-SQLServer from PostGRESQL

Forgive me if this question is a bit too generic, if it is, feel free to
just not respond.

I have a database which has been running in PostgreSQL for a number of
years at this stage which I want to port into MS SQL server.

It seems that the SQL that Postgre outputs when I do a backup is not
syntactically correct within MS-SQL server.

My question is, does anyone have any documentation on how to convert a
database from the Postgre platform to SQL server? Is it possible using
an ODBC connection to import a database structure including table
definitions, views etc into SQL Server?

Failing this, does anyone have any suggestions on where I might start -
I did attempt to go through the SQL code and modify it to suit SQL
server, but it's about 3,500 lines of code excluding the insert
statements (which themselves are also wrong) and almost every line needs
something changed when comparing SQL syntax from Postgre to MSSQL server

Thanks in advance for any comments/suggestions.

Engada.

--
Posted via a free Usenet account from http://www.teranews.comEngada wrote:

Quote:

Originally Posted by

I have a database which has been running in PostgreSQL for a number of
years at this stage which I want to port into MS SQL server.
>
It seems that the SQL that Postgre outputs when I do a backup is not
syntactically correct within MS-SQL server.

pg_dump has a number of flags that may help, e.g. --inserts

What specific types of syntax errors do you encounter?

Googling (PostgreSQL export) turns up some third-party programs designed
to simplify this type of port.