Date Error

Question

Date Error

SQL_Kills

Hall of Fame

Points: 3154
More actions
November 10, 2014 at 10:30 am

#298221

Hi,
I get the following error when trying to run the below SQL any ideas on how to get round this?
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
WITH
Audit AS (
SELECT 266993 ID, '01/11/2014 10:05:45: TEST5sda'log_string UNION ALL
SELECT 266992 ID, '02/11/2014 10:05:45: FLLA@@\DKKDK'log_string UNION ALL
SELECT 266991 ID, '03/11/2014 10:05:45: === Import files' log_string UNION ALL
SELECT 266990 ID, '16/04/2014 10:05:45: Database = Unknown'log_string UNION ALL
SELECT 266969 ID, '14-3-2014 20:17:59: oldest invoice actions done' log_string UNION ALL
SELECT 266968 ID, '14-3-2014 20:17:59: Generated 0 actions' log_stringUNION ALL
SELECT 266967 ID, '14-3-2014 20:17:59: oldest invoice actions' log_stringUNION ALL
SELECT 266966 ID, '7-3-2014 17:31:03: Non-existent, Currency=USD' log_string
)
SELECT
convert(datetime,left(log_string,10),103), a.*
FROM
Audit a
where convert(datetime,left(log_string,10),103) between '01-NOV-2014' and '15-NOV-2014'

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

SQL_Kills (11/10/2014)
Hi,
I get the following error when trying to run the below SQL any ideas on how to get round this?
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
WITH
Audit AS (
SELECT 266993 ID, '01/11/2014 10:05:45: TEST5sda'log_string UNION ALL
SELECT 266992 ID, '02/11/2014 10:05:45: FLLA@@\DKKDK'log_string UNION ALL
SELECT 266991 ID, '03/11/2014 10:05:45: === Import files' log_string UNION ALL
SELECT 266990 ID, '16/04/2014 10:05:45: Database = Unknown'log_string UNION ALL
SELECT 266969 ID, '14-3-2014 20:17:59: oldest invoice actions done' log_string UNION ALL
SELECT 266968 ID, '14-3-2014 20:17:59: Generated 0 actions' log_stringUNION ALL
SELECT 266967 ID, '14-3-2014 20:17:59: oldest invoice actions' log_stringUNION ALL
SELECT 266966 ID, '7-3-2014 17:31:03: Non-existent, Currency=USD' log_string
)
SELECT
convert(datetime,left(log_string,10),103), a.*
FROM
Audit a
where convert(datetime,left(log_string,10),103) between '01-NOV-2014' and '15-NOV-2014'

Try looking at the value you are trying to convert to a datetime...

SELECT

left(log_string,10)

FROM

Audit a

The last row in your sample is never going to work there. You should probably look at using substring and charindex to split out the date portion. The fact that you have varying date formats in here suggests you are going to be fighting a losing battle though. I assume your dateformat must be dmy? Are you absolutely certain that all of the data is in that format?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 2

Hi Sean,

Yes I know ID 266966 is not a valid date, I tried to use the IsDate function to exclude these dates but wasn't working either so not sure what else to do?

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Here is how you could use the existing data to do what you are trying to do.

SELECT

left(log_string,10) --This is what you are trying to convert to a date

, SUBSTRING(log_string, 0, charindex(' ' , log_string, 0))

, CAST(SUBSTRING(log_string, 0, charindex(' ' , log_string, 0)) as date)

FROM

Audit a

where CAST(SUBSTRING(log_string, 0, charindex(' ' , log_string, 0)) as date) between '01-NOV-2014' and '15-NOV-2014'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 4

Thanks Sean, but getting the following error

Conversion failed when converting date and/or time from character string.

Brandie Tarvin SSC Guru Points: 173064 More actions · Answer 5

You have two different date formats in your sample code. Unless you have a way for SQL to auto-identify which log strings contain which date formats, you're going to continually run into conversion issues. There is just no way do this conversion (as it stands) without hard coding in the IDs as the method of determination.

There are 4 records that do a DD-MM-YYYY format and 5 records that do a MM-DD-YYYY format. It's the DD-MM-YYYY format records that are causing the issue.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

SQL_Kills (11/10/2014)
Thanks Sean, but getting the following error
Conversion failed when converting date and/or time from character string.

You probably have to set the dateformat first or this will fail. It works fine on my instance after I set the dateformat.

set dateformat dmy;

Now if your data is not in a consistent format you are pretty much screwed unless you can find something consistent. Maybe you will have to move your data to a temp table in a couple of passes (one for each date format) or something like that.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 101099 More actions · Answer 7

I used a CROSS APPLY to assign an alias name to the result of the string to datetime conversion, so the conversion doesn't have to be repeated anywhere.

I also changed the date/datetime "between" to a "standard" >= and < instead.

SET DATEFORMAT dmy

;WITH

Audit AS (

SELECT 266993 ID, '01/11/2014 10:05:45: TEST5sda'log_string UNION ALL

SELECT 266992 ID, '02/11/2014 10:05:45: FLLA@@\DKKDK'log_string UNION ALL

SELECT 266991 ID, '03/11/2014 10:05:45: === Import files' log_string UNION ALL

SELECT 266990 ID, '16/04/2014 10:05:45: Database = Unknown'log_string UNION ALL

SELECT 266969 ID, '14-3-2014 20:17:59: oldest invoice actions done' log_string UNION ALL

SELECT 266968 ID, '14-3-2014 20:17:59: Generated 0 actions' log_stringUNION ALL

SELECT 266967 ID, '14-3-2014 20:17:59: oldest invoice actions' log_stringUNION ALL

SELECT 266966 ID, '7-3-2014 17:31:03: Non-existent, Currency=USD' log_string

)

SELECT

log_datetime,

*

FROM

Audit a

CROSS APPLY (

SELECT CAST(LEFT(log_string, CHARINDEX(': ', log_string) - 1) AS datetime) AS log_datetime

) AS assign_alias_names

where log_datetime >= '01-NOV-2014' and

log_datetime < '16-NOV-2014'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".