Help with Date format convert Month 1 to 01.

How can I convert 1/8/2010 to 01/08/2010. I need to capture and replace
date range like Month 1 -> 01
                      2 -> 02
                      ...
                      9 -> 09

Above is the input search parameter and the search failed because user input 
1/6/2010.
Any helps would greatly appreciated.


  DECLARE @SearchVal VARCHAR(50)
  SET @SearchVal = '1/8/2010'  --9/4/2010 should return 09/04/2010
  SELECT @SearchVal
  
Expect result:  
---------
01/08/2010
0
Utf
2/19/2010 3:59:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
1274 Views

Similar Articles

[PageSpeed] 47

try this

SELECT CAST('01/04/2010' AS DATETIME)
SELECT CONVERT(VARCHAR(20), '01/04/2010',103)

"LN" <LN@discussions.microsoft.com> wrote in message 
news:7AF19D3C-24F3-47DE-AFA5-3B07D87B4DD5@microsoft.com...
>
> How can I convert 1/8/2010 to 01/08/2010. I need to capture and replace
> date range like Month 1 -> 01
>                      2 -> 02
>                      ...
>                      9 -> 09
>
> Above is the input search parameter and the search failed because user 
> input
> 1/6/2010.
> Any helps would greatly appreciated.
>
>
>  DECLARE @SearchVal VARCHAR(50)
>  SET @SearchVal = '1/8/2010'  --9/4/2010 should return 09/04/2010
>  SELECT @SearchVal
>
> Expect result:
> ---------
> 01/08/2010 


0
Simon
2/19/2010 4:17:47 PM
Assuming the order is mdy, you can use below (remember to include the SET 
command!!!). However, a bigger question is why you are storing dates as 
varchar in the first place...

SET DATEFORMAT mdy
DECLARE @SearchVal VARCHAR(50)
SET @SearchVal =  CONVERT(varchar(50), CONVERT(datetime, '1/8/2010', 101), 
101)
SELECT @SearchVal

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"LN" <LN@discussions.microsoft.com> wrote in message 
news:7AF19D3C-24F3-47DE-AFA5-3B07D87B4DD5@microsoft.com...
>
> How can I convert 1/8/2010 to 01/08/2010. I need to capture and replace
> date range like Month 1 -> 01
>                      2 -> 02
>                      ...
>                      9 -> 09
>
> Above is the input search parameter and the search failed because user 
> input
> 1/6/2010.
> Any helps would greatly appreciated.
>
>
>  DECLARE @SearchVal VARCHAR(50)
>  SET @SearchVal = '1/8/2010'  --9/4/2010 should return 09/04/2010
>  SELECT @SearchVal
>
> Expect result:
> ---------
> 01/08/2010 

0
Tibor
2/19/2010 4:21:48 PM
Thank you Tibor.

L

"LN" wrote:

> 
> How can I convert 1/8/2010 to 01/08/2010. I need to capture and replace
> date range like Month 1 -> 01
>                       2 -> 02
>                       ...
>                       9 -> 09
> 
> Above is the input search parameter and the search failed because user input 
> 1/6/2010.
> Any helps would greatly appreciated.
> 
> 
>   DECLARE @SearchVal VARCHAR(50)
>   SET @SearchVal = '1/8/2010'  --9/4/2010 should return 09/04/2010
>   SELECT @SearchVal
>   
> Expect result:  
> ---------
> 01/08/2010
0
Utf
2/19/2010 5:19:01 PM
Reply:

Similar Artilces:

Conditional Formatting Problem #4
Below is an example spreadsheet that I’m dealing with. I have a column with “Standards” (more on that later), but I have my results in three possible ‘formats’ “Standard” Result 1 Result 2 Result 3 0.01 0.123 0.123J <0.123 1.0 0.123 0.123J <0.123 First, I want to create a conditional format that will BOLD only the cells that have just numbers in them (i.e. 0.123), and leave the other two types alone. Second, I will be comparing the cells with only numbers in them (i.e. Result 1) to the Standard and highlighting only those values that are greater to or equal to the Standard. In...

