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 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 to datetime...

Hello..
I am having a difficult time trying to get SQL Server to convert the
following date:
22-08-2004 00:00:00
I have tried to convert and cast and I get the following error message:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
Any help would be most appreciative.
Thank you,
Brett
P.S.
I am using SQL Server 2000How are you doing this convert ? This works for me:
Select convert(varchar(20),'22-08-2004 00:00:00',102)
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Brett Davis" <bdavis123@.cox.net> schrieb im Newsbeitrag
news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> Hello..
> I am having a difficult time trying to get SQL Server to convert the
> following date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>|||You need to tell SQL Server more about the pattern used for the datetime. Tr
y
this:
Select Convert(DateTime, '22-08-2004 00:00:00', 105)
Without specifying the date format, I believe that SQL uses the format from
the
database's collation or perhaps the server's regional settings.
HTH
Thomas
"Brett Davis" <bdavis123@.cox.net> wrote in message
news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
> Hello..
> I am having a difficult time trying to get SQL Server to convert the follo
wing
> date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
an
> out-of-range datetime value.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>|||Use styles 112 or 126. See CONVERT in BOL.
Example:
select cast('20040822' as datetime)
select cast('2004-08-22T00:00:00.000' as datetime)
go
AMB
"Brett Davis" wrote:

> Hello..
> I am having a difficult time trying to get SQL Server to convert the
> following date:
> 22-08-2004 00:00:00
> I have tried to convert and cast and I get the following error message:
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
> Any help would be most appreciative.
> Thank you,
> Brett
> P.S.
> I am using SQL Server 2000
>
>|||Thomas,
You use the style parameter when converting from datetime to varchar / char
and not the opposite. All these statements will give same result.
select convert(datetime, '20050427', 105)
select convert(datetime, '20050427', 112)
select convert(datetime, '20050427', 126)
select convert(datetime, '20050427')
AMB
"Thomas" wrote:

> You need to tell SQL Server more about the pattern used for the datetime.
Try
> this:
> Select Convert(DateTime, '22-08-2004 00:00:00', 105)
> Without specifying the date format, I believe that SQL uses the format fro
m the
> database's collation or perhaps the server's regional settings.
>
> HTH
>
> Thomas
>
> "Brett Davis" <bdavis123@.cox.net> wrote in message
> news:eHEz6r1SFHA.2432@.TK2MSFTNGP12.phx.gbl...
>
>|||I think we might be saying the same thing. By passing the style parameter, y
ou
are giving SQL information about the format of the string.

>All these statements will give same result.
> select convert(datetime, '20050427', 105)
> select convert(datetime, '20050427', 112)
> select convert(datetime, '20050427', 126)
> select convert(datetime, '20050427')
But these do not:
1. select convert(datetime, '22-08-2004 00:00:00', 105)
2. select convert(datetime, '22-08-2004 00:00:00', 112)
3. select convert(datetime, '22-08-2004 00:00:00', 126)
4. select convert(datetime, '22-08-2004 00:00:00',)
Only the first one successfully parses the string into a datetime. The other
s
fail with a conversion error because the system thinks that the first digits
are
the month instead of the day.
I'll grant you that passing the ISO format (yyyymmdd) would be the best way
to
avoid all of these problems.
Thomas|||You are right.
AMB
"Thomas" wrote:

> I think we might be saying the same thing. By passing the style parameter,
you
> are giving SQL information about the format of the string.
>
> But these do not:
> 1. select convert(datetime, '22-08-2004 00:00:00', 105)
> 2. select convert(datetime, '22-08-2004 00:00:00', 112)
> 3. select convert(datetime, '22-08-2004 00:00:00', 126)
> 4. select convert(datetime, '22-08-2004 00:00:00',)
> Only the first one successfully parses the string into a datetime. The oth
ers
> fail with a conversion error because the system thinks that the first digi
ts are
> the month instead of the day.
> I'll grant you that passing the ISO format (yyyymmdd) would be the best wa
y to
> avoid all of these problems.
>
> Thomas
>
>

