Formatting date SQL date to remove time

Hi,

I need a way of changing the following SQL statement so that the dates are without the hh:mm:ss tt:

"Select DISTINCT([StartDate]) From [Events]"

How can this be done?

Thanks,
Curt.


Regards, Curt
0
CurtWRC
5/2/2008 11:32:20 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
972 Views

Similar Articles

[PageSpeed] 59

SELECT CONVERT (VARCHAR, [StartDate], 101) FROM ...

For other date formatting types, see http://msdn2.microsoft.com/en-us/library/ms187928.aspx


Mark replies as answers if they helped you solve the problem.
0
siva_sm
5/2/2008 11:48:08 AM

use the following Conversions

Convert("urdatecolumn",Varchar,101);

and there are more codes for the dateconversions.Its general Syntax is

Convert("urdatecolumn",Varchar,NumberCode);

Mark As Answer if this helps you

Further Queries Recommended

Happy CodingWink


Regards,
B.ShivaKarthik
Developer
Hyderabad,India

-------------------------------------------------
Don't Forget to Mark as Answer for the post that helped you.
-------------------------------------------------
0
ShivaKarthik
5/2/2008 12:32:23 PM

Thanks for your replies. I tried this first:

"Select CONVERT(VARCHAR, [StartDate], 101) From [Events]"

But I got this error:

'These columns don't currently have unique values'

Then I tried this:

"Select DISTINCT(CONVERT(VARCHAR, [StartDate], 101)) From [Events]"

I didn't get an error but it also didn't work properly.

Where have I gone wrong?

Cheers,
Curt


Regards, Curt
0
CurtWRC
5/6/2008 9:40:47 AM

Try this one

Convert(Varchar,StartDate,101)

Mark As Answer if this helps you

Further Queries Recommended

Happy CodingWink


Regards,
B.ShivaKarthik
Developer
Hyderabad,India

-------------------------------------------------
Don't Forget to Mark as Answer for the post that helped you.
-------------------------------------------------
0
ShivaKarthik
5/6/2008 11:25:27 AM
Reply:

Similar Artilces:

date time format of sql server
I am developing appliction which is deployed on server whose regional settings changed to norwegian language. I have orderform in which we are inserting date like DateTime.Now.ToString() . but I am getting error like 'The conversion of a char data type to a datetime data type resulted in anout-of-range datetime value.' whether  I have to make change to culture settings in web.config ? Also whether I have to make convert date format.  Regards,Mahesh--------------------click "Mark as Answer" on the post that helps you.   In sql server you can always inse...

Convert Remedy ARSystem Date in SQL Server to short date format
Hi: Does anyone know how to convert Remedy ARSystem Date in SQL Server to short date format compensating for EST and Daylight savings. Thank you in advance!  Here is the SQL 2005 solution but I need this to work in .NET as I can not add stored procs to the proprietary database.   Any ideas on the syntax for VB.NET? SELECT DATADD(day, myepochvalue - 2361331, '17530101') as TimeFrame See:http://www.nabble.com/Integer-date-conversion-in-Business-Object-WebIntelligence-td6554394.html  This is what finally worked in VB.NET Public Class ConvertEpoc...

Dates, Dates, Dates!
Hello all... I am in the process of building an appointment system, and I am trying to see if I can get some pointers on which way to go on this. The first version of this appointment system was very lo-tech, and it stored all of the possible dates/times in the database. Once a user booked an appointment, their information was logged to that date/time, and then that date/time would no longer show in the list of possible openings. This was great for a temporary fix, but with having to pre-populate all of the dates/times, the system would eventually have to be updated to continue on pa...

SQL Stored Procedure
Hi hopefully someone can help. I created the following function to return all dates between a start date and end date:  ALTER FUNCTION fnGetDatesInRange ( @FromDate datetime, @ToDate datetime ) RETURNS @DateList TABLE (Dt datetime) AS BEGIN DECLARE @TotalDays int DECLARE @DaysCount int SET @TotalDays = DATEDIFF(dd,@FromDate,@ToDate) SET @DaysCount = 0 WHILE @TotalDays >= @DaysCount BEGIN INSERT INTO @DateList SELECT (@ToDate - @DaysCount) AS DAT SET @DaysCount = @DaysCount + 1 ...