help with sorting 02-11-10
I have an excel spreasheet that I am trying to sort. Some rows are highlighted, some are not. When I select the rows I want to sort, my highlights end up in some of the rows that were not previously highlighted and some of the non-highlighted rows end up with highlights. It is not consistent. It ends up with a portion of a row highlighted and all scrambled. Anyone know what i need to do? Please help! How are the rows highlighted? Manually or by Conditional Formatting? Is there any pattern to the highlighting? Gord Dibben MS Excel MVP On Thu, 11 Feb 2010 14:47:0...

Can i use conditional formating on a cell when it contains a formula? #2
=mid(text(a1;"0000000000.00");1;1 -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi the reason for this is that thie formula returns a string and not a numeric value. Change the formula to: =--mid(text(a1;"0000000000.00");1;1) -- Regards Frank Kabel Frankfurt, Germany sit wrote: > =mid(text(a1;"0000000000.00");1;1) OR ... Change your Conditional formula to: =&quo...

Help...to many birthdays
I currently use Outlook 2000 as my contact manager. I have over 7000 contacts, most with birthdays in the birthday heading. My problem is that , when I pull up my calendar, all of the birthdays show on the calendar. There are some days when I'll get 30 birthdays showing up. Is there any way to configure the system so that the birthdays will not show up on the calendar view? Thanks for the help. Barry "Barry" <barryasman@verizon.net> wrote in message news:NKj5f.2794$fC3.1066@trndny01... >I currently use Outlook 2000 as my contact manager. I have over 7000 > ...

Selecting distinct records help please
Good Morning, I have an xml file that's generated from a database. How do I select distinct values from a field in xslt and then loop through the records and produce output. Example <registrations> <registration> <company>Awesome Printers</company> <first>John</first> <last>Smith</last> </registration> <registration> <company>Awesome Printers</company> <first>Bob</first> <last>Roberts</last> </registration> <registration> <company>Awesome Printe...

REPOST: Help managin a shared file, please? (XL2007)
In XL2007, we have a shared workbook on the network. Several of the users go into the field with laptops, so they open the file, then disconnect the network cable, close the lid and leave. The workbook's user list still shows them connected. Then when they return, they plug back in and open the file again - which adds them again as a separate user. I've seen the same user logged in on three different days! What's the best way to manage a situation like this? Should they open the file and Save As to their laptop? If so, then what's the best way to reconcile the data withou...

Help with Formula using Analysis ToolPack Function ISODD #3
Hi Peo, Thank you for assistance. The formula is fine - but is there a way can avoid manually changing the Row Index number (after ROOMS) for eac Row so that I can just copy / fill the formula down. I tried the RO function but did not get the correct answer. Further assistance appreciated. Thanks Tin� Peo Sjoblom Wrote: > > Sure, just use index as well, for the first row you can use > > =SUMPRODUCT(--(MOD(INDEX(ROOMS,1,),2)=1)) > > just change the 1 after ROOMS to whatever row you want to count > > Regards, > Peo Sjoblom > > > "Tin�&qu...

HELP: Excel changed all my hyperlinks A:
Help For some reason Excel went and changed all my hyperlinks to point to my floppy drive a:\ -WHY? Is there any way I can automatically remove the a:\ hyperlink references, manually renaming is going to take ages. Excel's replace function doesn't seem to work with hyperlinks :-( -Arska Hello Arska, Have you tried find and replace? Press ctrl+h Find what: A:\ Replace with: C:\My Documents (assuming this is where the file you want to link) Then hit Replace All or if your using xl 2002 you can go to EDIT|links then hit change source Hope this could help Jon-jon "Ars...

How do I make 11.800 convert to 800 as a number not text
I want to drop the 11. and have the cell next to it be 800. I want to then be able to use that 800 and add other numbers to it. I am having two problems, 1) the 11.800 always becomes 11.8 ( I have tried increasing the decimals) and 2) if I have a different number like for example 11.255 by using RIGHT I succeed in getting 255 but as text. Please help. Thank you all in advance --- Message posted from http://www.ExcelForum.com/ Nave, =MOD(A2, 1) * 1000 will give you the 800 from 11.8 -- Earl Kiosterud mvpearl omitthisword at verizon period net -----------------------------------------...

