convert

hi,
How do I convert this into smalldatetime please?
I am doing this because there is a field of type varchar which has to go into a separate table with field of type smalldatetime.

select convert(smalldatetime, '14/10/04', 101)

This is what I have but the error is:
Conversion failed when converting character string to smalldatetime data type.

This looks like a locale issue; try executing a SET DATEFORMAT DMY before executing your convert. You might also want to give a look to Umachandar's comments in this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=597495&SiteID=1

Note the suggestion to try to use one of the ISO formats when possible.

Code Snippet

set dateformat dmy

select convert(varchar, cast('14/10/7' as datetime), 101) [a date/time]

/*
a date/time
10/14/2007
*/

select cast('14/10/7' as smalldatetime) as [converted]

/*
converted
2007-10-14 00:00:00
*/

|||

Try this:

select convert(smalldatetime, '10/14/2004', 101)

You already had smalldatetime and the system was probably expecting the MM/DD/YYYY format rather than DD/MM/YYYY

sqlsql

Convert

I want to do this

SELECT TOP 100 PERCENT CONVERT(smalldatetime, DOCDATE, 105) AS DOCDATE
FROM SQSDBA.D_DETAILS
WHERE (DOCTYPE = 'SSCB') AND (DOCNUM = 155)
ORDER BY ROWNO

It is returning a date as 13/12/2003 ?

Any Ideas ?

Michaelwhat result are u expecting?|||I expect 13-12-2003|||select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(y,docdate))|||select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(y,docdate))FROM SQSDBA.D_DETAILS
WHERE (DOCTYPE = 'SSCB') AND (DOCNUM = 155)
ORDER BY ROWNO|||Originally posted by harshal_in
select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(y,docdate))

woops i get 13-12-347 ??|||oh sorry!!
select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(yy,docdate))FROM SQSDBA.D_DETAILS
WHERE (DOCTYPE = 'SSCB') AND (DOCNUM = 155)
ORDER BY ROWNO|||Originally posted by harshal_in
oh sorry!!
select convert(varchar(10),datepart(d,docdate))+'-'+convert(varchar(10),datepart(m,docdate))+'-'+convert(varchar(10),datepart(yy,docdate))FROM SQSDBA.D_DETAILS
WHERE (DOCTYPE = 'SSCB') AND (DOCNUM = 155)
ORDER BY ROWNO

Thanks very much works a treat

Very much appreciate your Help|||Grievem,

Your query is (was) actually returning a datetime datatype which was being displayed in a different format than you desired.

Ideally, you should leave it be, and then let whatever interface you use do the formatting you want. This is because if you convert it to a character field you will have difficulty sorting or grouping on the results.

Let SQL Server do the calculations and the data retrieval, which it is best at, and let the interface do the formatting, because that is what it is best at.|||blindman's points are good. In your query, it is not neccesary to convert one data type to another. You can do it on your program, i.e., ASP code.|||I do agree with both of u it is not necessary to convert the datatype into varchar as it would affect the sorting and other stuff.The best way to format it is in the front end.
regards,
harshal.|||Originally posted by blindman
Ideally, you should leave it be, and then let whatever interface you use do the formatting you want. This is because if you convert it to a character field you will have difficulty sorting or grouping on the results. this is quite true, insofar as it is good general advice, however, i would like to point out that ISO standard date formats do sort and group perfectly well

furthermore, if you just "let it be", it may come out in a format that you will then have to unstring in your application code to get into the format you prefer!!! this makes the application code dependent on whichever default date format this particular installation uses, and blows you right out of the water if somebody changes the default!!!

my advice: always display dates out of the database in ISO format

also, a comment on this expression:

CONVERT(smalldatetime, DOCDATE, 105)

please note, the style value of the CONVERT function, e.g. 105 in the above example, is used to determine the display format for converting a datetime value to character string, not for helping the database to "unstring" or "interpret" or "decipher" a string value

as far as i know, the database will convert a string into a datetime value depending only on whether it can recognize a valid date value in the string, and not by any helpful "hint" you may be trying to throw its way

at least, that has been my experience, and that is also how i interpret the CAST and CONVERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp) documentation:style

Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types|||"please note, the style value of the CONVERT function, e.g. 105 in the above example, is used to determine the display format for converting a datetime value to character string, not for helping the database to "unstring" or "interpret" or "decipher" a string value"

That's what I used to think as well, but I believe the point was made and demonstrated on this forum a few months back that the convert function will use the style value when deciding whether to interpret "01/02/03" as "Jan 2, 2003", "Feb 1, 2003", or "Feb 3, 2001".

select convert(datetime, '01/02/03', 1)
select convert(datetime, '01/02/03', 3)
select convert(datetime, '01/02/03', 11)

Results:
2003-01-02 00:00:00.000
2003-02-01 00:00:00.000
2001-02-03 00:00:00.000|||whoa

shock and awe

microsoft product actually works better than its documentation

who'd'a thunk it

thanks, blindman

Conversion problems between mssql and access