date/time or date and time
I am setting up an SQL database and I will need to get differences in dates. For example I have a start date, start time, completion date and completion time and I want to get the difference between the start and completion. Would it be better to have one field with both date and time in it, or better to have a date field and a time field? Even though I have already started setting up the tables with seperate fields for date and time I am now leaning toward one field with date/time in it. (Only because that is the way I had to do it when setting up an Excel spreadsheet for a similar ta...

Date Times into MS-SQL server database using .Net
I keep getting errors when trying to input a date time string into an MS SQL Server database and i'm begining to loose patience with it. My field is set up as "datetime" field with a length of 8 (bits ?) My sql string to input into that database is tehn concatonated like so (using sql and vb.net).... mystring = "UPDATE tablename SET mydatefield='" & DateTime.Now & "' WHERE id=" & frmid I keep getting the following error.... The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statem...

How to get todays date in format YY/MM/DD and to compare it to another date passed into the sql
I need to do the following and am hoping someone can help me out. I have C#(asp.net app) that will call a stored procedure. The C# will pass in a date to thestored procedure. The date is in the format YY/MM/DD. Once inside of the stored procedure, the datepassed into the stored proc needs to be compared to todays date. Todays date must be determined inthe SQL. So basically here is my pseudo code for what I am trying to accomplish. Basically I just am afterthe comparison of the two values: If @BeginDate < TodaysDate The difficult part is how to obtain the value for "TodaysDate&qu...

Strange Date format behavior when selecting by date, using MS SQL server 2005 express edition
In my local desk machine, regional settings for date have the following format    dd/mm/yyyy (its the standard im my country) In my application using MS SQL server 2005 express edition, when I try do recover records using a date as parameter, the only way to recover the right records  is modifying the date format so that de original values in the format  of dd/mm/yyyy is changed to mm/dd/yyyy For example the right date is   13/09/2008 (dd/mm/yyyy)  (all the records where added the table using this date in this forma...

SQL Date Format
Hello!I inserted data to dbase, but I soon got error message.. Europien date format is dd.mm.yyyy So I get date from text box:<asp:textbox id="date" style='Z-INDEX: 102; LEFT: 411px; POSITION: absolute; TOP: 42px' runat='server' Width='185px' Height='22px'>Date</asp:textbox>Date which is get as string is inserted to dbase by code behind:string insert = "INSERT INTO tbl_UFD_PlanPremier (DAT_Prem) VALUES ('"+date.Text+"')";I thought that that will work ok, but.. By date 1.1.2006 was everithing ok, problems begann w...

SQL format date.
Hi all, I have the SQL statement: "SELECT OrderID, OrderDate FROM tblOrders WHERE OrderDate < #" & FormatDateTime(Now(), DateFormat.ShortDate) & "#" This is accessing an MS-Access database. OrderDate is of field type Date, General Date. I need it to check the short date portion only. How do I do this? Cheers, David What you have should work fine. MS Access DateTime fields always store the date and the time; but if you're finding dates before today, the time won't matter. AutoFed Hi, Oops... sorry, the SQL should be EQUAL not less...

SQL TIME AND DATE
Hi  I have created a simple SQL database with four columns using Microsoft SQL Server Mangement Studio Express  - (Name(varchar), Email (varchar), Comment (varchar), Datetime). The idea is for this database to hold feedback and comments from my website by connecting a form view with insert capabilities to this database. How can I configure the Datetime column in the database to automaticaly input the time and date from when the user posted the comment?   Thanks  To set the date/time automatically, make the column non-null and give it a default value of GetDate()...

Seperate Date and Time Merge; done at SQL Update or C# .NET then Update
I do realize that his could be posted in a few spots but I think the answer is in the SQL. I have a ASP.NET page, with a SqlDataSource, Text Box and Calendar Controls. I have the textbox and calendar controls eval'ed to the same sql data source DateTime Field. The text box is formatted eval to small time and the calendars eval has no formatting. ex: <asp:TextBox ID="START_TIME" Text='<%# Eval("EVENT_START","{0:t}") %>' runat=server Width=200></asp:TextBox> I want to merge the two controls; one has the date the other has the time when I update the pages data to the SqlDa...

DataSource.DataSet.RecordCount causes SQL error on dutch pc (wrong date format)
Hi All, Normaly I use ''SET DATEFORMAT dmy ........" in my SQL code. But I found out that DataSource.DataSet.RecordCount uses this line "SELECT COUNT(*) FROM Table .........." The problem is that on an English windows pc the DataSource.DataSet.RecordCount execute normally but on a Dutch pc the DataSource.DataSet.RecordCount causes a SQL error. (wrong date format) Now I am using this code GetRecordCount(DataSource.DataSet) instead of DataSource.DataSet.RecordCount // GetRecordCount // -------------------------------------------------------------------...

How to convert date-time format to Oracle Date Format DD-MM-YY?
I get a resultset through Jaguar from Oracle7. The resultset is forwarded to query object which bounds to grid object. Things works fine with datatype being NUMBER, VARCHAR2. But if I have a colmun whose datatype is "Date" and is formatted as "DD-MON-YY" where "MON" is from "Jan" to "Dec". What I will see from my grid display is: "YYYY-MM-DD:HH:MM:SS.SS". So how can I convert this date-time format to be displayed as an Oracle recogniziable Date format (DD-MM-YY)? This conversion also prevents a problem of updating s...

Web resources about - Formatting date SQL date to remove time - asp.net.sql-datasource

Wikipedia:Manual of Style/Text formatting - Wikipedia, the free encyclopedia
The most common use of boldface is to highlight the article title, and often synonyms, in the lead section (first paragraph). This is done for ...

Formatting « Above the Law: A Legal Web Site – News, Commentary, and Opinions on Law Firms, Lawyers ...
Above the Law: A Legal Web Site – News, Commentary, and Opinions on Law Firms, Lawyers, Law School, Law Suits, Judges and Courts

Android Studio 1.2 Reaches Beta, Adds Built-In Decompiler, Inline Debugger Variables, Smarter Formatting ...
When Android Studio v1.1 entered the Stable channel, about 6 weeks ago, the Dev Tools team gave word that v1.2 was already well underway and ...

TextExpander 2.0 Brings Rich Text Formatting, Fill-In Snippets And More
TextExpander has finally received its big 2.0 update.

WordEver HD - Text Editor with Redesigned Keyboard and MarkDown syntax & formatting highlight
Holen Sie sich „WordEver HD - Text Editor with Redesigned Keyboard and MarkDown syntax & formatting highlight“ im App Store. Sehen Sie sich Screenshots, ...

Macs are vulnerable to Thunderstrike 2 firmware malware that survives formatting
... Thursday, researchers will demonstrate that not only can Macs be remotely infected with malware, but that this malware can survive a user formatting ...

16 Tips on Blog Writing and Formatting
... again. My point is that you ought to take advice with a grain of salt. And that applies to this post as well. Writing blog posts and formatting ...

Select Text With Similar Formatting in Google Docs
Google Docs added a cool new feature that lets you select all the text with similar formatting, so you can quickly make some changes. It's like ...



Resources last updated: 1/10/2016 4:41:53 AM