converting to date only

I have a function on a MSSQL 2000 db like the following:

create function GetDateOnly (@.pInputDate datetime)
returns datetime
as
begin
return cast(convert(varchar(10), @.pInputDate, 111) as datetime)
end

which returns the date with the time all zeros ( '2006-05-09 00:00:00' ). I tried to implement this same function on an MSSQL 7 server and I get errors.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.

How can I code something similar in version 7? I'm assuming it's a version difference that is causing my problems.

Thanks,
Randy7.0 did not support user-defined functions.

You could write a stored proc with an OUTPUT parameter to do this, but you could not include it in a SELECT statement as you can with a function.

You will probably need to code the logic each time you need it. But I suggest you use this formula instead, which is more efficient than the CONVERT method you are using:

create function GetDateOnly (@.pInputDate datetime)
returns datetime
as
begin
return dateadd(day, datediff(day, 0, getdate()), 0)
end|||thanks alot for the advise. I thought about stored procedure, but then like you mentioned I can't use it in a SELECT which was my whole goal. Back to the drawing board :-)

Randy

Converting text to table with DTS

I am using DTS to import text to a table. This mostly works except for the 'DateTime' datatype where I always run into either 'Arithmetic overflow' or 'Unable to convert varchar to DateTime format.' I can convert same data without incident in Access.

Has anyone run into this problem?Import the problem dates into a VARCHAR(30) column using DTS. Then mangle them at will using the SQL functions to make them easier to digest!

-PatP|||I'm kind of new to SQL...do you mean using functions like CAST and CONVERT in custom DTS packages?|||whats the format of date in the text file? use IsDate() function in the transformation ActiveX to check for validity of date
-Rohit|||Import the problem dates into a VARCHAR(30) column using DTS. Then mangle them at will using the SQL functions to make them easier to digest!

-PatP

Import everything in to an existing tables as varchar...the perform your audits...

and learn bcp......

Is the file fixed width or delimited?

Mangle...lol..

Converting string to datetime

Hi,

I'm in a bit of a tricky situation. I'm upgrading and existing application (VB.NET 05 and sql server 2000). One of the tables has field having datatype varchar(20) but actually storing dates. From different parts of the application the datetime values are saved basically in three formats.

1. 2004/11/26 00:00:00

2. Nov 25 2004 12:00AM
3. 24/11/2004

The problem is I need to run a datediff to calculate a date difference. I can't get my head around to convert all the three types to one data type using a sql.

Really appritiate if some one can help me out on this.

Regards,

Vije

If your data is exactly the three formats you provided, then this approach should work for you:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
MyDate nvarchar(30),
MyNewDate datetime
)

INSERT INTO @.MyTable ( MyDate ) VALUES ( '2004/11/26 00:00:00' )
INSERT INTO @.MyTable ( MyDate ) VALUES ( 'Nov 25 2004 12:00AM' )
INSERT INTO @.MyTable ( MyDate ) VALUES ( '24/11/2004' )

UPDATE @.MyTable
SET MyNewDate = CASE isdate( MyDate )
WHEN 1 THEN cast( MyDate AS datetime )
ELSE convert( datetime, MyDate, 103 )
END
SELECT *
FROM @.MyTable

RowID MyDate MyNewDate
-- - -
1 2004/11/26 00:00:00 2004-11-26 00:00:00.000
2 Nov 25 2004 12:00AM 2004-11-25 00:00:00.000
3 24/11/2004 2004-11-24 00:00:00.000

|||

Vije:

You might be able to get by with something like this:

Code Snippet

