|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Does CONVERT update the source dataCan I use the CONVERT function to change the way a date/time is
displayed in a query without actually commiting the change to the database? Please excuse my ignorance. Further, how do I convert the date/time into mmm-yy format? Thanks SHC It depends.
Are you using CONVERT in a SELECT or an UPDATE? If you just use it in a SELECT, the stored data is not changing, only the display. If you use CONVERT in an UPDATE, it 'might' change the stored data, it depends on several factors We'll need to see your DDL and your SQL Statements. -- Show quoteHTH Kalen Delaney, SQL Server MVP <shallcoo***@afig.com.au> wrote in message news:1152840949.465484.50450@75g2000cwc.googlegroups.com... > Can I use the CONVERT function to change the way a date/time is > displayed in a query without actually commiting the change to the > database? Please excuse my ignorance. > > Further, how do I convert the date/time into mmm-yy format? > > Thanks > SHC > Its a SELECT, so thanks for clearing that up.
Not sure what you mean by DDL? The date/time data currently looks like: 2006-03-30 09:42:17.330. I just want the month and year (or year and month either is good). SHC Kalen Delaney wrote: Show quote > It depends. > > Are you using CONVERT in a SELECT or an UPDATE? > > If you just use it in a SELECT, the stored data is not changing, only the > display. > > If you use CONVERT in an UPDATE, it 'might' change the stored data, it > depends on several factors > > We'll need to see your DDL and your SQL Statements. > > -- > HTH > Kalen Delaney, SQL Server MVP > > > <shallcoo***@afig.com.au> wrote in message > news:1152840949.465484.50450@75g2000cwc.googlegroups.com... > > Can I use the CONVERT function to change the way a date/time is > > displayed in a query without actually commiting the change to the > > database? Please excuse my ignorance. > > > > Further, how do I convert the date/time into mmm-yy format? > > > > Thanks > > SHC > > shallcoo***@afig.com.au wrote:
> Its a SELECT, so thanks for clearing that up. SELECTs do not make any changes to the data.> The date/time data currently looks like: 2006-03-30 09:42:17.330. I Look up the CONVERT() function in Books Online, there are various> just want the month and year (or year and month either is good). styles you can use when converting a datetime to a character field. e.g. SELECT RIGHT(CONVERT(varchar, getdate(), 6), 6) That's really cool...
but if the OP really want a dash in there, we could just surround your solution with REPLACE: SELECT REPLACE(RIGHT(CONVERT(varchar, getdate(), 6), 6),' ','-') -- Show quoteHTH Kalen Delaney, SQL Server MVP "Chris Lim" <blackca***@hotmail.com> wrote in message news:1152842987.334913.194940@i42g2000cwa.googlegroups.com... > shallcoo***@afig.com.au wrote: >> Its a SELECT, so thanks for clearing that up. > > SELECTs do not make any changes to the data. > >> The date/time data currently looks like: 2006-03-30 09:42:17.330. I >> just want the month and year (or year and month either is good). > > Look up the CONVERT() function in Books Online, there are various > styles you can use when converting a datetime to a character field. > > e.g. SELECT RIGHT(CONVERT(varchar, getdate(), 6), 6) > My hero today!
Chris Lim wrote: Show quote > shallcoo***@afig.com.au wrote: > > Its a SELECT, so thanks for clearing that up. > > SELECTs do not make any changes to the data. > > > The date/time data currently looks like: 2006-03-30 09:42:17.330. I > > just want the month and year (or year and month either is good). > > Look up the CONVERT() function in Books Online, there are various > styles you can use when converting a datetime to a character field. > > e.g. SELECT RIGHT(CONVERT(varchar, getdate(), 6), 6) DDL is the Data Definition Language. We need to see how the table is
defined. If you've actually use the datetime datatype, it is not stored as 2006-03-30 09:42:17.330, that is only the way it is displayed by default using most tools. Datetime data is stored in an internal format that you never actually see. Convert can be used with lots of different style values to convert the datetime to a string and display lots of different ways, but mmm-yy is not one of them. I presume you have the SQL Server Books Online available. You can look up the datename function to extract the month, and then use the substring function to get the first 3 characters. Use datename to get the year, and substring to get the last two characters. Concatenate the two together. Also, take a look here: http://www.karaszi.com/sqlserver/info_datetime.asp -- Show quoteHTH Kalen Delaney, SQL Server MVP <shallcoo***@afig.com.au> wrote in message news:1152842201.466059.145410@m73g2000cwd.googlegroups.com... > Its a SELECT, so thanks for clearing that up. > > Not sure what you mean by DDL? > > The date/time data currently looks like: 2006-03-30 09:42:17.330. I > just want the month and year (or year and month either is good). > > SHC > > > Kalen Delaney wrote: >> It depends. >> >> Are you using CONVERT in a SELECT or an UPDATE? >> >> If you just use it in a SELECT, the stored data is not changing, only the >> display. >> >> If you use CONVERT in an UPDATE, it 'might' change the stored data, it >> depends on several factors >> >> We'll need to see your DDL and your SQL Statements. >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> >> >> <shallcoo***@afig.com.au> wrote in message >> news:1152840949.465484.50450@75g2000cwc.googlegroups.com... >> > Can I use the CONVERT function to change the way a date/time is >> > displayed in a query without actually commiting the change to the >> > database? Please excuse my ignorance. >> > >> > Further, how do I convert the date/time into mmm-yy format? >> > >> > Thanks >> > SHC >> > > try..it....
select convert(char(7),getdate(),120) --yyyy-mm select convert(char(7),getdate(),111) --yyyy/mm select convert(char(5),getdate(),11) -- yy/mm <shallcoo***@afig.com.au> ??????:1152840949.465484.50***@75g2000cwc.googlegroups.com... Show quote > Can I use the CONVERT function to change the way a date/time is > displayed in a query without actually commiting the change to the > database? Please excuse my ignorance. > > Further, how do I convert the date/time into mmm-yy format? > > Thanks > SHC > These are perfect. How to I relate it to my stored dates under
Deadline_Date? Thanks SHC cn.popeye wrote: Show quote > try..it.... > > select convert(char(7),getdate(),120) --yyyy-mm > select convert(char(7),getdate(),111) --yyyy/mm > select convert(char(5),getdate(),11) -- yy/mm > > > <shallcoo***@afig.com.au> > ??????:1152840949.465484.50***@75g2000cwc.googlegroups.com... > > Can I use the CONVERT function to change the way a date/time is > > displayed in a query without actually commiting the change to the > > database? Please excuse my ignorance. > > > > Further, how do I convert the date/time into mmm-yy format? > > > > Thanks > > SHC > >  
Other interesting topics
|
|||||||||||||||||||||||
