October 31, 2008 at 9:54 am
Having a strange date issue here... the database (not developed by me I must add at this point!) stores two dates in datetime fields but one in a varchar field.
The datetime ones are fine, but the issue with the varchar field is that the date is stored in UK format - 27/08/2008 05:37 - so when I try to do anything with it, basically I can't
Tried a straight conversion but that didn't work, and have tried a few things including converting to a datetime field if the date is a date - that's fine but only recognises those that would "work" in US format, eg 01/09/2008 05:37 is then read as a 09 Jan instead of 01 Sep
CASE ISDATE(DISP.actiondate)
WHEN 1
THEN CONVERT(VARCHAR, CAST(DISP.actiondate As DateTime), 20)
ELSE NULL
END AS [Action Date]
I'm off to try a few more things but it's close to the weekend and my brain is sore, so any help would be greatly appreciated 🙂
October 31, 2008 at 10:02 am
Try this:
SELECT CONVERT(datetime,'27/08/2008 05:37',103)
The 103 specifies that the date is in British/French format.
October 31, 2008 at 10:05 am
Try this:
select convert(datetime, '01/09/2008 05:37', 103)
The style 103 indicates that you want to read the string you're passing through as if it was in the british datetime format.
Hope that helps.
Looks like I was beaten to this:)
October 31, 2008 at 10:05 am
HI There,
try using the SET DATEFORMAT So that the system knows how to read your string.
e.g
SET DATEFORMAT dmy
DECLARE @val VARCHAR(100)
SET @val = '27/08/2008 05:37'
SELECT CAST(@val as DATETIME)
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 31, 2008 at 10:17 am
Thanks guys
Garadin/SQLZ - thanks, but the problem is (sorry, didn't mention this!) that using CONVERT does work ok on that field, but I can't use it in comparisons against the other actual DateTime fields.
SELECT
CONVERT(VARCHAR, Date1, 103) AS [Creation Date],
CONVERT(VARCHAR, Date2, 103) AS [Finish Date],
CONVERT(VARCHAR, Date3, 103) AS [Action Date]
FROM x
returns dates in format:
02/08/2008,04/08/2008,01/09/2008 04:53
Then trying to compare them using a datediff throws up the classic error:
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.
I think I need to convert them all to a "universal" datetime format? Am suprised 103 brings back diff results on a datetime versus a varchar.
Christopher Stobbs (10/31/2008)
HI There,try using the SET DATEFORMAT So that the system knows how to read your string.
e.g
SET DATEFORMAT dmy
DECLARE @val VARCHAR(100)
SET @val = '27/08/2008 05:37'
SELECT CAST(@val as DATETIME)
Thanks
Chris
Thanks Chris, I'll give this a whirl
October 31, 2008 at 10:41 am
I think the problem here might be that you're converting to varchar, and then SQL Server tries to implicitly convert that varchar values to datetimes.
Try and convert them to datetime rather than varchar.
October 31, 2008 at 10:59 am
Could you provide us with the DDL of the table, some sample data, and the expected results? For guidance, read the article mentioned below.
October 31, 2008 at 11:01 am
SQLZ (10/31/2008)
I think the problem here might be that you're converting to varchar, and then SQL Server tries to implicitly convert that varchar values to datetimes.Try and convert them to datetime rather than varchar.
You're absolutely right... I was doing a DateDiff on two fields converted to VARCHAR instead of DateTime.
Changed to convert to Datetime 103 and it works a treat.
Thanks for your help guys and sorry for timewasting - was having a dumb moment 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply