rss2
asked on
Convert dd/mm/yyyy string to smalldatetime
For the life of me I can't figure out why this is not working.
My SQL Server is set to using US dates. I have a list of dd/mm/yyyy formatted strings that I would like to update the smalldatetime column with.
This:
update ws
set time_dt = convert(varchar(10),[time]
lasttradedate_dt = convert(varchar(10),lasttr
..comes up with this error:
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.
The statement has been terminated.
(where columns time_dt and lasttradedate_dt are in the smalldatetime format and columns [time] and lasttradedate are string that look like: dd/mm/yyyy)
I have tried SO MANY different ways. Can anyone help ?? MOST frustrating..
My SQL Server is set to using US dates. I have a list of dd/mm/yyyy formatted strings that I would like to update the smalldatetime column with.
This:
update ws
set time_dt = convert(varchar(10),[time]
lasttradedate_dt = convert(varchar(10),lasttr
..comes up with this error:
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.
The statement has been terminated.
(where columns time_dt and lasttradedate_dt are in the smalldatetime format and columns [time] and lasttradedate are string that look like: dd/mm/yyyy)
I have tried SO MANY different ways. Can anyone help ?? MOST frustrating..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THat gives me:
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated.
How do I reformat the dd/mm/yyyy strings to yyyy-mm-dd?
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated.
How do I reformat the dd/mm/yyyy strings to yyyy-mm-dd?
I beg to differ... datetime and smalldatetime are very stable variables, that use BIGINT and INT to represent dates.
Can you please look at the values of the data you are trying to update?
Are there any bogus data entries (or ones that become bogus) in the [time] and [lasttradedate] columns?
Try to also use 101 in the convert, instead of 110, although I'm sure that this is not where the problem is.
Hope this helps,
Omri.
Can you please look at the values of the data you are trying to update?
Are there any bogus data entries (or ones that become bogus) in the [time] and [lasttradedate] columns?
Try to also use 101 in the convert, instead of 110, although I'm sure that this is not where the problem is.
Hope this helps,
Omri.
ASKER
Thank you Omri. You were absolutely right. There were strange characters in the strings which I thought were just dd/mm/yyyy.
I removed the extra characters and used your little miniscript above. That worked.
Many many thanks!!!!
I removed the extra characters and used your little miniscript above. That worked.
Many many thanks!!!!
ASKER
Oh dear, I should have split the points.rafrancisco actually gave me the working script.
How do I retract the points and split them up?
I'm very sorry about that.
How do I retract the points and split them up?
I'm very sorry about that.
obahat,
>>datetime and smalldatetime are very stable variables, that use BIGINT and INT to represent dates.<<
Not exactly: Internally datetime data type uses two 4 byte integers and smalldatetime uses two 2 byte integers. The concept of bigint and int and merely the T-SQL representation of 8 byte and 4 byte integers.
>>datetime and smalldatetime are very stable variables, that use BIGINT and INT to represent dates.<<
Not exactly: Internally datetime data type uses two 4 byte integers and smalldatetime uses two 2 byte integers. The concept of bigint and int and merely the T-SQL representation of 8 byte and 4 byte integers.
Hi all !
Very usefull discussion!
I think in global application one should consider the culture info:
So this example uses fi-FI for Finland , you might consider de-DE for Deutchland , etc.
Very usefull discussion!
I think in global application one should consider the culture info:
So this example uses fi-FI for Finland , you might consider de-DE for Deutchland , etc.
object valueToConvert = "06.12.2008 13:45:17" //finnish independance day ; )
string strTime = System.Convert.ToString ( valueToConvert ).Trim ();
System.Diagnostics.Debug.WriteIf ( "The time string is " + strTime );
//Take into consideration the culture
CultureInfo culture = CultureInfo.CreateSpecificCulture ( "fi-FI" );
DateTime dtDate = DateTime.Parse ( strTime , culture );
System.Diagnostics.Debug.WriteIf ( "My dtDate.ToLongTimeString() is " + dtDate.ToLongTimeString () );
//Get the new vormat
valueToConvert = dtDate.ToLongTimeString() ;
System.Diagnostics.Debug.WriteIf ( "The datetime time " + dtDate.ToLongTimeString ());
//FOR SHORT DATE FORMAT
string strTime = System.Convert.ToString ( valueToConvert ).Trim();
System.Diagnostics.Debug.WriteIf ( "The time string is " + strTime );
//Old Data Format
CultureInfo culture = CultureInfo.CreateSpecificCulture("fi-FI");
DateTime dtDate = DateTime.Parse ( strTime , culture );
System.Diagnostics.Debug.WriteIf ( "My dtDate.ToString() is " + dtDate.ToShortDateString () );
//New Data Format
valueToConvert = dtDate.ToShortDateString () ;
System.Diagnostics.Debug.WriteIf ( "The smalldate time is dd.mm.yyyy" + dtDate.ToString ( "dd.mm.yyyy" ) );
Use yyyy-mm-dd as the format for your strings. It is by far the most reliable way to pass dates and times.
Tim Cottee