select theDt,
case when charindex(':', theDt) <> 0
and isDate(theDt) = 1
then convert(datetime, theDt)
when charindex(':', theDt) = 0
and isDate(theDt) = 0
and isDate
( parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
) = 1
then parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
end as convertedDT
from ( select '2004/11/26 00:00:00' as theDT union all
select 'Nov 25 2004 12:00AM' union all
select '24/11/2004' union all
select 'invalid'
) a
/*
where isDate(theDt) = 1
or ( charindex(':', theDt) = 0
and isDate(theDt) = 0
and isDate
( parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
) = 1
)
*/

/*
theDt convertedDT
-
2004/11/26 00:00:00 2004-11-26 00:00:00.000
Nov 25 2004 12:00AM 2004-11-25 00:00:00.000
24/11/2004 2004-11-24 00:00:00.000
invalid NULL
*/

If you don't want the invalid data to appear just uncomment the WHERE clause (that is shown in red).

As an aside, you ought to consider converting this column to a DATETIME data type. These kinds of problems will only grow until you resolve the real problem here -- which is a design problem.

|||

In one single update statement you can't do it..

But the following batch may help you.

Code Snippet

Create Table #datedata (

[Dates] Varchar(20)

);

Insert Into #datedata Values('2004/11/26 00:00:00');

Insert Into #datedata Values('Nov 25 2004 12:00AM');

Insert Into #datedata Values('24/11/2004');

Set DateFormat DMY

Update

#datedata

Set

Dates = Convert(varchar,cast(Dates as datetime) , 120) -- Finaly converted as ANSI Format

Where

Isdate(Dates) = 1

Set DateFormat YMD

Update

#datedata

Set

Dates = Convert(varchar,cast(Dates as datetime) ,120) -- Finaly converted as ANSI Format

Where

Isdate(Dates) = 1

Select * From #dateData

|||

Woooh .. We all 3 given unique and different solution.... . I like Arnie's solution, Kent you are really hard worker..

Converting string to datetime

Hi

What's wrong about

SELECT SUM(CASE WHEN [PO Date] BETWEEN CONVERT(Datetime, @.FY + '/04/01')
AND CONVERT(Datetime, @.FY -1 + '/03/31') THEN Quantity ELSE 0 END) AS Expr1,
[Item No_]
FROM table A

It looks like you are trying to use the aggregate function sum() and the [item no_] column is not in an aggregate function or group by clause

|||

here it is,

Code Snippet

SELECT

SUM(CASE WHEN [PO Date] BETWEEN CONVERT(Datetime, @.FY + '/04/01')

AND CONVERT(Datetime, @.FY -1 + '/03/31') THEN Quantity ELSE 0 END) AS Expr1,

[Item No_]

FROM table A

Group By

[Item No_]

|||

Still can't! It show error massage" wrong parameter".

|||

Please provide the entire procedure code and the error message in its entirity.|||

Hi

I use this query on reporting service. After perview will show "Conversion failed when converting to varchar value"/04/01" to date type int.

|||

You are attempting to concatenate the varchar value '/04/01' to the integer value @.FY.

You need to first cast the variable @.FY as a char()/varchar().

However, this could be avoided IF you used an actual datetime value for the fiscal year instead an integer value. This may be an excellent opportunity to finally explore using the 'Calendar' table you've most likely heard about before. I recommend reviewing this article to see if there is utility in the concept for you.

(From this example, I suspect that there are other places in your code where you are attempting to handle 'date' issues in 'creative' and non-functional methods.)

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

|||

Hai,

As Arnie said, you need to first convert @.FY value to varchar type.

And, also, in your query, first date in Between is > second date which always returns the Expr1 to 0.

you can try this, sample, query:

DECLARE @.FY int

DECLARE @.TableA Table([PODate] datetime, [ItemNo_] int, Quantity int)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/01',1, 200)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/03',1, 100)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/05',1, 50)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/10',2, 30)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/22',2, 40)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/04/01',3, 60)

SET @.FY = 2007

SELECT

