Converting/Casting strings into Datetime datatype

Hello,

I have a varchar column that inludes dates in the following fomat: 03032007? When I try to cast this to datetime, I keep getting "Arithmetic overflow error converting expression to data type datetime." error. Maybe someone has some ideas how to handle this?

Thanks!

If you had only stored your date values in the ISO format of YYYYMMDD, they would easily cast or convert to datetime. -Or even left in one of the standard date delimiters, such as [ / - ].

However, you (or some unnamed 'other' person) made up a oddball format, and now you will have to 'handle' it to create a 'real' date value everytime you need to use it.

(This assumes your format is MMDDYYYY.)

SELECT cast( stuff( stuff( '03032007', 3, 0, '/' ), 6, 0, '/' ) AS datetime )

-
2007-03-03 00:00:00.000

|||

You know that MS stores sqlagent datetime in to two int columns with the following format, right? (Take a look at the schema for msdb: sysalerts,sysjobhistory, sysjobschedules, sysjobservers, and sysjobsteps.)

date: YYYYMMDD

time: HHMMSS

So, it's not that weird to see the public employs such schema.

|||

But I also notice that MS stores SQL Agent datetime in ISO format (YYYYMMDD).

That little 'standard' makes a lot of difference in cast/convert.

That is behind my even mentioning using a standard ISO format in my response...

Converting XML Datetime to SQL Datetime

I have an XML colmn in SQL 2005 table which looks like:

<abc>

<abcdate>2007-01-31T13:47:27.25-05:00</abcdate>

</abc>

The following query :

SELECT xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(30)') FROM abcTABLE

Returns 2007-01-31T13:47:27.25-05:00