GHOST Item within RMS 1.3
Hi All, I am new to this newsgroup. Your insights will be appreciated. We currently have 3 registers and one server runing RMS 1.3. we also export the database to external machine which has a 3rd party application which is the middleware. The middleware acts as the interface to the webserver and uploads the data to the web server. When I pull the inventory from the RMS into Middleware application, i see items that are NOT even in the RMS. Within RMS, i have checked the inactive items as well and the item is just no where to be found. The item being shown is really a duplicate item with...

Sort by formatting
I have a massive spreadsheet I have used a finddups macro on and have changed the formatting of cells with duplicate data. I would like to be able to sort or filter the data by conditional formatting so all the suspect duplicates can easily be copied to another spreadsheet. Hunt and peck is not practical with over 50,000 rows. Is there any way to sort/filter this data? I am currently using Excel XP and Windows XP. TIA George Hi sorting by formats is not supported by Excel. I'd use a helper column to 'tag' the respective rows and use this helper column for sorting/filtering....

Eudora 6.1 to Outlook Express 6 #2
I have having trouble importing my messages from Eudora 6.1 to Outlook Express 6. When I try to do import I get a long message that says "No messages can be found in this folder or another application is running that has the required files open. Please select another folder or try closing applications that may have the files open." Anybody have any ideas? ...

How to go back from 350 worksheet to the first worksheet in 1 file
Hi.. i hv 1 question to ask. Assume that i have more than 200 worksheet into 1 excel file, so imaging that i need to go back to the first worksheet from 350 page. So how should i do? Can i add the button like 'GO BACK TO FIRST PAGE' inside the 350 page, so when i click that button, it will go back to the first page, or any better suggesstion? Thanks in advanced. Regards, Wilmos Lee Hi try right-clicking with your mosue on the sheet navigation buttons in the lower left corner of your workbook. This should open a list of all your worksheets -- Regards Frank Kabel Frankfurt, Germany ...

Blank Screen without Summary in Outlook Today Help Please
When I open my outlook today I see my summary flash just for a milisecond and then the screen is blank except for the words for the messages, tasks and calendar. Help if you can. What can I do to correct this? Thanks Steve ...

microsoft outlook email arriving with wrong date and time
Please guys everyone knows about the date settings on microsoft outlook so this fault is alittle harder than that to locate. Emails while in your own computer or sent to yourself have the correct date and time. When opened in the receiving computer it has the correct date and time. But as listed unopened in the receiving computer it has the incorrect and time. date might be the previous day or the next day. Tme has no relationo time sent. This Microsoft outlook using office 2000 or office 2007 on the same computer. Any advice appreciated. The received time is compute...

Excel using =RC[-1] format and I don't want it
On this machine only, Excel will use the format =RC[-1] when I am entering a formula on the command line by hand and click on a cell to reference it in the formula. I don't know how this got on but I would like it to go off. On my other machines, it does the old familiar A1:B2 style of cell reference. I can't even search for how to fix it since I can't determine what this is called so I can turn it off. The only reference I have seen is a button that is only effective for macro creating and I am not creating a macro, just a formula, interactively, in the cell. How ...

how to append date to table name using mk table qry
I am using a make table query to archieve data in a table. I would like to append the date / time behind the table name during the process. What is the best method for accomplishing this. On Thu, 3 Dec 2009 11:02:01 -0800, Make table query <Make table query@discussions.microsoft.com> wrote: >I am using a make table query to archieve data in a table. I would like to >append the date / time behind the table name during the process. What is the >best method for accomplishing this. Sorry, but that's a very bad idea. An archive should be stored external to t...