Hello

When I trying to insert data with datatype datetime or smalldatetime from SQL Server into a table in a linked access database I get this error :

Server: Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type smalldatetime to float is not allowed. Use the CONVERT function to run this query.

I dont understand why it try to insert it as a float?!Because from a SQL Server point of view DateTime values ARE float values.

So you cannot do an implict cast but you have to do explictly by using the T-SQL statement "CONVERT"

Look in Books On Line for better help on conversions.|||Hello

I have tried CONVERT. But I dont know to which datatype I should convert my source value.
I have converted to varchar and nvarchar but I still got the same error.|||can you post your code, and specify from what kind of data type you what convert to?|||INSERT INTO LINKEDACCESS...ContactTarget
Select ChangedBy, ChangedDate, PersonIdNo,IdNo,TargetCode,TargetCodeproductCode,T argetCodePotentialCode
from tblContactTarget
WHERE Id NOT IN(select Id from LINKEDACCESS...ContactTarget CT
WHERE CT.Target = tblContactTarget.TargetCode)

ChangedDate is of datatype Datetime in SQL and Date/Time in Access.
This INSERT will trigger the error I wrote about.|||Try to convert it to a varchar. use the CONVERT function so that you can even specify the dateformat you need to have

Conversion of Char to SmallDateTime

I have a column that has date information in it in the following
format: yyyymmdd I want to convert this information into and actual
SmallDateTime column so I can use it for comparison etc. Some of the
dates that are stored in this data are either blank or invalid dates
because of bad user input.
select
cast(left(inv_dt1,4) + '-' + right(left(inv_dt1,6),2)+'-'+
right(inv_dt1,2) as smalldatetime) as inv_dt
into newtable
from origtable
If I run that code, it errors out on the invalid fields. Is there a
way to tell SQL to just NULL the field if it is invalid in any way and
continue on?
Thanks,use the isdate function
create table WasabiTable (inv_dt1 varchar(23))
insert WasabiTable values ('ababababa')
insert WasabiTable values ('20060101')
insert WasabiTable values ('20060299')
insert WasabiTable values (NULL)
insert WasabiTable values ('20060401')
insert WasabiTable values ('20050331')
select
case
when isdate(inv_dt1) = 1 then convert(smalldatetime,inv_dt1)
else null
end onsetdate
from WasabiTable
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On 30 May 2006 12:10:08 -0700, "SQL Menace" <denis.gobo@.gmail.com>
wrote:

>use the isdate function
Just to add to that, be aware the ISDATE tests if the string will
convert to a valid DATETIME datatype. Valid dates for DATETIME are
from January 1, 1753 through December 31, 9999. Valid dates for
SMALLDATETIME are from January 1, 1900, through June 6, 2079. So,
while it may be unlikely, it is possible for a string to satisfy the
ISDATE test yet still fail the conversion to SMALLDATETIME.
Roy Harvey
Beacon Falls, CT

Conversion failed when converting character string to smalldatetime data type.

