Re: convert(datetime, datefld, 101) error
From: Ron (anonymous_at_discussions.microsoft.com)
Date: 01/08/05
- Next message: Ron: "Re: convert(datetime, datefld, 101) error"
- Previous message: Joel Leong: "How to insert at a specific node in a Tree / Hierarchy using T-SQL"
- In reply to: Kalen Delaney: "Re: convert(datetime, datefld, 101) error"
- Next in thread: Kalen Delaney: "Re: convert(datetime, datefld, 101) error"
- Reply: Kalen Delaney: "Re: convert(datetime, datefld, 101) error"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 7 Jan 2005 23:15:10 -0800
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: Ron: "Re: convert(datetime, datefld, 101) error"
- Previous message: Joel Leong: "How to insert at a specific node in a Tree / Hierarchy using T-SQL"
- In reply to: Kalen Delaney: "Re: convert(datetime, datefld, 101) error"
- Next in thread: Kalen Delaney: "Re: convert(datetime, datefld, 101) error"
- Reply: Kalen Delaney: "Re: convert(datetime, datefld, 101) error"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
