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],110),
lasttradedate_dt = convert(varchar(10),lasttradedate,110)


..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..
rss2Asked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
Try this one

SET DATEFORMAT dmy

update ws
set time_dt = cast([time] as smalldatetime),
lasttradedate_dt = cast(lasttradedate as smalldatetime)

SET DATEFORMAT mdy
0
 
TimCotteeCommented:
Hi rss2,

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
0
 
rss2Author Commented:
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?

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
obahatCommented:
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.
0
 
rss2Author Commented:
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!!!!

0
 
rss2Author Commented:
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.
0
 
Anthony PerkinsCommented:
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.
0
 
yordan_georgievCommented:
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.
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" ) );

Open in new window

0
All Courses

From novice to tech pro — start learning today.