SUM(CASE WHEN [PODate] BETWEEN CONVERT(Datetime, CONVERT(VarChar(4),(@.FY-1)) + '/03/31') AND

CONVERT(Datetime, CONVERT(VarChar(4),@.FY) + '/04/01')

THEN Quantity ELSE 0

END) AS Expr1,

[ItemNo_]

FROM @.TableA

GROUP BY [ItemNo_]

Hope this will work, Please clarify If'm wrong.

Regards,

Y.Kiran Kumar.

converting string to date

Hello,

I try to convert a pseudo datetime string into a date. In Oracle I can do
to_date( MyDate, 'yyyymmddhh24miss' ); how I can do this with MS SQL ?

thanks and regards
MarkSee CONVERT() in Books Online - it supports a number of different
formats, although not the exact one you've mentioned above. You might
need to look at modifying the string before using CONVERT() - see
"String Functions" in Books Online, or you could also consider doing
that in your client application.

Simon|||On Fri, 12 Aug 2005 14:17:57 +0200, Mark wrote:

>Hello,
>I try to convert a pseudo datetime string into a date. In Oracle I can do
>to_date( MyDate, 'yyyymmddhh24miss' ); how I can do this with MS SQL ?
>thanks and regards
>Mark

Hi Mark,

The easiest way is to use string functions to convert your date to the
unambiguous ISO-standard yyyy-mm-ddThh:mm:ss format, then cast to
datetime:

DECLARE @.DateString char(14)
SET @.DateString = '20050812204332'
SELECT CAST(SUBSTRING(@.DateString, 1, 4) + '-'
+ SUBSTRING(@.DateString, 5, 2) + '-'
+ SUBSTRING(@.DateString, 7, 2) + 'T'
+ SUBSTRING(@.DateString, 9, 2) + ':'
+ SUBSTRING(@.DateString, 11, 2) + ':'
+ SUBSTRING(@.DateString, 13, 2) AS datetime)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Converting Sql7 to 2000 version

Can anyone tell where i can find any guide to prevent big error!! My problems
are date and string. When using datetime fields, before i used [yyyy-mm-dd]
format, now its no longer available!!! How cai i do?!!?! Must i covert all
stored procedure or code? Wich problems can i have with the new string limit?
Thanx all!!
Marko
Hi
Have you looked at CAST and CONVERT. Formatting a string as you want is is
still there.
How are you trying to use the data?
Regards
Mike
"Marko" wrote:

