December 12, 2011 at 1:55 pm
Hi Guys,
I have the following code the works in 2000 but not in 2008
SELECT substring(ltrim(rtrim(gh.purchaseordernumber)),3,30) AS po_number,
cast(gl.poline AS CHAR(8)) AS po_line_number,
1 AS po_sequence,
CASE @ShillMode
WHEN 1 THEN RTRIM(lTRIM(CAST(gh.grnnumber AS CHAR(8))))
ELSE ltrim(rtrim(@warehouse)) + replicate('0',6 - len(rtrim(ltrim(cast(gh.grnnumber AS CHAR(6)))))) + rtrim(ltrim(cast(gh.grnnumber AS CHAR(6))))
END AS grn_number,
gl.grnline AS grn_line_number,
cast(gh.warehouse AS CHAR(2)) AS warehouse,
cast('' AS CHAR(15)) AS bin_location,
cast(gl.productnumber AS CHAR(15)) AS part_code,
max(gl.quantityreceived) AS quantity,
max(gl.weight) AS weight,
cast(ltrim(rtrim(cast(gl.grnnumber AS VARCHAR(7)))) + ltrim(rtrim(cast(gl.grnline AS VARCHAR(3)))) AS CHAR(11)) AS fdc_serial,
cast('V' AS CHAR(1)) AS status_flag,
cast(gl.batchnumber AS CHAR(10)) AS batch_code,
cast(gh.grncreationdate AS DATETIME) AS fdc_time,
cast(convert(CHAR(10),gh.grncreationdate,120) AS DATETIME) AS movement_date,
'GRNAC' AS trans_type,
'N' AS inspection_required,
'WMS Interface' AS fdc_error_text,
gh.deliverynotenumber AS suppliers_ref,
sum(fg.quantity) AS no_of_blocks
FROM fgstockmovements fg
INNER JOIN grnheader gh
ON gh.grnnumber = fg.warehousefrom
AND gh.company = fg.company
AND gh.location = fg.location
AND gh.warehouse = fg.warehouse
INNER JOIN grnlines gl
ON gh.grnnumber = gl.grnnumber
AND gh.company = gl.company
AND gh.location = gl.location
AND gh.warehouse = gl.warehouse
AND fg.productnumber = gl.productnumber
AND fg.documentreference = gl.containerid
WHERE fg.requiresupload = 99
AND fg.company = @Company
AND fg.location = @Location
AND fg.warehouse = @Warehouse
AND fg.movementtype = 1
AND fg.requiresupload = 99
AND gh.purchaseordernumber <> ' '
AND gh.status <> 0
GROUP BY substring(ltrim(rtrim(gh.purchaseordernumber)),3,30),
gl.poline,
gh.grnnumber,
gl.grnline,
gh.warehouse,
gl.productnumber,
cast(ltrim(rtrim(cast(gl.grnnumber AS VARCHAR(7)))) + ltrim(rtrim(cast(gl.grnline AS VARCHAR(3)))) AS CHAR(11)),
gl.batchnumber,
gh.grncreationdate,
cast(convert(CHAR(10),gh.grncreationdate,120) AS DATETIME),
gh.deliverynotenumber
I know the lines causing the problem are
cast(convert(CHAR(10),gh.grncreationdate,120) AS DATETIME) AS movement_date,
and
cast(convert(CHAR(10),gh.grncreationdate,120) AS DATETIME),
The error I get is
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
When I come the lines above out there is no error. Any idea's why this is or what I can do to fix. I 've tried a few things but nothing has worked for me so far 🙁
December 12, 2011 at 2:00 pm
Have you narrowed down whether the whole dataset is failing, or just a few rows? If just a few rows, what does the data in them look like? If the whole dataset, what are some sample values from that column?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 12, 2011 at 2:02 pm
Not exactly sure why there is an issue but are you actually taking a datetime, converting it to char and then back to datetime? Can you just simply use the original value? Unless I am missing something you would get the same (but faster) results.
_______________________________________________________________
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/
December 12, 2011 at 2:28 pm
Sean - I think you might be right
It happens for any row in my data set. I just bring back the one field causing my problem like so
SELECT TOP 5 cast(convert(CHAR(20),gh.grncreationdate,120) AS DATETIME) FROM GRNHeader gh
I get the same problem.
However if I take out the 120 so it looks like this
SELECT TOP 5 cast(convert(CHAR(20),gh.grncreationdate) AS DATETIME) FROM GRNHeader gh it will run for me and gives data in the following format
2010-09-11 06:31:00.000
2010-09-13 08:12:00.000
2008-09-16 12:09:00.000
The reason I need the 120 in there though is because I need the date formatted in 2010-09-11 06:31:00 format, but taking the original value seems to give me that.
This code is part of a bigger query so perhaps the reason why the code was originally beings cast and converted will become clear as I delve in.
December 12, 2011 at 2:33 pm
ricko (12/12/2011)
Sean - I think you might be rightIt happens for any row in my data set. I just bring back the one field causing my problem like so
SELECT TOP 5 cast(convert(CHAR(20),gh.grncreationdate,120) AS DATETIME) FROM GRNHeader ghI get the same problem.
However if I take out the 120 so it looks like this
SELECT TOP 5 cast(convert(CHAR(20),gh.grncreationdate) AS DATETIME) FROM GRNHeader ghit will run for me and gives data in the following format2010-09-11 06:31:00.000
2010-09-13 08:12:00.000
2008-09-16 12:09:00.000
The reason I need the 120 in there though is because I need the date formatted in 2010-09-11 06:31:00 format, but taking the original value seems to give me that.
This code is part of a bigger query so perhaps the reason why the code was originally beings cast and converted will become clear as I delve in.
Formatting is in the front end. Since you are storing your data as datetime the "format" is totally irrelevant.
What happens if you do this:
SELECT TOP 5 gh.grncreationdate FROM GRNHeader gh
Since you seem to be missing a couple details in your explanation I am guessing that you are using this to display data in the front end?
_______________________________________________________________
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/
December 12, 2011 at 2:42 pm
Don't bother with date/time formatting in the query, unless you absolutely have to. The presentation layer is where that should happen. Allows for localization, personal preferences, et al.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2011 at 3:29 am
It appears that my issue is in fact down to data. This code worked before on an older server. I thought it was a 2000 V 2008 issue but in fact it's a local language settings issue on the server. British vs Us_English, screwing up the day and month positions in the date.
I need to do the formatting in the background as I need the date in a certain format in order to insert it into a field on another platform and this is being used as part of an openquery statement.
December 13, 2011 at 6:18 am
Aha! That makes more sense.
Use the "Set Language" option in the script to force US English in that case. Unless you can change the server setting without breaking anything.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2011 at 6:33 am
yep - will change the language setting in the script - thanks for the help
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply