A SQL-TRANSACT Question How to Return only the Date Part of a DateTime Field.

VWD 2008 Express. Visual Basic.

I have a SQL table I am querying from my aspx code behind.  The table has a column that is a datetime datatype.  I want to only return the date portion of the column.  What can I use in my SQL SELECT statement to return only the date part?  By this I mean instead of mm/dd/yyyy hh:mm:ss I just want mm/dd/yy.  I have seen the CONVERT function, but it does not seem to do the job.  Thanks for any help.


Dr. Douglas Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org
0
Chaplain
4/4/2009 1:57:11 AM
asp.net.getting-started 91979 articles. 4 followers. Follow

4 Replies
518 Views

Similar Articles

[PageSpeed] 24

select convert(varchar, getdate(), 101)

 
- William

Please mark the most helpful reply/replies as "Answer".

Give some of my PWSK modules a try.
0
whighfield
4/4/2009 2:12:47 AM

Hi, Chaplain

 To remove Time Part TRy Like this

 

declare @date as datetime
Set @date=(Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0))
Select @date

--Output
-----------------------
2009-04-03 00:00:00.000
  
RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
4/4/2009 4:18:30 AM

 Instead of

SELECT GetDate() AS A

use 

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS A


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
4/4/2009 3:49:20 PM

SELECT CONVERT(varchar(8), GETDATE(), 1) as [Your New Column in mm/dd/yy format]

Replace GETDATE() with your datetime column.


Limno

0
limno
4/4/2009 7:50:05 PM
Reply:

Similar Artilces:

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 ...

Getting Started with .net and a few questions
Hello I've done some asp and php programming along with various other languages in the past and slowing getting my head around .net.I have a small project of creating a Holiday Booking system on our Intranet and just trying to get the first part to work properly will hopefully get me sorted for the whole project. The Intranet is running on .net 2 and I prefer to use the VB code rather than C#. This is connecting to an SQL server.My initial page that I am trying to create is just for an admin user to view which dates in a table have been specified as Bank Holidays and be able to add ...

Getting parts of DateTime field
Hi everyone, I want to get year, month, day and time parts of a DateTime field in sql, Can help me? There are many datetime functions you can use. For example: You can use Year(dtColumn), Month(dtColumn) to get year and month returned in integer format. You can also  use DATEPART function to get the part you want, for example: Datepart(yyyy,dtColumn) to get year. Here is the full list from MSDN library: http://msdn.microsoft.com/en-us/library/ms174420.aspx    Limno...

How do I get the date part from datetime variable ?
How do I get the date part from datetime variable ? e.g declare @start_time datetime select @start_time = "10/31/2003" This puts date and current System time into the variable. I use this for some comparison on datetime field on database. But when I do the comparison, I want to do the comparison without time part on database field and my variable. How do I do that ? Thanks Check out the datepart function. In article <3fd10c0f$1@forums-1-dub>, <phari> says... > How do I get the date part from datetime variable ? > > e.g > declare...

get DateTime.Now.Date in sql
 Hi, Im making an c# asp.net website. I use an dataset and an gridview.  I will fill an gridview with users with the follow sql query:SELECT     UserIdFROM         StatsWHERE     LunchDate =  DateTime.Now.Date()How can I get all the users with the date of today?I have try the follow solution but it doesnt work:SELECT     UserId FROM         Stats WHERE     LunchDate =  @LunchDateIn the objectDataSource I hav...

Select all dates from SQL datetime field
I have a form that's filtering search results and porting to a gridview. The drop down is selecting from the date column of SQL, but i want it the default value to show all dates in the gridview. In a normal string field, you can just use "%", but a datetime field won't take this. What can i use to show all dates? Can you post the SQL you have currently and how the value from the ftont-end is passed to the DB?***********************Dinakar NethiLife is short. Enjoy it.*********************** <asp:SqlDataSource ID="LDSSqlDataSource1" runat="serve...

getting just the date or from DateTime field
Hi, I have used smalldatetime datatype to store my date and time values. i want to store just the date or time but the problem is it stores both the date and time. For eg, if i add the the date 03/11/2004, it also the stores the current time automatically. so the new value will be something like 03/11/2004 10:00:00 AM where i want just 03/11/2004. further problem is even though i managed to store just the date like 03/11/2004 in the database, whole date and time shows up when i display it in my pages. any help will be appreciated. thanx,-keeara g------------------ If i want to dis...