Excel graph 3 sets of data. 2 sets as lines 1 as column?
I have data a bit like below, I am trying to do a graph, which shows the AFR as columns for A-D, but I want 'water' and 'whole' as seperate lines to show current scores across the graph. When I do the graph, I get two columns and 1 line. Does anyone know how to get to get the two lines? A B C D AFR 0.8 0.6 0.4 0.3 Water 0.5 0.5 0.5 0.5 Whole 0.3 0.3 0.3 0.3 Hi AndyN, On the chart, select the second column, right-click, select Chart Type from the context menu, select line chart. Ed Ferrero www.edferrero.com Ed Thanks for that worked a treat, I'll remember that ...

Chart help
I have a chart that shows sales data for each company that has done work for us by month for the past year. Problem is that if there were no sales for that company for a given month, that month dosn't show in the graph. What I need to show is a graph by company for every month with a 0 sales total for that month if there were no sales. I know I could just go through and put in a zero sale for each company for any month that they did not have a sale, but... I seem to remember that there is a setting that will do this for me. Can someone tell me if I am corect and where this setting is? ...

Chart formatting #3
Is there a way to shade certain areas of a line graph? I have a simple line graph extending horizontally from point 1 to 10 on the x-axis. I would like to shade the entire background area of the graph from points 2-4 and points 6-8. Is there a way to make the graph apear that the background is grayed out between these points. Thanks Chris Chris - This has been sitting here a long time... Make a combo chart. Start by locking the Y axis scale parameters (uncheck the auto boxes). Add a series to your chart which has zero values where you want no shading and <Y axis max> where ...

Best 3 Consecutive Months
I have 10 years worth of monthly data and need to determine the best (or worst) three consecutive months for benchmarking purposes. How do I write a formula that provides me the value of the highest (or lowest) value for three consecutive months (perhaps array formula?). I want to avoid at all cost adding another column of data to the worksheet that provides a running three month average and then utilizing the MAX or MIN function to determine the value. Thanks in advance Rachel Hi, It may be possible in a single formula but nothing springs to mind so here's another ...

general questions.. please help
can i know normally do people convert text files to xml format? how can we do to convert data from text files to xml? is it possible? to generate xml from jsp is it done automatically or manually? thanks in advance! appreciate if there are any links helpful... thanks all of u! jeremy *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Hello Jeremy, > can i know normally do people convert text files to xml format? not normally by hand, but it can be done. Doing it with a program is better. > how can we do to convert data from text files to x...

Help! COUNTIF and SUMPRODUCT are eating my brain!
This *should* be easy, I guess, but I keep getting errors where I canno see a problem with my formula (Tho' admittedly I'm by no means an Exce expert). Here's my task: I have column G labeled "Tasks" and the cell values are either "Open" "Closed", "Cancelled", or blank. Then I have column L labeled "Name and the cell values are, of course, names. I'm simply trying to come u with a formula that will tally the tasks that are "Open" and of those how many are assigned to "Smith". After fruitless attempts using COUN...

12 Month Running Total
All, thanks for the advice to get me this far. previously i had asked about a 12 month rolling total, below is the SQl but for Decembers the total is wrong, can anyone suggest a fix? AC2007/Xp thanks in advance. SELECT Firm_Employee_Count_time.Financial_Month, Format([Financial_Month],'yyyymm') AS FinMon, Firm_Employee_Count_time.OccurDate, [Firm_Employee_Count_time]![OccurDate]-IIf(Right([Firm_Employee_Count_time]![OccurDate],2)=12,111,99) AS OccurPrevious, Firm_Employee_Count_time.EmployeeCount, Nz([Firm_Employee_Count_Fired]![EmployeeCount],0)*-1 AS FiredNum, ...

"Month ending" on reports is wrong
I've seen a few posts that are a couple of years old on this, but no resolustion. Is this still a bug? Or a "feature" that I am too dumb to understnad? The basic issue is that on a report, it says "Month Ending 31.12.2006" and lists the transaction for January 2007. So basically the "Month ending" is always one-off. Is there a fix for this? In microsoft.public.money, Max wrote: >I've seen a few posts that are a couple of years old on this, but no >resolustion. Is this still a bug? Or a "feature" that I am too dumb to >understnad? We ...