Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Forums » .NET » ASP.NET »
datepicker - sql server
Posted Date: 07 Jan 2009 Posted By: gunapandian Member Level: Silver Points: 1
Responses:
1
|
hi friends,
The below query excuted but it retrieve the record when date format is '01/02/2008'.
The problem is, when i choosing the date in datepicker, it takes as '1/2/2009'format. so i not able to retrieve the record.but it contains the record in db.
Please suggests whats the problem...
select jobno,chapterno,author_corr,proof_corr,process,complexity,started_at,ended_at,CONVERT(varchar, DATEADD(s, duration, 0), 108)as duration,total_pages from tasksheet_college
where substring(started_by,4,7)='165' and convert(varchar,started_at,101) ='1/2/2009'
|
Responses
|
| Author: BrianP 07 Jan 2009 | Member Level: Gold | Rating:  Points: 6 |
You can either format the selected started_at date in your code or in the SQL:
1. To format the selected date in your code using the asp.net calendar control:
string selectedStartedAt = String.Format("{0:MM/dd/yyyy}", calendar.SelectedDate);
2. If you are using the calendar control from the ajax toolkit, try setting the Format property: Format="MM/dd/yyyy"
If you prefer to do all of the conversions in your SQL proc, here is a user-defined function for formatting dates:
CREATE FUNCTION dbo.udfFormatDate
(
@Datetime DATETIME,
@FormatMask VARCHAR(32)
)
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @StringDate VARCHAR(32)
SET @StringDate = @FormatMask
IF (CHARINDEX ('YYYY',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'YYYY',
DATENAME(YY, @Datetime))
IF (CHARINDEX ('YY',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'YY',
RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX ('Month',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'Month',
DATENAME(MM, @Datetime))
IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
SET @StringDate = REPLACE(@StringDate, 'MON',
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
IF (CHARINDEX ('Mon',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'Mon',
LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX ('MM',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'MM',
RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX ('M',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'M',
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX ('DD',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'DD',
RIGHT('0'+DATENAME(DD, @Datetime),2))
IF (CHARINDEX ('D',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'D',
DATENAME(DD, @Datetime))
RETURN @StringDate
END
---- end user-defined function
Change your SQL to:
select jobno,chapterno,author_corr,proof_corr,process,complexity,started_at,ended_at,CONVERT(varchar, DATEADD(s, duration, 0), 108)as duration,total_pages from tasksheet_college where substring(started_by,4,7)='165' and dbo.udfFormatDate(started_at,'M/D/YYYY') ='1/2/2009'
|
| Post Reply |
|
|
|
 | | This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post. |
|
|
|
|