SELECT CAST(xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(30)') AS DATETIME) FROM abcTABLE

Returns

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting datetime from character string.

--

SELECT CAST(xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(19)') AS DATETIME) FROM abcTABLE

Returns 2007-01-31 13:47:27.000

because length of 19 trims the milliseconds

Is it possible to convert this type of XML data type and still acheive accuracy to the milliseconds?

Thanks

Gary

You could use convert function with 126/127 style (for more info http://msdn2.microsoft.com/en-us/library/ms187928.aspx):

declare @.t1 varchar(25)

declare @.t2 varchar(25)

set @.t1 = '2007-01-31T13:47:27.25-05:00'

set @.t2 = '2006-12-12T23:45:12.10'

select convert(datetime,@.t2,126)

select convert(datetime,@.t1,127) --It must works, but don't work on my PC. I think something wrong with my system

|||

Hi Konstantin Kosinsky

It is not working at my machine as well, the "-" is the culprit still.

Style 127 is for ISO8601 with time zone Z: yyyy-mm-ddThh:mm:ss.mmmZ

It does not like the hyphen "-"

|||

Just quote from BOL:

The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

But it does not work. :).

At this moment i could propose substring by last "-" and use 126 or 127 style

|||

The docs are misleading here. If you look carefully at the table above the quote, style 127 only work with Zulu timezone ("Z"). The workaround here is to use XQuery simple type construction.

declare @.t1 xml

set @.t1 = '<abc>2007-01-31T13:47:27.25+05:00</abc>'

select @.t1.value('xs:dateTime(/abc[1])', 'datetime')

-galex

|||

Hi Galex

I tried:

SELECT

Assessmentxml.value('xs:dateTime(/Abc/AbcDate)[1]', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

I got:

Msg 2365, Level 16, State 1, Line 10

XQuery [dbo.Assessment.AssessmentXML.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xs:dateTime'

Then I tried:

SELECT

Assessmentxml.value('(/Abc/AbcDate)[1] CAST AS xs:dateTime', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

and got

Msg 2370, Level 16, State 1, Line 2

XQuery [dbo.Assessment.AssessmentXML.value()]: No more tokens expected at the end of the XQuery expression. Found 'CAST'.

What is the correct syntax to make it work?

Thanks

|||

In SQL Server, the expression you are casting (construction is the same) requires a singleton. Since you are using untyped XML, you need to use a positional predicate.

Please try:

SELECT

Assessmentxml.value('xs:dateTime(/Abc/AbcDate[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

Notice the [1] predicate is inside the construction.

Also, XQuery is case sensitive, so you should be using "cast as" instead of "CAST AS".

-galex

|||

Hi Galex

I get the same error. Please try this:

set ansi_nulls, quoted_identifier, ansi_warnings, ansi_padding ON

declare @.Ax table

( AxID uniqueidentifier not null default(newid())

, AxRefDateTag sysname

, AxXML as cast(

'<Assessment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<AssessmentID>' + cast(AxID as sysname) + '</AssessmentID>'

+ AxRefDateTag + '</Assessment>' as xml)

)

insert @.Ax(AxRefDateTag) values('<AssessmentReferenceDate>2007-01-31T13:47:27.25+05:00</AssessmentReferenceDate>')

SELECT

AxRefDateTag

,AxXml.value('xs:dateTime(/Assessment/AssessmentReferenceDate[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM @.Ax

|||

Gary,

That was my bad. I forgot to put keep the () around the path expression.

xs:dateTime((/Assessment/AssessmentReferenceDate)[1])

Keep in mind, this is also another option, but has different semantics:

xs:dateTime(/Assessment[1]/AssessmentReferenceDate[1])

Sorry for the confusion.

Regards,

Galex

|||This works well :) - thanks a lot Galex|||

Hi Gary

I am also getting the same issue.

Actually straightforward I cannot use the one which you sent ( 'xsateTime(/Assessment/AssessmentReferenceDate[1])', 'datetime')

I cannot say [1], i have to use the variable instead of that, because I am looping through the data.

I am using the below code. It is working fine when the date time is in this format 2007-01-31T13:47:27.25' only.

CAST(CAST(Message.query('data(//SHIPMENT/ISSUE_DT)[sql:variable("@.vcounter")]') AS VARCHAR) AS DATETIME) END,

I tried like this with the format you had given

Message.value('xsateTime((//SHIPMENT/ISSUE_DT)[sql:variable("@.vcounter")])', 'datetime'))

but i am getting an error while compiling

XQuery [Ediinbound.Message.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xsateTime'.

Kindly help me ASAP...

Thanks

Ram

|||

This is how it worked. Galex showed me how to put brackets around the XPATH.

set ansi_nulls, quoted_identifier, ansi_warnings, ansi_padding ON

declare @.Ax table

( AxID uniqueidentifier not null default(newid())

, AxRefDateTag sysname

, AxXML as cast(

'<Assessment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<AssessmentID>' + cast(AxID as sysname) + '</AssessmentID>'

+ AxRefDateTag + '</Assessment>' as xml)

)

insert @.Ax(AxRefDateTag) values('<AssessmentReferenceDate>2007-01-31T13:47:27.25+05:00</AssessmentReferenceDate>')

SELECT * FROM @.Ax

SELECT

AxRefDateTag

,AxXml.value('xsateTime((/Assessment/AssessmentReferenceDate)[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM @.Ax

I haven't tried the variable before, but I think from the above explanation [1] or singleton is a MUST in SQL Server queries.

Converting XML Datetime to SQL Datetime

I have an XML colmn in SQL 2005 table which looks like:

<abc>

<abcdate>2007-01-31T13:47:27.25-05:00</abcdate>

</abc>

The following query :

SELECT xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(30)') FROM abcTABLE

Returns 2007-01-31T13:47:27.25-05:00

SELECT CAST(xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(30)') AS DATETIME) FROM abcTABLE

Returns

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting datetime from character string.

--

SELECT CAST(xmlColumn.value('(/abc/abcDate)[1]', 'nvarchar(19)') AS DATETIME) FROM abcTABLE

Returns 2007-01-31 13:47:27.000

because length of 19 trims the milliseconds

Is it possible to convert this type of XML data type and still acheive accuracy to the milliseconds?

Thanks

Gary

You could use convert function with 126/127 style (for more info http://msdn2.microsoft.com/en-us/library/ms187928.aspx):

declare @.t1 varchar(25)

declare @.t2 varchar(25)

set @.t1 = '2007-01-31T13:47:27.25-05:00'

set @.t2 = '2006-12-12T23:45:12.10'

select convert(datetime,@.t2,126)

select convert(datetime,@.t1,127) --It must works, but don't work on my PC. I think something wrong with my system

|||

Hi Konstantin Kosinsky

It is not working at my machine as well, the "-" is the culprit still.

Style 127 is for ISO8601 with time zone Z: yyyy-mm-ddThh:mm:ss.mmmZ

It does not like the hyphen "-"

|||

Just quote from BOL:

The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

But it does not work. :).

At this moment i could propose substring by last "-" and use 126 or 127 style

|||

The docs are misleading here. If you look carefully at the table above the quote, style 127 only work with Zulu timezone ("Z"). The workaround here is to use XQuery simple type construction.

declare @.t1 xml

set @.t1 = '<abc>2007-01-31T13:47:27.25+05:00</abc>'

select @.t1.value('xs:dateTime(/abc[1])', 'datetime')

-galex

|||

Hi Galex

I tried:

SELECT

Assessmentxml.value('xs:dateTime(/Abc/AbcDate)[1]', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

I got:

Msg 2365, Level 16, State 1, Line 10

XQuery [dbo.Assessment.AssessmentXML.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xs:dateTime'

Then I tried:

SELECT

Assessmentxml.value('(/Abc/AbcDate)[1] CAST AS xs:dateTime', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

and got

Msg 2370, Level 16, State 1, Line 2

XQuery [dbo.Assessment.AssessmentXML.value()]: No more tokens expected at the end of the XQuery expression. Found 'CAST'.

What is the correct syntax to make it work?

Thanks

|||

In SQL Server, the expression you are casting (construction is the same) requires a singleton. Since you are using untyped XML, you need to use a positional predicate.

Please try:

SELECT

Assessmentxml.value('xs:dateTime(/Abc/AbcDate[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM dbo.Assessment

Notice the [1] predicate is inside the construction.

Also, XQuery is case sensitive, so you should be using "cast as" instead of "CAST AS".

-galex

|||

Hi Galex

I get the same error. Please try this:

set ansi_nulls, quoted_identifier, ansi_warnings, ansi_padding ON

declare @.Ax table

( AxID uniqueidentifier not null default(newid())

, AxRefDateTag sysname

, AxXML as cast(

'<Assessment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<AssessmentID>' + cast(AxID as sysname) + '</AssessmentID>'

+ AxRefDateTag + '</Assessment>' as xml)

)

insert @.Ax(AxRefDateTag) values('<AssessmentReferenceDate>2007-01-31T13:47:27.25+05:00</AssessmentReferenceDate>')

SELECT

AxRefDateTag

,AxXml.value('xs:dateTime(/Assessment/AssessmentReferenceDate[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM @.Ax

|||

Gary,

That was my bad. I forgot to put keep the () around the path expression.

xs:dateTime((/Assessment/AssessmentReferenceDate)[1])

Keep in mind, this is also another option, but has different semantics:

xs:dateTime(/Assessment[1]/AssessmentReferenceDate[1])

Sorry for the confusion.

Regards,

Galex

|||This works well :) - thanks a lot Galex|||

Hi Gary

I am also getting the same issue.

Actually straightforward I cannot use the one which you sent ( 'xsateTime(/Assessment/AssessmentReferenceDate[1])', 'datetime')

I cannot say [1], i have to use the variable instead of that, because I am looping through the data.

I am using the below code. It is working fine when the date time is in this format 2007-01-31T13:47:27.25' only.

CAST(CAST(Message.query('data(//SHIPMENT/ISSUE_DT)[sql:variable("@.vcounter")]') AS VARCHAR) AS DATETIME) END,

I tried like this with the format you had given

Message.value('xsateTime((//SHIPMENT/ISSUE_DT)[sql:variable("@.vcounter")])', 'datetime'))

but i am getting an error while compiling

XQuery [Ediinbound.Message.value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xsateTime'.

Kindly help me ASAP...

Thanks

Ram

|||

This is how it worked. Galex showed me how to put brackets around the XPATH.

set ansi_nulls, quoted_identifier, ansi_warnings, ansi_padding ON

declare @.Ax table

( AxID uniqueidentifier not null default(newid())

, AxRefDateTag sysname

, AxXML as cast(

'<Assessment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<AssessmentID>' + cast(AxID as sysname) + '</AssessmentID>'

+ AxRefDateTag + '</Assessment>' as xml)

)

insert @.Ax(AxRefDateTag) values('<AssessmentReferenceDate>2007-01-31T13:47:27.25+05:00</AssessmentReferenceDate>')

SELECT * FROM @.Ax

SELECT

AxRefDateTag

,AxXml.value('xsateTime((/Assessment/AssessmentReferenceDate)[1])', 'datetime') AS [AssessmentExpectedStartDate]

FROM @.Ax

I haven't tried the variable before, but I think from the above explanation [1] or singleton is a MUST in SQL Server queries.

Converting Word Document into RDL Format

Hi,
how can I convert a word document into the RDL Format for Reporting Services.
Is there a tool or an workaround ?
Thanks
StefanOn Jul 19, 11:46 pm, Stefan <Ste...@.discussions.microsoft.com> wrote:
> Hi,
> how can I convert a word document into the RDL Format for Reporting Services.
> Is there a tool or an workaround ?
> Thanks
> Stefan
As far as I know, there is not really anything available. You would
need to investigate into using a custom .NET application to read in
the word document (most likely using System.IO and Streamreader) and
then output the file in the RDL format (using Streamwriter, XMLDoc,
etc). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Is the issue that you want a word document that comes up similar to click on
a report. I do this now. In the designer just do a right mouse click on
reports, add and existing item, then select your word document. Deploy like
a normal report. The only caveat is when you redeploy first go into report
manager and delete it and then deploy. I have found a redeploy does not
stick.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stefan" <Stefan@.discussions.microsoft.com> wrote in message
news:E80FD6D3-A511-4C37-B018-5C872946555D@.microsoft.com...
> Hi,
> how can I convert a word document into the RDL Format for Reporting
> Services.
> Is there a tool or an workaround ?
> Thanks
> Stefan

Converting visual foxpro to sql 2000

I am trying to convert a foxpro database to sql 2000 using the wizard within sql 2000 but it keeps giving me an odbc error when accessing the foxpro database? I am stuck and don't know where to turn next. Any ideas? Thanks
rick,
Please share your error message and the version of Visual FoxPro you are
using. It sounds like a problem in defining your
ODBC data source, but need more info.
Russell Fields
"rick" <anonymous@.discussions.microsoft.com> wrote in message
news:DCEDB7E2-CC4A-4661-8E8F-19169A3D35AD@.microsoft.com...
> I am trying to convert a foxpro database to sql 2000 using the wizard
within sql 2000 but it keeps giving me an odbc error when accessing the
foxpro database? I am stuck and don't know where to turn next. Any ideas?
Thanks
|||In news: DCEDB7E2-CC4A-4661-8E8F-19169A3D35AD@.microsoft.com,
rick <anonymous@.discussions.microsoft.com> wrote:
> I am trying to convert a foxpro database to sql 2000 using the wizard
> within sql 2000 but it keeps giving me an odbc error when accessing
> the foxpro database?
Hi Rick,
FoxPro tables can come in either of two formats. They can be "free" tables,
meaning that each table is independent of the others, or they can be part of
a "database container" which holds metadata and allows additional features
for the tables. If you see a file with a DBC extension then you've got a
database.
Also, tables using any of the new features that were introduced in VFP7 and
VFP8 can only be accessed via the FoxPro and Visual FoxPro OLE DB data
provider.
Both the ODBC driver and OLE DB data provider are downloadable from
http://msdn.microsoft.com/vfoxpro/do...s/default.aspx .
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@.mvps.org www.cindywinegarden.com

Converting Visual FoxPro reports to RDL

I need to convert Microsoft Visual FoxPro reports (FRX files) to RDL format.
FoxPro report has groups.
I think I must create nested List elements to simulate grouping and add
report fields to those list elements.
I have analyzed some possibilities:
1. Generate RDL XML from VFP FRX file using XmlDocument or XML stream.
2. Using dynamic RDL generation class from http://www.gotreportviewer.com
3. Transform XML report defintion generated by FoxPro XMLListener
(http://msdn2.microsoft.com/en-us/library/ms994713(VS.80).aspx) to RDL
4. Use intermediate format like described in
http://www.vfpconversion.com/Blog.aspx?blogid=1b208937-2232-4b48-9a12-8c89f98ce08f&blog=us:Mike&messageid=b842330d-c2e2-4b5e-9d04-3415c8c0a4ad
Any idea or sample of FRX to RDL conversion ?
Andrus.Hi Andrus,
I see there is a VFP to RDL conversion tool at
http://www.vfpconversion.com/Vfp2NetReports.aspx .
--
Cindy Winegarden
cindy@.cindywinegarden.com
VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
"Andrus" <kobruleht2@.hot.ee> wrote in message
news:%23SyWacFVHHA.3948@.TK2MSFTNGP05.phx.gbl...
>I need to convert Microsoft Visual FoxPro reports (FRX files) to RDL
>format.
> FoxPro report has groups.
> I think I must create nested List elements to simulate grouping and add
> report fields to those list elements.
> I have analyzed some possibilities:
> 1. Generate RDL XML from VFP FRX file using XmlDocument or XML stream.
> 2. Using dynamic RDL generation class from http://www.gotreportviewer.com
> 3. Transform XML report defintion generated by FoxPro XMLListener
> (http://msdn2.microsoft.com/en-us/library/ms994713(VS.80).aspx) to RDL
> 4. Use intermediate format like described in
> http://www.vfpconversion.com/Blog.aspx?blogid=1b208937-2232-4b48-9a12-8c89f98ce08f&blog=us:Mike&messageid=b842330d-c2e2-4b5e-9d04-3415c8c0a4ad
>
> Any idea or sample of FRX to RDL conversion ?
>
> Andrus.
>
>

converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO
Why are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegrou ps.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>

converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GOWhy are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
--
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegroups.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>

converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert
(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GOWhy are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegroups.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), conve
rt(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>

Converting varchar to Money

I have a a Case statement which sometimes fails when converting a Varchar
column which contains numeric values to Money. I can understand why it fail
s
when "1.05E+07" is passed in, but other values appear to fail also. I have
not located the other offending values yet (500,000 rows to sift through)
but converting them to Float first avoids the errors. Any ideas?
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
"Server: Msg 235, Level 16, State 1, Line 1
Cannot convert a char value to money. The char value has incorrect syntax."
However the following code, which converts to Float, and then to Money, does
not fail.
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
CONVERT(FLOAT,c1_PriorSalePrice) )(a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
the contents can be converted to any numeric type. See
http://www.aspfaq.com/2390 for the long-winded version of this.
(b) if using convert(money, convert(float())) works, then what is wrong with
using that?
(c) STOP STORING NUMERIC VALUES AS STRINGS!
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>I have a a Case statement which sometimes fails when converting a Varchar
> column which contains numeric values to Money. I can understand why it
> fails
> when "1.05E+07" is passed in, but other values appear to fail also. I
> have
> not located the other offending values yet (500,000 rows to sift through)
> but converting them to Float first avoids the errors. Any ideas?
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
> "Server: Msg 235, Level 16, State 1, Line 1
> Cannot convert a char value to money. The char value has incorrect
> syntax."
> However the following code, which converts to Float, and then to Money,
> does
> not fail.
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
> CONVERT(FLOAT,c1_PriorSalePrice) )
>
>|||Brother AAron,
The data in question is being provided in bulk by an outside vendor, so I
have no choice in how the data is provided or in what format. I have never
seen this situation before and it may have implications for other procedures
.
I will go read the link you provided.
Thanks
Michael
"Aaron Bertrand [SQL Server MVP]" wrote:

> (a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
> the contents can be converted to any numeric type. See
> http://www.aspfaq.com/2390 for the long-winded version of this.
> (b) if using convert(money, convert(float())) works, then what is wrong wi
th
> using that?
> (c) STOP STORING NUMERIC VALUES AS STRINGS!
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>
>

Converting varchar to int (or numeric)

In one of my stored procedures, a varchar is input that is assumed to be an int, and I need to validate that it is an int before I CAST it as an int. I'm currently using ISNUMERIC() to eliminate non-numeric values, but how do I recover gracefully if values such as '7.5e3', '$334', or '45.9943' sneak through? They all pass the ISNUMERIC() test, but cause errors when being cast to int. Similar problems if casting to numeric, etc.You can use the case statement - for example:

declare @.test varchar(20)
select @.test='123.3'
select case when charindex('.',@.test) > 0 then cast(@.test as decimal(10,2))
when charindex('e',@.test) > 0 then cast...
else cast(@.test as int) end|||Thanks. That should work. On retrospect, it's obvious enough I should have come up with that myself!

converting varchar to int

Syntax error converting the varchar value '3.1.7.4.3.9.' to a column of data type int...

how to overcome this problem ..

Hello Raj,

What should '3.1.7.4.3.9.' be, ayou wanting to remove the .'s to have it be 317439? Try this:

=cInt(Replace(Fields!Field1.Value, ".", ""))

Jarret

|||when i run the query it throws the error... i have to handle the error in the data tab itself... can you help me how to find the exact field in which the irregular datatype is located ...is there any way to track down the issue...|||

It is clearly an SQL error which cannot be solved in SSRS using expressions.

I'm assuming that your dataset is formed by a stored procedure, not plain SQL text.

The error is because you are trying to put an invalid integer value into a table (temporary table or user table) column which is of INT datatype. If possible, get the SQL text of the stored procedure and check all insert/update statements and all the columns of INT datatype involved in it. Also check If there is an implicit conversion using CONVERT or CAST function.

Shyam

|||can you tell me the problem is with varchar or datetime... because i didnt find any varchar like '3.1.7.4.3.9.'|||

Yes, it is definitely a varchar. You may have to look out for columns in the table that is being used as source to insert/update the value in the table which has a corresponding column of INT datatype.

Shyam

|||thank you shyam .. i will check it out...

Converting varchar to int

Hi,
I have a varchar(255) column where I may have data like this:
43294430949
adkk3400
1056
ff1d
10
302
15000043
I would like to write a SQL query that returns values between
10 and 500 (numeric)
If I just do this:
Select * from table where column between '10' and '500'
I would also get 15000043. That's incorrect
I also tried doing this:
Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
column) <=500
but it fails when I have characters in the column.
Do you guys know how I can do that?
ThanksFirst you need a solid function that can determine if the value is numeric.
For
that go here: http://www.aspfaq.com/show.asp?id=2390.
Select *
From #Test As T
Where IsNumeric(T.Data) = 1
And dbo.IsReallyInteger(T.Data) = 1
And Cast(T.Data As BigInt) Between 10 And 50
Why the two checks? If you only use IsReallyNumeric, SQL cannot determine wh
at
that function actually does and more specifically, whether it filters for va
lues
that will be castable to BigInt. Then why use IsReallyInteger in the first
place? The reason is that IsNumeric is faulty in its determination of numeri
c
values. Characters like "$" and "d' and other odd characters can return true
for
a IsNumeric.
HTH
Thomas
"Star" <noemail@.noemail.com> wrote in message
news:%23RPTMgtjFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int, colum
n)
> <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks|||Try,
Select *
from table
where
case
when c1 like '[0-9][0-9]' or c1 like '[0-9][0-9][0-9]' then cast(c1 as int)
else null
end between 10 and 500
AMB
"Star" wrote:

> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
> column) <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks
>|||You can write your query with a WHERE clause like:
WHERE CASE WHEN ISNUMERIC( col ) = 1
THEN CAST( col AS BIGINT )
END BETWEEN 10 AND 500 ;
Note that there are certain considerations with ISNUMERIC with characters
like e, d, $ etc. in which case you'd have to use PATINDEX to make sure the
values are numerically compatible. Also, is the converted value is beyond
the value limitations of INT or BIGINT or even DECIMAL values, then you'll
get an overflow error.
Anith|||We need more information:
1) How should the values such as 'adkk3400' be considered? Do you want this
to be 3400 numeric, or do you want to ignore rows with nun-numeric content?
2) For the numeric values, the 'int' data type would not work for your first
value '43294430949' - it is outside the rane of acceptable values. Would
'bigint' be OK?
So, for example, if you are ignoring rows with alphabetical characters, and
your data only contained numbers and letters, you could try something like
this (untested pseudo-code, since you did not provide DDL, sample data, or
expected results [http://www.aspfaq.com/etiquette.asp?id=5006]):
SELECT <Final Column List>
(SELECT UglyDataColumn, <Other Column List>
FROM MakeBelieveTable
WHERE UglyDataColumn NOT LIKE '%[A-Za-z]%') NUMONLY
WHERE CAST(NUMONLY.UglyDataColumn AS bigint) BETWEEN 10 AND 500
If this is not what you are looking for, you will need to provide better
specifications.
P.S. Out of curiosity, what type of information exactly is this
'UglyDataColumn' holding? That is a seriously bad assortment of values, and
I am guessing there are either some missing constraints on that column, or
the design is fundamentally flawed.
"Star" <noemail@.noemail.com> wrote in message
news:%23RPTMgtjFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a varchar(255) column where I may have data like this:
> 43294430949
> adkk3400
> 1056
> ff1d
> 10
> 302
> 15000043
> I would like to write a SQL query that returns values between
> 10 and 500 (numeric)
> If I just do this:
> Select * from table where column between '10' and '500'
> I would also get 15000043. That's incorrect
> I also tried doing this:
> Select * from table where CONVERT(int, column) >=10 and CONVERT(int,
> column) <=500
> but it fails when I have characters in the column.
> Do you guys know how I can do that?
> Thanks

Converting varchar to decimal

I'm have a varchar(50) field that I want to convert to decimal. These are
two samples:
+000000063451473.38
-000000038818201.42
Logically I want to:
-remove the + sign and leave the - sign
-remove any leading 0s
My desired outcome is:
63451473.38
-38818201.42
How can I reliably do this. Thanks to anyone who could help.Actually this should be down in the frontend after dataretrieval, because
string functions are not that really fast in SQL Server (2000).
DECLARE @.Number2Convert Varchar(50)
SET @.Number2Convert = '-000000063451473.38'
SELECT (CASE LEFT(@.Number2Convert,1) WHEN '+' THEN '' ELSE '-' END) +
CONVERT(VARCHAR(50),CONVERT(DECIMAL(34,2
),RIGHT(@.Number2Convert,LEN(@.Number2
Convert)-1)))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Terri" <terri@.cybernets.com> schrieb im Newsbeitrag
news:d6389k$9ht$1@.reader2.nmix.net...
> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>|||something like this should do:
select str(your_col,18,2)
from tb
-oj
"Terri" <terri@.cybernets.com> wrote in message
news:d6389k$9ht$1@.reader2.nmix.net...
> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>|||Thanks Jens, I need to calculate with this data before it even will reach
the front end and it will be a once a day process with minimal records so
performance is not critical here.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eN3i9fAWFHA.2928@.TK2MSFTNGP10.phx.gbl...
> Actually this should be down in the frontend after dataretrieval, because
> string functions are not that really fast in SQL Server (2000).
> DECLARE @.Number2Convert Varchar(50)
> SET @.Number2Convert = '-000000063451473.38'
> SELECT (CASE LEFT(@.Number2Convert,1) WHEN '+' THEN '' ELSE '-' END) +
>
CONVERT(VARCHAR(50),CONVERT(DECIMAL(34,2
),RIGHT(@.Number2Convert,LEN(@.Number2
Convert)-1)))
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Terri" <terri@.cybernets.com> schrieb im Newsbeitrag
> news:d6389k$9ht$1@.reader2.nmix.net...
are
>|||Terri
Use Cast Function.. Example
Select Cast('+000000063451473.38' as Decimal(38,3))
Charly
"Terri" wrote:

> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>

Converting varchar to DateTime

Hi,

I wanted to convert the varchar to date time and here is what i am doing

DECLARE @.dt VARCHAR(20)

SET @.dt = '20070111' -- YYYYMMDD format

select CONVERT(datetime, @.dt, 120)

This works perfectly fine and the result would be- 2007-01-11 00:00:00.000

But if i changed my datetime format from YYYYMMDD to YYYYMMDDHHMM then this is failing and throwing

"Conversion failed when converting datetime from character string."

Can any one please let me know how do we achieve this?

~Mohan

YYYYMMDDHHMM is not recognized as a valid datetime string. For example, YYYMMDD HH:MM works.

|||

This is the Convert sintax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The first parameter data_type is the required convertion type, of course including the length if required. The second parameter is the expression to convert, and to endding the last parameter is used to define the style in that you are passing the "expression" parameter.

In your code, the style parameter says 120 that corresponds to a ODBC Canonical format in this format: yyyy-mm-dd hh:mis.

Then, ?Does because a string with the format yyyymmdd can be converted to string?:

The YYYYMMDD is widely recognized ODBC String Format that can be used to convert a string to a SQL Server DateTime format. When you use this format, the convert function ignores the last parameter, because it's a standard format. Instead, YYYMMDDHHMM is not a SQL Server recognized format, by this you can not use this.

I recommend to you, to pass strings in the yyyy-mm-dd hh:mis format to be recognized by the CONVERT or CAST functions in SQL server.

Converting varchar to datetime

I have a sql server 2000 db that has a carchar field that is currently storying date data in the following format:

June 16

Can I convert that from varchar to datetime or smalldatetime without loss of data? And, if so, what adjustments do I have to make in my ado code to continue to allow my clients to add data. Currently they add date data by selecting a month from one dropdown and the day from another.

Thanks!Create another column and use CONVERT function to conver the date and store. Then delete the old column.

converting varchar to date from asp.net to sort

for some odd reason our other programmer used varchar datatype to store dates. he claims it gives him more control. now i am trying to sort it based on date. so i create a procedure:

CREATE PROCEDURE GetAllWeekEnding

AS
Select convert(datetime, we) as we2 FROM tblArchive order by we2
GO

if i use the convert function in the procedure, i'll get an error msg when i run the code. this is the code i am using.

Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringSQL"))
Dim MyCommand As SqlCommand

MyCommand = New SqlCommand("GetAllWeekEnding", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
MyConnection.Open()

Dim mydr As SqlDataReader = MyCommand.ExecuteReader()
While mydr.Read()
DropDownList1.Items.Add(mydr("we2"))
End While

mydr.Close()
MyConnection.Close()

the error message is: No accessible overloaded 'ListItemCollection.Add' can be called without a narrowing conversion

any ideas?the .Add method expects a ListItem or a String. Try:

DropDownList1.Items.Add(mydr("we2").ToString())

Is there any reason you are not just binding to myDr?|||<<<<Is there any reason you are not just binding to myDr? >>>
yes, when i bind to the listbox, i am getting date and time.

i can get rid of the time by using:

While mydr.Read()
DropDownList1.Items.Add(Format(mydr("we2"), "M/dd/yyyy").ToString)
End While|||You could easily use Convert to convert the datetime to a date only (of type varchar()) in SQL Server in the SP if the only purpose of this SP is to feed this DDL.|||<<<<<You could easily use Convert to convert the datetime to a date only (of type varchar()) in SQL Server in the SP if the only purpose of this SP is to feed this DDL.
>>>>
the problem is that the date is already in varchar datatype in the database. on the aspx page, i want to load all the dates and i want it sorted by date. the only way for me to show the dates on the aspx page sorted by date is to convert the date from varchar to datetime, like this:

CREATE PROCEDURE GetAllWeekEnding

AS
Select Distinct convert(datetime, we) as we2 FROM tblArchive order by we2 desc
GO

once varchar is converted to datetime, it not only shows just the date but also time so i cannot bind it to a dropDownList.

if i just bind the orginal varchar on the DropDownList witout converting it to DateTime, it's not going to be sorted because you cannot sort a varchar like you sort a datetime|||CREATE PROCEDURE GetAllWeekEnding

AS
Select Distinct CONVERT(nvarchar(20),convert(datetime, we),101) as we2 FROM tblArchive order by convert(datetime, we) desc
GO

There is no rule that the representation you SELECT needs to be the same as you use to ORDER BY.

Converting varchar to date

Hi
Not being a coder I have no idea how to do this and existing posts dont
really help.
I have a table called 'Incoming' and a column called dateofbirth. The
format of the column is varchar. The values in the dateof birth column
are 01012000. When searching for particular dates I'm getting crap
results (because the query is crap too). I reckon I need to convert the
values in the column to dates so that my query can work.
All help gratefully accepted.
TIAHi
declare @.dt varchar(20)
set @.dt='01012000'
select
convert(datetime,substring(@.dt,5,4)+substring(@.dt,1,2)+substring(@.dt,3,2),112)
"jjaggii" <richardwsmit@.gmail.com> wrote in message
news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA
>|||Many thanks for that Uri :)
Uri Dimant wrote:
> Hi
> declare @.dt varchar(20)
> set @.dt='01012000'
> select
> convert(datetime,substring(@.dt,5,4)+substring(@.dt,1,2)+substring(@.dt,3,2),112)
>
>
> "jjaggii" <richardwsmit@.gmail.com> wrote in message
> news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...
> > Hi
> > Not being a coder I have no idea how to do this and existing posts dont
> > really help.
> >
> > I have a table called 'Incoming' and a column called dateofbirth. The
> > format of the column is varchar. The values in the dateof birth column
> > are 01012000. When searching for particular dates I'm getting crap
> > results (because the query is crap too). I reckon I need to convert the
> > values in the column to dates so that my query can work.
> > All help gratefully accepted.
> > TIA
> >|||SELECT DATEOFBIRTH = CAST( SUBSTRING(dateofbirth,5,4) + '-' +
SUBSTRING(dateofbirth,1,2) + '-' +
SUBSTRING(dateofbirth,3,2)
AS DATETIME
)
FROM Incoming
M A Srinivas
jjaggii wrote:
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA

Converting varchar to date

Hi
Not being a coder I have no idea how to do this and existing posts dont
really help.
I have a table called 'Incoming' and a column called dateofbirth. The
format of the column is varchar. The values in the dateof birth column
are 01012000. When searching for particular dates I'm getting crap
results (because the query is crap too). I reckon I need to convert the
values in the column to dates so that my query can work.
All help gratefully accepted.
TIAHi
declare @.dt varchar(20)
set @.dt='01012000'
select
convert(datetime,substring(@.dt,5,4)+subs
tring(@.dt,1,2)+substring(@.dt,3,2),11
2)
"jjaggii" <richardwsmit@.gmail.com> wrote in message
news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA
>|||Many thanks for that Uri
Uri Dimant wrote:
[vbcol=seagreen]
> Hi
> declare @.dt varchar(20)
> set @.dt='01012000'
> select
> convert(datetime,substring(@.dt,5,4)+subs
tring(@.dt,1,2)+substring(@.dt,3,2),
112)
>
>
> "jjaggii" <richardwsmit@.gmail.com> wrote in message
> news:1152864481.073991.8460@.75g2000cwc.googlegroups.com...|||SELECT DATEOFBIRTH = CAST( SUBSTRING(dateofbirth,5,4) + '-' +
SUBSTRING(dateofbirth,1,2) + '-' +
SUBSTRING(dateofbirth,3,2)
AS DATETIME
)
FROM Incoming
M A Srinivas
jjaggii wrote:
> Hi
> Not being a coder I have no idea how to do this and existing posts dont
> really help.
> I have a table called 'Incoming' and a column called dateofbirth. The
> format of the column is varchar. The values in the dateof birth column
> are 01012000. When searching for particular dates I'm getting crap
> results (because the query is crap too). I reckon I need to convert the
> values in the column to dates so that my query can work.
> All help gratefully accepted.
> TIA

Converting varbinary to varchar

I have a password field which is of varbinary. Since its a varbinary Icannot see the password in the database I only see hexadecimal values.Now my question is that how can I convert those hexadecimal values tostring or varchar so I can read the password.
any ideas ??

Not sure if you seen this or not, but it talks about ways to convert binary fields.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Nick