Newbie Question
I have a new project which has just started. One of the requirements is to generate XML files. Do I have to create schema files? Does anyone have a tutorial or sample code to help me get started. I have some background using XML, but not in .NET. Thx Mark How to create a new xml file with XmlDocument? More examples hereSonu Kapoor [MVP] Feel free to ask me any .NET question | Ajax Data Controls Forum Thanks, this is very helpful. What do I need to do extra to setup the schema for this xml file? Is the schema really necessary as well? Thx Mark You dont need to define any sc...

Sql SELECT returning NULL on DateTime field
Hi there ; This Problem is goin to make me crazy! I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 . I used asp.net 2.0 to insert datetime values, directly,  using sth like DateTime.Now.ToString() . i use 2 selects as follows : 1)select * from X where  Date1='8/9/2006 11:17:41 AM' 2)select * from X where  Date2='8/9/2006 11:17:41 AM' #1 is OK, but the second one returns a row with all columns set to NULL . (X is the name of the table) Thanks in advance That means no qualified record for #2 query. Are you sure...

SQL Question
I have a process start time field like 2004-01-09 05:04:25.000. I have another field that is the total time (hrs/mins) that the process will take to complete. I want to add the hrs/mins to the process start time field above, and get a "process complete time field" (same format would be ok). Any thoughts on how/best way to do? Thanks, Ted Look at the DATEADD( ) and DATEPART( ) functions. These will facilitate the calculation for you. <Ted Davidson> wrote in message news:3ffeb8a3.6895.846930886@sybase.com... > I have a process start time field like 2004-...

SQL Server Question
Forgive my incredible newbiness, but I have acquired a very basic grasp of the sql server process.  I've got a connection and I know it works, but I'm having trouble understanding how I get a value returned such that I can assign a variable to it. I'm guessing that I use SelectCommand along the lines of the following: MyDataSource.SelectCommand = "SELECT EmailAddress FROM EmailTable Where EmailID=1"  I think that line identifies the piece of data I want, but how do I return it and assign it to a variable? This doesn't seem to be the answ...

select statement to get date from datetime field... help
Hi All, Could someone please help how to compose select statement to get date only from date/time field in ms access? In sql server there is convert function but i have ms access here... Regards, Paul   You can use the Format function. See #8 in http://articles.techrepublic.com.com/5100-10878_11-6135056.htmlIf this post was useful to you, please mark it as answer. Thank you! That's a useful article Johan has linked to, but my preference is to get the whole value of the field out of Access, then use string formatting in ASP.NET to change the way it displays: http://www.m...

Problem getting focus in fields containing date and datetime
I've PB 5.0.4 on NT 4 with several applications which use different databases (Oracle, Informix and Access). My problem refers to only one of these (the Oracle one): when a field (in a datawindow or in a window) get focus, the editing size is reduced. The fact is that I have a lot of date field that crash becouse of the field size is measured. My colleagues does'n have the problem in spite of the sources are the same (but the OS is different, they have windows 95-98) Probably this is since I've installed PB 7 on my machine. Thanks Matteo Date fields under NT4 have...

get only date from a date with a datetime format
i have a problem with dates when it comes to searchingbasically, SQL searches for a date including the seconds,but i only need to date so that in any given time, it will yield a result.here's a part of my code-behind code: reqNo = RequisitionsLogic.GetReqNoByDate(currentDate, requestorCode)and here's the SQL statement created in a TableAdapter:SELECT RequisitionNoFROM RequisitionsWHERE (RequisitionDate = @RequisitionDate) AND (RequestorCode = @RequestorCode) take a look with this, don't just accept influenceshttp://thedailyarr.tkhttp://www.ftpx.com/index...

Web resources about - A SQL-TRANSACT Question How to Return only the Date Part of a DateTime Field. - asp.net.getting-started

Transact-SQL - Wikipedia, the free encyclopedia
Transact-SQL ( T-SQL ) is Microsoft 's and Sybase 's proprietary extension to SQL . SQL, the acronym for Structured Query Language, is a standardized ...

Resources last updated: 12/18/2015 9:07:51 AM