Hello, I have problem with this code.(This programpresents - there is GridView tied to a SQL database that will sort the data selected by a dropdownList at time categories. There are 2 time categories in DropDownList - this day, this week.

Problem: when I choose one categorie in dropDownlist for examle this week and submit data on the server I got this error.

Conversion failed when converting character string to smalldatetime data type.

Here is code:

<%

@.PageLanguage="C#" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">

protectedvoid DropDownList1_SelectedIndexChanged(object sender,EventArgs e)

{

string datePatt =@."yyyymmdd";

// Get start and end of day

DateTime StartDate =DateTime.Today;

string @.StartDate1 = StartDate.ToString(datePatt);

string @.EndDate = StartDate.AddDays(1).ToString(datePatt);// Get start and end of weekstring @.startOfWeek = StartDate.AddDays(0 - (int)StartDate.DayOfWeek).ToString(datePatt);string @.startOfNextWeek = StartDate.AddDays(7 - (int)StartDate.DayOfWeek).ToString(datePatt);

switch (DropDownList1.SelectedValue)

{

case"1":

// day

SqlDataSource1.SelectCommand =

"SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" +"WHERE" +"[DATE] >=" +"'@.StartDate1'" +" AND [DATE] < " +"'@.EndDate'";break;case"2"://week

SqlDataSource1.SelectCommand =

"SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" +"WHERE" +"[DATE] >=" +"'@.startOfWeek'" +"AND [DATE] <" +"'@.startOfNextWeek'";break;

}

}

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headid="Head1"runat="server"><title>Untitled Page</title><styletype="text/css">body {font:1emVerdana;

}

</style>

</

head>

<

body><formid="form1"runat="server"><div>

<asp:DropDownListID="DropDownList1"runat="server"AutoPostBack="True"OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"Style="z-index: 100; left: 414px; position: absolute; top: 22px"><asp:ListItemSelected="True"Value="1">jeden den</asp:ListItem><asp:ListItemValue="2">jeden tyden</asp:ListItem></asp:DropDownList>

<asp:GridViewID="GridView1"runat="server"Style="z-index: 102; left: 228px; position: absolute;

top: 107px"

DataSourceID="SqlDataSource1"AutoGenerateColumns="True">

</asp:GridView> <br/><br/><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="Data Source=CR\SQLEXPRESS;

Initial Catalog=MyConn;Integrated Security=True"

ProviderName="System.Data.SqlClient"></asp:SqlDataSource></div></form>

</

body>

</

html>

string datePatt =@."yyyymmdd";

In your 'date' pattern, mm is minutes.

You want MM for month: yyyyMMdd

|||Thank you, for your reply, Icorrected this error. But the problem is still here.|||

I notice you are trying to use parameters but I don't see the code that adds parameters: Parameters.Add(...).

If you pass in the date as a DateTime, you will not have to format it to a string.

|||Thank you, I will try.

Conversion failed when converting character string to smalldatetime data type

I am newbie in asp and sql, and I am using VS & SQL express

when I try to submit I get following error

"Conversion failed when converting character string to smalldatetime data type"

Following is my insert statement

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@.DropDownList1','@.DropDownList2','@.DropDownList3','@.TextBox6','@.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3','@.TextBox1','@.TextBox2','@.TextBox3','@.TextBox4','@.TextBox5','@.TextBox7')">

</asp:SqlDataSource>

These are @.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3' defined as datetime in database.

I would appriciate if somebody could help.

Thanks

anybody here?|||

The issue here seems to be specific to the authoring in the ASP.Net pages...someone in the ASP.Net forum may be able to help you.

Here's a potentially useful links to start with: http://forums.asp.net/thread/881828.aspx

Conversion failed when converting character string to smalldatetime data type

I am newbie in asp and sql, and I am using VS & SQL express

when I try to submit I get following error

"Conversion failed when converting character string to smalldatetime data type"

Following is my insert statement

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@.DropDownList1','@.DropDownList2','@.DropDownList3','@.TextBox6','@.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3','@.TextBox1','@.TextBox2','@.TextBox3','@.TextBox4','@.TextBox5','@.TextBox7')">

</asp:SqlDataSource>

These are @.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3' defined as datetime in database.

I would appriciate if somebody could help.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES (@.DropDownList1,@.DropDownList2,@.DropDownList3,@.TextBox6,@.DropDownCalendar1,@.DropDownCalendar2,@.DropDownCalendar3,@.TextBox1,@.TextBox2,@.TextBox3,@.TextBox4,@.TextBox5,@.TextBox7)">

</asp:SqlDataSource>

|||

Now I am getting following error

Must declare the scalar variable "@.DropDownList1".

How do I do it in asp, I found the syntax for declare statement but don't know how put it in asp?

Thanks

|||Go to the design view, right click the sqldatasource, choose properties, then choose the selectcommand. A dialog should open, in there you can add all your parameters for the select.|||

I did that and still getting same error

after changes it looks like this

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data"InsertCommand="INSERT INTO data(Apps, Location, Impact, System, tDate, Start_Time, End_Time, Duration, Problem, Cause, Solution, DW_Case, Comments) VALUES (@.DropDownList1,@.DropDownList2,@.DropDownList3,@.TextBox6,@.DropDownCalendar1,@.DropDownCalendar2,@.DropDownCalendar3,@.TextBox1,@.TextBox2,@.TextBox3,@.TextBox4,@.TextBox5,@.TextBox7)">

<SelectParameters>

<asp:FormParameterFormField="DropDownList1"Name="@.Apps"/>

<asp:FormParameterFormField="DropDownList2"Name="@.Location"/>

<asp:FormParameterFormField="DropDownList3"Name="@.Impact"/>

<asp:FormParameterFormField="TextBox6"Name="@.System"/>

<asp:FormParameterFormField="DropDownCalendar1"Name="@.tdate"/>

<asp:FormParameterFormField="DropDownCalendar2"Name="@.Start_time"/>

<asp:FormParameterFormField="DropDownCalendar3"Name="@.End_time"/>

<asp:FormParameterFormField="TextBox1"Name="@.Duration"/>

<asp:FormParameterFormField="TextBox2"Name="@.Problem"/>

<asp:FormParameterFormField="TextBox3"Name="@.Cause"/>

<asp:FormParameterFormField="TextBox4"Name="@.Solution"/>

<asp:FormParameterFormField="TextBox5"Name="@.DW_case"/>

<asp:FormParameterFormField="TextBox7"Name="@.Comments"/>

</SelectParameters>

</asp:SqlDataSource>

I really appreciate your.

Thanks

|||I also tried doing samething with insert parameter and still no luck|||You have to call them the same thing. @.DropDownList1=@.Apps. Change one to the other and repeat for all parameters. Either change the insert statement, replacing @.DropDownList1 with @.Apps, *OR* change the parameter "@.Apps" to use the name "@.DropDownList1".|||

Motley

I really appreciate your help,

And a BIG Thanks to you.

sqlsql