Re: convert(datetime, datefld, 101) error
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 01/08/05
- Next message: Gert-Jan Strik: "Re: SQL Server 2000 BUG?"
- Previous message: sriehl_at_removethis.insightbb.com: "drop multiple columns...then what?"
- In reply to: Ron: "Re: convert(datetime, datefld, 101) error"
- Next in thread: MGFoster: "Re: convert(datetime, datefld, 101) error"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 8 Jan 2005 08:40:40 -0800
If the data is stored as varchar and you know all the data is in the same
format, AND all you want is the year, yep, the last two characters can be
extracted with the right() function.
-- HTH ---------------- Kalen Delaney SQL Server MVP www.SolidQualityLearning.com "Ron" <anonymous@discussions.microsoft.com> wrote in message news:0f6501c4f551$c9d0ffd0$a301280a@phx.gbl... > datefld is a varchar field in a sql server 2000 table, > tbl1. All the fields in tbl1 are varchar fields because > the table picks up raw data. The problem is that the > date values (which are in varchar format) can come in as > 1/1/2004 or 1/1/04. I want to see all the groups of > years that I have picked up. The other reply suggested > Right(datefld, 2). That seemed quite simple. Is it > really this easy? > > Thanks, > Ron > >>-----Original Message----- >>Hi Ron >> >>It's a bit confusing exactly what you are trying to do > here. Is the data >>already in a SQL Server table? In a column of type > datetime? If so, then the >>year isn't stored as 04, or 2004, the entire date is > stored in an internal >>format that is completely unambiguous. It's only > converted into a more >>recognizable format when you select it. >> >>You say you just want to select the year. If the data is > stored as datetime, >>you can try: select year(datefld) >> >>Also note that in general, using the format parameter in > teh convert >>function is used when you convert a datetime to a > character string. Once you >>convert to a datetime, SQL Server will always display > datetimes in a format >>dictated by certain regional settings. >> >>Datetime data can be quite tricky to work with. Again, > if you tell us what >>you have in terms of table structure, and exactly what > you want to do, I'm >>sure we can find a solution. >> >>Also, please read about DATETIME data in the Books > Online. >> >>-- >>HTH >>---------------- >>Kalen Delaney >>SQL Server MVP >>www.SolidQualityLearning.com >> >> >>"Ron" <anonymous@discussions.microsoft.com> wrote in > message >>news:127701c4f518$a911c1f0$a401280a@phx.gbl... >>> Well, here is one thing I tried that seems to work >>> >>> Select year(convert(datetime(Replace >>> (datefld, '/04', '/2004'), 101)) from tbl1 >>> >>> The only problem is that I can only select years for >>> 2004. Any suggestions are welcome on how I could > extend >>> this to work for all years. Ideally, I am looking for > a >>> format function like >>> >>> select year(convert(datetime, format >>> (datefld, 'mm/dd/yyyy'),101)) from tbl1 >>> >>> Thanks, >>> Ron >>> >>>>-----Original Message----- >>>>Hello, >>>> >>>>I read external data to a table that is all text fields >>>>(will take any text). One field is supposed to contain >>>>dates, like 1/1/2004, 1/2/04, 1/3/2004, >>>>1/4/04, ...1/1/2005, 1/1/05, 1/2/2005, 1/3/05, ... >>>> >>>>I want to just read the year value and am using >>>> >>>>select year(convert(datetime, datefld, 101)) from tbl1 >>>> >>>>The ID 101 of the convert function is supposed to > handle >>>>the style mm/dd/yy. But this works only for date > formats >>>>of 1/1/2004, 1/1/2005. If I read a value of 1/2/04, >>>>1/2/05 I get this error: >>>> >>>>"Syntax error converting datetime from character > string." >>>> >>>>I want to group on the years of these dates. My > question >>>>is if there is a way to manipulate my data with sql >>>>functions or do I need to update my table so that all > the >>>>values like 1/1/04 are updated to 1/1/2004 - first? >>>> >>>>If there is a way to manipulate this data with sql >>>>functions without having to alter the actual data so > that >>>>I can use the Select statement, may I ask what that > would >>>>be? >>>> >>>>Thanks, >>>>Ron >>>>. >>>> >> >> >>. >>
- Next message: Gert-Jan Strik: "Re: SQL Server 2000 BUG?"
- Previous message: sriehl_at_removethis.insightbb.com: "drop multiple columns...then what?"
- In reply to: Ron: "Re: convert(datetime, datefld, 101) error"
- Next in thread: MGFoster: "Re: convert(datetime, datefld, 101) error"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
