select distinct date from datetime
Hi i got a table with a few lines that looks like this:
id -> date
1. 2006-12-19 09:45:00
2. 2006-12-19 05:45:00
3. 2006-12-20 05:45:00
4. 2006-12-21 05:45:00Note that id 1 and 2 have the same date.
I want with a sql question get this result:
1. 2006-12-19
3. 2006-12-20
4. 2006-12-21
Is this possible, make a distinct on the date only and not the time and if, how so?
Thanks!
herman
|
0 |
|
thrinker
1/10/2007 8:22:44 PM
asp.net.web-forms
93655 articles. 
6 followers.
10 Replies
646 Views
Hi,
for example
SELECT DISTINCT CAST(CAST(MONTH(date) as varchar(2)) + '/' + CAST(DAY(date) as varchar(2)) + '/' + CAST(YEAR(date) as varchar(4)) as datetime) FROM TestTablewould get you distinct dates. If you need ids also, then it could lopok something like
SELECT ( SELECT top 1 id FROM testTable WHERE CAST(CAST(MONTH(date) as varchar(2)) + '/' + CAST(DAY(date) as varchar(2)) + '/' + CAST(YEAR(date) as varchar(4)) as datetime)=newdate ORDEr BY id ), newdate FROM ( SELECT DISTINCT CAST(CAST(MONTH(date) as varchar(2)) + '/' + CAST(DAY(date) as varchar(2)) + '/' + CAST(YEAR(date) as varchar(4)) as datetime) newdate FROM TestTable a ) tmpOptionally you could just use functions instead of casting on outer querySELECT ( SELECT top 1 id FROM testTable WHERE YEAR(date)=YEAR(newdate) AND MONTH(date)=MONTH(newdate) AND DAY(date)=DAY(newdate) ORDEr BY id ), newdate FROM ( SELECT DISTINCT CAST(CAST(MONTH(date) as varchar(2)) + '/' + CAST(DAY(date) as varchar(2)) + '/' + CAST(YEAR(date) as varchar(4)) as datetime) newdate FROM TestTable a ) tmp
Thanks,
Teemu Keiski
Finland, EU
|
|
0 |
|
joteke
1/10/2007 9:08:17 PM
Thanks, helped alot.
Heres another question: I want too select info from the table again but like this:
id -> date -> project
1. 2006-12-19 09:45:00 b-12
2. 2006-12-19 05:45:00 b-13
3. 2006-12-20 05:45:00 b-12
4. 2006-12-21 05:45:00 b-12SELECT project FROM table WHERE started='2006-12-19'
The result i want:
b-12
b-13But since started is a datetime it cant be done like this.
Anyone?
herman
|
|
0 |
|
thrinker
1/10/2007 10:33:20 PM
hi,
try this
string co = "Data Source=localhost\\sqlexpress;Initial Catalog=pubs;Integrated Security=True";
System.Data.SqlClient.SqlConnection sqlconnect = new System.Data.SqlClient.SqlConnection(co);
System.Data.SqlClient.SqlCommand sqlcommand = new System.Data.SqlClient.SqlCommand();
sqlcommand.CommandText = "select project from test where date=@date";
sqlcommand.Connection = sqlconnect;
System.Data.SqlClient.SqlParameter p1 = new System.Data.SqlClient.SqlParameter();
p1.ParameterName = "@date";
p1.Value = DateTime.Parse("12/19/2007");
p1.SqlDbType = System.Data.SqlDbType.DateTime;
sqlcommand.Parameters.Add(p1);
sqlconnect.Open();
System.Data.SqlClient.SqlDataReader dr = sqlcommand.ExecuteReader();
while (dr.Read())
TextBox1.Text = dr.GetValue(0).ToString();
sqlconnect.Close();
Jessica Cao
Sincerely,
Microsoft Online Community Support
“Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. ”
|
|
0 |
|
Jessica
1/11/2007 3:51:40 AM
hi herman
you can try following
SELECT project FROM table WHERE convert(varchar,started,103) = '19/12/2006'
thanks,
satish.
Kind Attn: If a reply to your post helped you, kindly mark it as Answered.
__________________________________________________
Please save Animals Help World Society For Protection Of Animals,
Protect these speechless creatures of GOD
|
|
0 |
|
satish_nagdev
1/11/2007 4:10:30 AM
Thanks! it worked its just, im having a datetime that i convert to dd/MM/yyyy so after the convert the result is:
dtDates.ToString("dd/MM/yyyy")
'19-12-2006'
But i need the result: '19/12/2006' -> with thease / instead of -anyone know how i can convert in a diffrent way?
herman
|
|
0 |
|
thrinker
1/11/2007 10:59:23 AM
I really really need help on this one! please!!
|
|
0 |
|
thrinker
1/11/2007 4:04:39 PM
/ in format string is a date separator as is replaced by the separator of your current locale. You can just write it like this
dtDates.ToString("dd") & "/" & dtDates.ToString("MM") & "/" & dtDates.ToString("yyyy")
Thanks,
Teemu Keiski
Finland, EU
|
|
0 |
|
joteke
1/11/2007 4:28:06 PM
Thank you but i had to replace the & with +, maybe because of C#?, well i duno hehhe
But it workes great now!
herman
|
|
0 |
|
thrinker
1/11/2007 5:11:04 PM
Yeah C# uses + for string concatenation. I wasn't aware which language you use.
Thanks,
Teemu Keiski
Finland, EU
|
|
0 |
|
joteke
1/11/2007 5:42:51 PM
even
Response.Write(dt.ToString("dd/MM/yyyy")); worked for me.
thanks,
satish.
Kind Attn: If a reply to your post helped you, kindly mark it as Answered.
__________________________________________________
Please save Animals Help World Society For Protection Of Animals,
Protect these speechless creatures of GOD
|
|
0 |
|
satish_nagdev
1/11/2007 5:43:55 PM
Similar Artilces:
Parse DateTime into Date or select DateTime as Date from Access
I am getting DateTime values out of an Access Database and I want to only use the Date part of this field. I have tried to cast the returned value to a DateTime value and then format the DateTime to only show the Date part in the toString() fcn. <asp:Label runat="server" Text='<%# getDate(DataBinder.Eval(Container, "DataItem.ACTIVE_FROM"))%>'> But I can't cast the object to a DateTime object. I have also tried using DateTime.Parse() but this gives an error saying that the format is not correct. { ...
Implementing .Net Form into an APS.net web form, can this be done
I am new here, but have been searching for a while, and may not have the correct lingo to find what I am looking for. I am tasked with implementing a .exe application that was writen vb6 then converted to .net into a new website my team is developing. The idea is to put each of the 3 different forms in this .exe application of 3 different .aspx pages. I have attempted multiple things to get this into the page and even started to just rewrite it as an ASP.net web form, but even then I can't reuse any of the code since the System.Web.UI.Page doesn't inherit the sa...
Load Text box of one web form with selected list item of list box of another web form
one help please Problem is i have two web forms 1. ForiegnkeyTest.aspx 2. foroginkeytable.aspx step 1 1 ForiegnkeyTest.aspx one web form <head runat="server"><script language="javascript" type="text/javascript"> function popWin() { var confirmWin = null; confirmWin = window.open('foroginkeytable.aspx','anycontent', 'width=455,height=435,status'); } </script> &...
windows forms in vb .net into web forms
we are creating a database driven website as part of our project. currently we have forms connecting to the database created in visual basic. these windows forms we need to convert into web forms which are asp .net compatible. can u give me an answer or show me a way. Hi, because of the difference in nature between web and win applications you'll need to reimplement at least the UI part. If you have a multitiered application in which the busines logic and data access are in different parts/assemblies you can reuse them. Grz, Kris.Read my blog. Handy Firefox plugins for web developers.Wor...
.Net Web Form & Web Service Deployment
Hi all I have written some test web services and some web form applications and deployed them to my IIS. That's fine but I now want to deploy them to my real IIS server. I have created an msi for each but the problem is I seem to only be able to deploy to the default web site. Our default web site is turned off and instead we use several other sites. Does anybody happen to if Powerbuilder 11 supports non default web sites? If not do I need to hack the msi and if so can anybody suggest a suitable tool. I have Orca but it's not very friendly. Unless of course I knew exactly w...
How to convert a chinese date to english date in web form?
Hi ASP.NET Proz, I try to convert a chinese charater date (18-一月-2008) to english date (18-Jan-2008) from a web form. Is that possible? Thanks in advance for the Proz! * .NET Proz always make impossible to possible Try this one: System.Globalization.CultureInfo nlCulture = new System.Globalization.CultureInfo("zh-CHT"); // Chinese Traditional DateTime date = DateTime.MinValue; DateTime.TryParseExact(chineseDateString, "dd-MMM-yyyy", nlCulture, System.Globalization.DateTimeStyles.None, out date); ...
How to Make Web Forms from other Web Forms
In putting together an ASP.NET application for a biz application, we have come to the point where there are three different types of basic forms which have, for the most part, been perfected. Now its is a matter of cloning those forms for various uses and modifying or adding code to suit the particular need. Copy and paste works OK, but there is a lot of changing of certain words, etc. Is there a way in ASP.NET to take these basic forms, turn them into classes or templates, create forms from them, then add or overwrite code? ? (Anybody who is a Vis Fox Pro programmer would know imme...
Web forms and mobile web forms
Hey everyone, I am looking to develope a web site that depending on the device connecting to it, it will display a certain page. In this case im looking to create a web and wap version of the same project. I have the Web forms version finished using ASP.NET and C# and I am wondering how to create the mobile web form and incorporate it in the project. In the project folder, can I just add a mobile web??? But what about displaying device specific pages?? Cheers -Dudach Yes, you can add mobile web forms and controls to your Web Application project. No problem. For device/br...
How to use Select case statement to select web form controls
Hi; I don't know why I can't conceptionalize this but I want to take these if statements and put them in a select case. I am testing for multiple selection from several DDLs on my web form. If DropDownList1.SelectedIndex > -1 And DropDownList2.SelectedIndex > 0 Then If Me.CheckBox1.Checked Then ObjectDataSource1.FilterExpression = "assignee = {0} AND assignor = {1}" Else ObjectDataSource5.FilterExpression = "assignee = {0} AND assignor = {1}" End If End If If DropDownList1.SelectedIndex > -1 And DropDownList3.SelectedIndex >...
Calendar Select does not select a date if datetime value contains a time other than midnight
If you set a Calendar control SelectedDate with a DateTime where the there is a date part, and a time of 00:00:00, the calendar correctly selects that dateIf the time part is not midnight, the Calendar does not select the date.I used the DateTime.Date value to set the date from a DateTime value with non-midnight datesIs this a bug? or by quirky designIf it was easy, everybody would be doing it. I could be wrong but more like quicky design because past midnight is in the 24hour clock which is in TimeSpan.http://msdn2.microsoft.com/en-us/library/system.timespan.aspxKind regards,Gift Peddie ...
For interrupting.web form Resized by position of the div : if div can't dispaly all (such as only top part , then web form heithten the heght),resize the web form
Web Form Resized by position of the div : if div can't dispaly all (such as only top part , then web form heithten the heght),resize the web form ! Because of my pool English, if you can't see , I will explain more ! ThanksGambler is the real life I guess you mean when form grows it also grow parent div element. This is normal. If you want div to stay fixed size and rather clip the form and maybe show a scrollbar, look at overflow property in CSS.Thanks------------------------------------------------------------This posting is provided "AS IS" with no warranties, and confers no ...
How To Convert c#2005.net web template to vb2005.net Forms Template
I need Convert c#2005.net web template to vb2005.net Forms Template There are some free c# to vb converters out there which will allow you to upload a project and convert it to vb. The programming models for windows forms and web projects are very different there is no way to convert a web to windows forms project. http://www.carlosag.net/Tools/CodeTranslator/Default.aspx Silverlight-helpVb TipsSpace Coast .Net User Group Hey Ajax-y, This tool really helps all programmers. I thought I would share this wit...
Web Form embedded in another Web Form
Hi, I have programmed a Web Form which acts like a scientific calculator. I would like to embedd this calculator in the upper left hand corner of another accounting Web Form. I don't want to host the calculator on the same server as the accounting page. I.e. I want them to have distinct URLs. Classically, I would do this using frames, but is there a better way using ASP? Is there some sort of Panel which can display the webpage at a specific URL which I can just drag onto my accounting Web Form? Thanks, P1000 Frames are the only way to embed a webform inside ano...
.Net Web Forms
what is the best way to create a multi-page web insurance quote form (similar to Geico) that will insert information into a database? The examples i've seen show one form with a submit button at the bottom. I need to break this form into at least three different aspx or ascx pages. Thanks for the help. I did something similiar since I have like 7 sections to an application. The way I did it was by using the asp:panel control. When the user finished one section and clicks a button it inputs the data into the database and hides the 1st panel and shows the next one. HTH...
I am getting DateTime values out of an Access Database and I want to only use the Date part of this field. I have tried to cast the returned value to a DateTime value and then format the DateTime to only show the Date part in the toString() fcn. <asp:Label runat="server" Text='<%# getDate(DataBinder.Eval(Container, "DataItem.ACTIVE_FROM"))%>'> But I can't cast the object to a DateTime object. I have also tried using DateTime.Parse() but this gives an error saying that the format is not correct. { ...
Implementing .Net Form into an APS.net web form, can this be done
I am new here, but have been searching for a while, and may not have the correct lingo to find what I am looking for. I am tasked with implementing a .exe application that was writen vb6 then converted to .net into a new website my team is developing. The idea is to put each of the 3 different forms in this .exe application of 3 different .aspx pages. I have attempted multiple things to get this into the page and even started to just rewrite it as an ASP.net web form, but even then I can't reuse any of the code since the System.Web.UI.Page doesn't inherit the sa...
Load Text box of one web form with selected list item of list box of another web form
one help please Problem is i have two web forms 1. ForiegnkeyTest.aspx 2. foroginkeytable.aspx step 1 1 ForiegnkeyTest.aspx one web form <head runat="server"><script language="javascript" type="text/javascript"> function popWin() { var confirmWin = null; confirmWin = window.open('foroginkeytable.aspx','anycontent', 'width=455,height=435,status'); } </script> &...
windows forms in vb .net into web forms
we are creating a database driven website as part of our project. currently we have forms connecting to the database created in visual basic. these windows forms we need to convert into web forms which are asp .net compatible. can u give me an answer or show me a way. Hi, because of the difference in nature between web and win applications you'll need to reimplement at least the UI part. If you have a multitiered application in which the busines logic and data access are in different parts/assemblies you can reuse them. Grz, Kris.Read my blog. Handy Firefox plugins for web developers.Wor...
.Net Web Form & Web Service Deployment
Hi all I have written some test web services and some web form applications and deployed them to my IIS. That's fine but I now want to deploy them to my real IIS server. I have created an msi for each but the problem is I seem to only be able to deploy to the default web site. Our default web site is turned off and instead we use several other sites. Does anybody happen to if Powerbuilder 11 supports non default web sites? If not do I need to hack the msi and if so can anybody suggest a suitable tool. I have Orca but it's not very friendly. Unless of course I knew exactly w...
How to convert a chinese date to english date in web form?
Hi ASP.NET Proz, I try to convert a chinese charater date (18-一月-2008) to english date (18-Jan-2008) from a web form. Is that possible? Thanks in advance for the Proz! * .NET Proz always make impossible to possible Try this one: System.Globalization.CultureInfo nlCulture = new System.Globalization.CultureInfo("zh-CHT"); // Chinese Traditional DateTime date = DateTime.MinValue; DateTime.TryParseExact(chineseDateString, "dd-MMM-yyyy", nlCulture, System.Globalization.DateTimeStyles.None, out date); ...
How to Make Web Forms from other Web Forms
In putting together an ASP.NET application for a biz application, we have come to the point where there are three different types of basic forms which have, for the most part, been perfected. Now its is a matter of cloning those forms for various uses and modifying or adding code to suit the particular need. Copy and paste works OK, but there is a lot of changing of certain words, etc. Is there a way in ASP.NET to take these basic forms, turn them into classes or templates, create forms from them, then add or overwrite code? ? (Anybody who is a Vis Fox Pro programmer would know imme...
Web forms and mobile web forms
Hey everyone, I am looking to develope a web site that depending on the device connecting to it, it will display a certain page. In this case im looking to create a web and wap version of the same project. I have the Web forms version finished using ASP.NET and C# and I am wondering how to create the mobile web form and incorporate it in the project. In the project folder, can I just add a mobile web??? But what about displaying device specific pages?? Cheers -Dudach Yes, you can add mobile web forms and controls to your Web Application project. No problem. For device/br...
How to use Select case statement to select web form controls
Hi; I don't know why I can't conceptionalize this but I want to take these if statements and put them in a select case. I am testing for multiple selection from several DDLs on my web form. If DropDownList1.SelectedIndex > -1 And DropDownList2.SelectedIndex > 0 Then If Me.CheckBox1.Checked Then ObjectDataSource1.FilterExpression = "assignee = {0} AND assignor = {1}" Else ObjectDataSource5.FilterExpression = "assignee = {0} AND assignor = {1}" End If End If If DropDownList1.SelectedIndex > -1 And DropDownList3.SelectedIndex >...
Calendar Select does not select a date if datetime value contains a time other than midnight
If you set a Calendar control SelectedDate with a DateTime where the there is a date part, and a time of 00:00:00, the calendar correctly selects that dateIf the time part is not midnight, the Calendar does not select the date.I used the DateTime.Date value to set the date from a DateTime value with non-midnight datesIs this a bug? or by quirky designIf it was easy, everybody would be doing it. I could be wrong but more like quicky design because past midnight is in the 24hour clock which is in TimeSpan.http://msdn2.microsoft.com/en-us/library/system.timespan.aspxKind regards,Gift Peddie ...
For interrupting.web form Resized by position of the div : if div can't dispaly all (such as only top part , then web form heithten the heght),resize the web form
Web Form Resized by position of the div : if div can't dispaly all (such as only top part , then web form heithten the heght),resize the web form ! Because of my pool English, if you can't see , I will explain more ! ThanksGambler is the real life I guess you mean when form grows it also grow parent div element. This is normal. If you want div to stay fixed size and rather clip the form and maybe show a scrollbar, look at overflow property in CSS.Thanks------------------------------------------------------------This posting is provided "AS IS" with no warranties, and confers no ...
How To Convert c#2005.net web template to vb2005.net Forms Template
I need Convert c#2005.net web template to vb2005.net Forms Template There are some free c# to vb converters out there which will allow you to upload a project and convert it to vb. The programming models for windows forms and web projects are very different there is no way to convert a web to windows forms project. http://www.carlosag.net/Tools/CodeTranslator/Default.aspx Silverlight-helpVb TipsSpace Coast .Net User Group Hey Ajax-y, This tool really helps all programmers. I thought I would share this wit...
Web Form embedded in another Web Form
Hi, I have programmed a Web Form which acts like a scientific calculator. I would like to embedd this calculator in the upper left hand corner of another accounting Web Form. I don't want to host the calculator on the same server as the accounting page. I.e. I want them to have distinct URLs. Classically, I would do this using frames, but is there a better way using ASP? Is there some sort of Panel which can display the webpage at a specific URL which I can just drag onto my accounting Web Form? Thanks, P1000 Frames are the only way to embed a webform inside ano...
.Net Web Forms
what is the best way to create a multi-page web insurance quote form (similar to Geico) that will insert information into a database? The examples i've seen show one form with a submit button at the bottom. I need to break this form into at least three different aspx or ascx pages. Thanks for the help. I did something similiar since I have like 7 sections to an application. The way I did it was by using the asp:panel control. When the user finished one section and clicks a button it inputs the data into the database and hides the 1st panel and shows the next one. HTH...
Web resources about - select distinct date from datetime - asp.net.web-forms
Distinct society - Wikipedia, the free encyclopediaDistinct society (in French : la société distincte ) is a political term especially used during constitutional debate in Canada , in the second ...
Minecraft: Evicted! #3 - A Distinct Improvement! (Yogscast Complete Mod Pack) - YouTubePlaying the modded Minecraft pack Yogscast Complete, Hannah and Nilesy continue their adventure by looking for a place to call home and picking ...
Distinct lack of perspective in poseurs' protestThe world is changing exponentially. I once dated a Ukranian girl. I now have to say I dated a Crimean girl. It was so much easier when everyone ...
Claire Primrose and Kerry Johns display two distinct approaches to the Australian landscapeArt Review. Peter Haynes
What makes Quebec students 'distinct'?While Quebec students say the raging tuition debate and massive protests are about accessibility to education, its roots could also lie in the ...
Quebec to get its own distinct internet domain nameQuebec will soon enjoy distinct status in Canada and across the World Wide Web, thanks to recognition by the global Internet body.
Suicide A Disease? Psychiatrists Argue It Should Be Classified As Distinct DiagnosisShould suicide be classified as a disease? That's what a growing number of psychiatrists are arguing, suggesting that genetics and biology are ...
There are almost 19,000 distinct Android-running devices in the wildThere's always been much debate over whether or not the intense fragmentation of the Android ecosystem is a good thing, but I think the latest ...
Apple job description outlines goal to evolve Siri as a distinct, recognizable character... posted by Apple, this one looking for a creative writer skilled in character-driven dialog to “help the Siri team evolve Siri as a distinct, ...
Amazon Envisions Distinct Drone Zones for Future Delivery Fleet
The first wave of long-range commercial drones should be allowed to operate in a narrow, low-altitude band and must agree to be tracked, according ...
The first wave of long-range commercial drones should be allowed to operate in a narrow, low-altitude band and must agree to be tracked, according ...
Resources last updated: 1/8/2016 12:43:33 PM