October 19, 2010 at 8:00 am
Need to different types of input for date column.
5 formats:-
1. MM/DD/YYYY - (5/5/2010)
2. MM/DD/YY - (5/5/10)
3. M/D - (5/5)
4. mon day - (may 5)
5. month day, year - (may 5,2010)
FrontEnd - VB.net 2.0
Backend - SQL 2005
End user enter the date column to one of the above format.
We need to compare the above column to database table.
The table date column looks like '05/05/2010' ie(MM/DD/YYYY) format.
Can any one help on this..
thanks in advance..:-)
Nithi
October 19, 2010 at 8:32 am
not to sure how to get around NOT passing the year. Have never seen that before.
declare @mydate datetime
set @mydate = 'May 05 10'
select convert(nvarchar(24),@mydate,101)
October 19, 2010 at 11:55 am
Here's the page for the 'convert' function, could help:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
October 19, 2010 at 12:27 pm
Why not change the front end to get a standardized format? something like using a calendar control to get dates instead of a textbox?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 19, 2010 at 2:29 pm
He was talking about getting all sorts of formats as inputs from the front end which is why I suggested he use the front end to control the input. This really didn't have anything to do with display. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 20, 2010 at 5:33 am
Already we have the calender control in front end (vb.net).
Now my client needs to the following format for date.
(for different end users in different states)
The following formats will insert through textbox control.
we need to convert the following formats to 'MM/DD/YYYY' format. (convert funtion - format 101)
Format... sample data should be convert to
---------------- -------------- --------------------
1. MM/DD/YYYY - (5/5/2010) -> 05/05/2010
2. MM/DD/YY - (5/5/10) -> 05/05/2010
3. M/D - (5/5) -> 05/05/2010
4. mon day - (may 5) -> 05/05/2010
5. month day, year - (may 5,2010) -> 05/05/2010
October 20, 2010 at 5:59 am
As the others said, I would do the conversion in the front end and then pass the datetime value to SQL Server
A quick search brought up this for example:
http://www.dotnetspider.com/resources/4500-Convert-string-DateTime.aspx
brgds
Philipp Post
October 20, 2010 at 7:30 am
nithiyanandam-447806 (10/20/2010)
Already we have the calender control in front end (vb.net).Now my client needs to the following format for date.
(for different end users in different states)
The following formats will insert through textbox control.
we need to convert the following formats to 'MM/DD/YYYY' format. (convert funtion - format 101)
Format... sample data should be convert to
---------------- -------------- --------------------
1. MM/DD/YYYY - (5/5/2010) -> 05/05/2010
2. MM/DD/YY - (5/5/10) -> 05/05/2010
3. M/D - (5/5) -> 05/05/2010
4. mon day - (may 5) -> 05/05/2010
5. month day, year - (may 5,2010) -> 05/05/2010
Now I am confused...are you using the calendar control for the user to select dates or are you using a textbox? The only way you will ever be able to ensure that your users are entering a valid date is to use a calendar control (or some other method) of standardizing their input.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 21, 2010 at 5:14 am
Now I am using textbox input for date column
October 21, 2010 at 5:25 am
hai nithi
try to use the following format which u needed
If you use SQLSERVER, you can try the below sql statement.
SELECT CONVERT(char, DateOfBirth, 105) AS Date_Column FROM table
here are some options you can use with Convert function...
convert(char,date,N) N: the date temple
0 04 2 2005 9:06AM
1 04/02/05
2 05.04.02
3 02/04/05
4 02.04.05
5 02-04-05
6 02 04 05
7 04 02, 05
8 09:06:18
9 04 2 2005 9:06:18:857AM
10 04-02-05
11 05/04/02
12 050402
13 02 04 2005 09:06:18:857
14 09:06:18:857
20 2005-04-02 09:06:18
21 2005-04-02 09:06:18.857
22 04/02/05 9:06:18 AM
23 2005-04-02
24 09:06:18
25 2005-04-02 09:06:18.857
100 04 2 2005 9:06AM
101 04/02/2005
102 2005.04.02
103 02/04/2005
104 02.04.2005
105 02-04-2005
106 02 04 2005
107 04 02, 2005
108 09:06:18
109 04 2 2005 9:06:18:857AM
110 04-02-2005
111 2005/04/02
112 20050402
113 02 04 2005 09:06:18:857
114 09:06:18:857
120 2005-04-02 09:06:18
121 2005-04-02 09:06:18.857
126 2005-04-02T09:06:18.857
October 21, 2010 at 7:10 am
By far the easiest and only truly consistent way is to use a datepicker control. You are wanting to allow manual input of dates in just about any format. Not only do you have to try to parse or convert all these different formats you will have to validate them too. If you are not careful about letting the users use textboxes for date input you are going to end needing to support stuff like "next tuesday" or "Monday after thanksgiving". If using a textbox is non negotiable then you are in for a long road of data validation and conversions. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 21, 2010 at 8:46 am
It is the front ends responsibility to provide at least rudimentary validation of the data- you can use a date-picker, a pop-up picker for the text box, or even multiple select boxes to do this with some sort of validation involved. You could also put in a default date (such as current date) into the box to try and show the end-user what you expect.
By allowing the entry of bad data, you need to do more to fix the data, and the best place for that is the front end which can give feedback to the user if it cant figure it out without chewing up resources on the servers and network chit-chat
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply