|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Need to convert Date from "YYYY-MM-DD 00:00:00.0000" to "MM/DD/YYYY"Hello, I was asked to run a query to retrieve some data for my boss. The
query works just fine, however, my boss wants to import the data into his excel spreadsheet, and the date values as displayed in the result set in query analyzer are in the format "YYYY-MM-DD 00:00:00.0000". Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off all of the time stamp crap. I tried some CONVERT/CAST functions to no avail. This is sort of urgent, PLEASE HELP!! Thank you... RG Also, my date does need the forward slashes too (/) as in "mm/dd/yyyyy".
Thanks. RG Robert G wrote: Show quote >Hello, I was asked to run a query to retrieve some data for my boss. The >query works just fine, however, my boss wants to import the data into his >excel spreadsheet, and the date values as displayed in the result set in >query analyzer are in the format "YYYY-MM-DD 00:00:00.0000". > >Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off >all of the time stamp crap. I tried some CONVERT/CAST functions to no avail. >This is sort of urgent, PLEASE HELP!! > >Thank you... > >RG If its left in a datetime data type, the time portion will always be
returned. Instead, convert it to a string using something like the following, select convert(varchar(10), getdate(), 101) -- Show quote--Brian (Please reply to the newsgroups only.) "Robert G via SQLMonster.com" <fo***@SQLMonster.com> wrote in message news:546B8E3963E09@SQLMonster.com... > Hello, I was asked to run a query to retrieve some data for my boss. The > query works just fine, however, my boss wants to import the data into his > excel spreadsheet, and the date values as displayed in the result set in > query analyzer are in the format "YYYY-MM-DD 00:00:00.0000". > > Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim > off > all of the time stamp crap. I tried some CONVERT/CAST functions to no > avail. > This is sort of urgent, PLEASE HELP!! > > Thank you... > > RG Hell yes, thanks Brian!!! That worked like a freakin charm !! I did the
following: convert(varchar(10), [MyDateField], 101) I'd really like to thank everyone else who responded as well !!! I hope I can return the favor some day. RG Brian Lawton wrote: Show quote >If its left in a datetime data type, the time portion will always be >returned. Instead, convert it to a string using something like the >following, > >select convert(varchar(10), getdate(), 101) > >> Hello, I was asked to run a query to retrieve some data for my boss. The >> query works just fine, however, my boss wants to import the data into his >[quoted text clipped - 10 lines] >> >> RG SQL Server does not have a "date only" data type, so you will need to jump
through some hoops... Since your boss is importing the data into Excel, my first recommendation would be to leave the data as it is, and just format it appropriately in Excel. Excel can easily suppress the display of the time values, and it will properly recognize the values as date values. If that is not an option, you could try a kludge such as "SELECT CONVERT(CHAR(8), DateColumn, 112), <other columns here> FROM UnknownTable", which will output a character column formatted as 'YYYYMMDD'. Depending on how you are making this output available to Excel, Excel may or may not determine that this is a Date column, and may not provide proper sorting functionality. Show quote "Robert G via SQLMonster.com" <fo***@SQLMonster.com> wrote in message news:546B8E3963E09@SQLMonster.com... > Hello, I was asked to run a query to retrieve some data for my boss. The > query works just fine, however, my boss wants to import the data into his > excel spreadsheet, and the date values as displayed in the result set in > query analyzer are in the format "YYYY-MM-DD 00:00:00.0000". > > Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim > off > all of the time stamp crap. I tried some CONVERT/CAST functions to no > avail. > This is sort of urgent, PLEASE HELP!! > > Thank you... > > RG Hey thanks for your input. That does what you said it would, but excel
doesn't recognize it as a date format. Also, I could've parsed it in the query and eventually ended up with what I needed, but it would've required a lot more work than Brian's solution - which took like two seconds. But thanks so much, I still learned from your response. RG Jeremy Williams wrote: Show quote >SQL Server does not have a "date only" data type, so you will need to jump >through some hoops... > >Since your boss is importing the data into Excel, my first recommendation >would be to leave the data as it is, and just format it appropriately in >Excel. Excel can easily suppress the display of the time values, and it will >properly recognize the values as date values. > >If that is not an option, you could try a kludge such as "SELECT >CONVERT(CHAR(8), DateColumn, 112), <other columns here> FROM UnknownTable", >which will output a character column formatted as 'YYYYMMDD'. Depending on >how you are making this output available to Excel, Excel may or may not >determine that this is a Date column, and may not provide proper sorting >functionality. > >> Hello, I was asked to run a query to retrieve some data for my boss. The >> query works just fine, however, my boss wants to import the data into his >[quoted text clipped - 10 lines] >> >> RG -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200509/1 in addition to the other comments - you could simply change the boss's
spreadsheet column to display date format. :) Robert G via SQLMonster.com wrote: Show quote >Hello, I was asked to run a query to retrieve some data for my boss. The >query works just fine, however, my boss wants to import the data into his >excel spreadsheet, and the date values as displayed in the result set in >query analyzer are in the format "YYYY-MM-DD 00:00:00.0000". > >Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off >all of the time stamp crap. I tried some CONVERT/CAST functions to no avail. >This is sort of urgent, PLEASE HELP!! > >Thank you... > >RG > > select substring(CONVERT(CHAR(8), date, 112), 5,2) + '/'
+ substring(CONVERT(CHAR(8), date, 112), 7,2) + '/' + substring(CONVERT(CHAR(8), date, 112), 1,4) from table Archer Show quote "Trey Walpole" wrote: > in addition to the other comments - you could simply change the boss's > spreadsheet column to display date format. :) > > Robert G via SQLMonster.com wrote: > > >Hello, I was asked to run a query to retrieve some data for my boss. The > >query works just fine, however, my boss wants to import the data into his > >excel spreadsheet, and the date values as displayed in the result set in > >query analyzer are in the format "YYYY-MM-DD 00:00:00.0000". > > > >Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off > >all of the time stamp crap. I tried some CONVERT/CAST functions to no avail. > >This is sort of urgent, PLEASE HELP!! > > > >Thank you... > > > >RG > > > > > or just
select convert(varchar,date,101) from table :) bagman3rd wrote:Show quote >select substring(CONVERT(CHAR(8), date, 112), 5,2) + '/' >+ substring(CONVERT(CHAR(8), date, 112), 7,2) + '/' >+ substring(CONVERT(CHAR(8), date, 112), 1,4) >from table > >Archer > >"Trey Walpole" wrote: > > > >>in addition to the other comments - you could simply change the boss's >>spreadsheet column to display date format. :) >> >>Robert G via SQLMonster.com wrote: >> >> >> >>>Hello, I was asked to run a query to retrieve some data for my boss. The >>>query works just fine, however, my boss wants to import the data into his >>>excel spreadsheet, and the date values as displayed in the result set in >>>query analyzer are in the format "YYYY-MM-DD 00:00:00.0000". >>> >>>Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off >>>all of the time stamp crap. I tried some CONVERT/CAST functions to no avail. >>>This is sort of urgent, PLEASE HELP!! >>> >>>Thank you... >>> >>>RG >>> >>> >>> >>> I know what it is like to have a boss who is a total idiot, but you
might wantot get him/her a copy of ISO-8601 and ask why he is smarter than the entire world. I would love to hear his/her reply :) The kludge for the moron is a CONVERT() in a VIEW.  |
|||||||||||||||||||||||
Other interesting topics