> Can anyone tell where i can find any guide to prevent big error!! My problems
> are date and string. When using datetime fields, before i used [yyyy-mm-dd]
> format, now its no longer available!!! How cai i do?!!?! Must i covert all
> stored procedure or code? Wich problems can i have with the new string limit?
> Thanx all!!
> Marko
|||Thanx, but all my Stored Procedure i use CONVERT to obtain 120th format,
yyyy-mm-dd.
With 2000 version this is not good, and i must correct all the procedure
with yyyymmdd: is it correct?
This is a problem for me because i must to correct many procedures and then
i must tu correct all the procedure call from ado Connection; for example
cn.execute ("MyProc 'yyyy-mm-dd'") must become cn.execute ("MyProc
'yyyymmdd'")
Can you help me?
Again , Thanx !!
Mark
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Have you looked at CAST and CONVERT. Formatting a string as you want is is
> still there.
> How are you trying to use the data?
> Regards
> Mike
> "Marko" wrote:

Converting Sql7 to 2000 version

Can anyone tell where i can find any guide to prevent big error!! My problem
s
are date and string. When using datetime fields, before i used [yyyy-mm-
dd]
format, now its no longer available!!! How cai i do'!!?! Must i covert all
stored procedure or code? Wich problems can i have with the new string limit
?
Thanx all!!
MarkoHi
Have you looked at CAST and CONVERT. Formatting a string as you want is is
still there.
How are you trying to use the data?
Regards
Mike
"Marko" wrote:

> Can anyone tell where i can find any guide to prevent big error!! My probl
ems
> are date and string. When using datetime fields, before i used [yyyy-m
m-dd]
> format, now its no longer available!!! How cai i do'!!?! Must i covert a
ll
> stored procedure or code? Wich problems can i have with the new string lim
it?
> Thanx all!!
> Marko|||Thanx, but all my Stored Procedure i use CONVERT to obtain 120th format,
yyyy-mm-dd.
With 2000 version this is not good, and i must correct all the procedure
with yyyymmdd: is it correct'
This is a problem for me because i must to correct many procedures and then
i must tu correct all the procedure call from ado Connection; for example
cn.execute ("MyProc 'yyyy-mm-dd'") must become cn.execute ("MyProc
'yyyymmdd'")
Can you help me?
Again , Thanx !!
Mark
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Have you looked at CAST and CONVERT. Formatting a string as you want is is
> still there.
> How are you trying to use the data?
> Regards
> Mike
> "Marko" wrote:
>

Converting Sql7 to 2000 version

Can anyone tell where i can find any guide to prevent big error!! My problems
are date and string. When using datetime fields, before i used [yyyy-mm-dd]
format, now its no longer available!!! How cai i do'!!?! Must i covert all
stored procedure or code? Wich problems can i have with the new string limit?
Thanx all!!
MarkoHi
Have you looked at CAST and CONVERT. Formatting a string as you want is is
still there.
How are you trying to use the data?
Regards
Mike
"Marko" wrote:
> Can anyone tell where i can find any guide to prevent big error!! My problems
> are date and string. When using datetime fields, before i used [yyyy-mm-dd]
> format, now its no longer available!!! How cai i do'!!?! Must i covert all
> stored procedure or code? Wich problems can i have with the new string limit?
> Thanx all!!
> Marko|||Thanx, but all my Stored Procedure i use CONVERT to obtain 120th format,
yyyy-mm-dd.
With 2000 version this is not good, and i must correct all the procedure
with yyyymmdd: is it correct'
This is a problem for me because i must to correct many procedures and then
i must tu correct all the procedure call from ado Connection; for example
cn.execute ("MyProc 'yyyy-mm-dd'") must become cn.execute ("MyProc
'yyyymmdd'")
Can you help me?
Again , Thanx !!
Mark
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Have you looked at CAST and CONVERT. Formatting a string as you want is is
> still there.
> How are you trying to use the data?
> Regards
> Mike
> "Marko" wrote:
> > Can anyone tell where i can find any guide to prevent big error!! My problems
> > are date and string. When using datetime fields, before i used [yyyy-mm-dd]
> > format, now its no longer available!!! How cai i do'!!?! Must i covert all
> > stored procedure or code? Wich problems can i have with the new string limit?
> > Thanx all!!
> > Marko

Converting report input dates to UTC

I have created a report in reporting services and it takes a number of
DateTime values as input parameters. These values will be passed on to
queries in my database; the problem is that my database stores DateTime
values as UTC.
Is there a way I can convert them to UTC before the values are sent to the
query?Hello,
You could try this:
DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @.LocalDate)
http://geekswithblogs.net/ewright/archive/2004/09/14/11180.aspx
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Converting report input dates to UTC
>thread-index: AcYtdvuTa+Zu+2dZQC6M3qdl1xmC3Q==>X-WBNR-Posting-Host: 195.139.24.170
>From: "=?Utf-8?B?Q2hyaXN0b3BoZXIgS2ltYmVsbA==?="
<c_kimbell@.newsgroup.nospam>
>Subject: Converting report input dates to UTC
>Date: Thu, 9 Feb 2006 04:47:27 -0800
>Lines: 7
>Message-ID: <920BBD92-A5C6-4A43-B965-8D1AE0151687@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:68388
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I have created a report in reporting services and it takes a number of
>DateTime values as input parameters. These values will be passed on to
>queries in my database; the problem is that my database stores DateTime
>values as UTC.
>Is there a way I can convert them to UTC before the values are sent to the
>query?
>