incorrect syntax near key word AS

Hi Experts

whats wrong with the following - kindly

trim(left(CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) AS [fos_datetime_new_op],255)),

I am getting the error message as per subject box (#156)
route217JuniorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, the alias goes at the very end of the expression, and you have it within the LEFT.
trim(left(CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime),255)) AS [fos_datetime_new_op],

Open in new window

0
route217JuniorAuthor Commented:
thanks for the feedback...
0
route217JuniorAuthor Commented:
ok now gettinng trim is not a reconginzed builtin function name.....
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

route217JuniorAuthor Commented:
i am running the t-sql via access 2003...
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Correct.  Access has Trim(), T-SQL only has left trim LTRIM() and right trim RTRIM().

If you need both, it's LTRIM(RTRIM(your_value)).
0
route217JuniorAuthor Commented:
thanks jim

whats wrong with
LTRIM(CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime),255)) AS [fos_datetime_new_op],

error message the ltrim function requires 1 argument(s) (#174)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The above expression has an extra right parenthesis mark ) that you'll need to remove.  
Also, explain what the ,255 is for, as it appears to be an orphan from removing the LEFT.
0
tigin44Commented:
use this

LTRIM(CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime)) AS [fos_datetime_new_op]
0
PortletPaulfreelancerCommented:
There is a lot going on in that single line

convert {datetime to varchar(8) using style 112)
cast {the above to datetime}
ltrim {a datetime, but ltrim is for strings not datetime}


If you want 8 characters, and only 8 characters, derived from a datetime field all you need is:

CONVERT(varchar(8), [fos_datetime_new_op], 112)

e.g. from a datetime value visible as:
29 August 2013 10:23:36 +12345
would become the string of:
'20130829'
nb: not a number and not a date, it is a string
and there is no need to use cast, or ltrim - IF this is what you are expecting.

If this is not what you are expecting as a final result could you list a few datetime values, and then list the expected outcome of each, plus the data type wanted as output.
0
route217JuniorAuthor Commented:
PortletPaul

spot on....this is what i am expecting....

re: would become the string of:

'20130829'


nb: not a number and not a date, it is a string

and there is no need to use cast, or ltrim - IF this is what you are expecting.

whats the final result and apologies if the question was poorly worded..
0
PortletPaulfreelancerCommented:
idea 2:

Are you trying to produce a datetime value but with time reduced to 00:00:00 ?

The fastest way to do this is:

dateadd(day, datediff(day,0, [fos_datetime_new_op] ), 0)

this 'truncates' a datetime to the same date but at 00:00:00 as time

it works this way:
the number of days (integer) from 1900-01-01 is calculated
that integer number of days is then added to 1900-01-01 00:00:00
= the same date but at time 00:00:00

this approach is faster than cast/convert involving varchars
0
PortletPaulfreelancerCommented:
>>apologies if the question was poorly worded..
not at all, it's sometimes v.hard

to confirm: for the 8 character string which represents a date, all you need is:

CONVERT(varchar(8), [fos_datetime_new_op], 112)
0
route217JuniorAuthor Commented:
All

wheather i use:
1. LTRIM(CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime)) AS [fos_datetime_new_op]

or

2. CONVERT(varchar(8), [fos_datetime_new_op], 112)

i still get the error "the conversion of a varchar data type to a datetime data type resulted in an out-of-range value....

i.e. greather then 255 char.....
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just for kicks and giggles, what is the data type of [fos_datetime_new_op], and give us some examples.  

If we're ultimately doing a CAST( as datetime) on it, any value that is not correctly formatted as a date will result in a "the conversion of a varchar data type to a datetime..." error.
0
PortletPaulfreelancerCommented:
can we see more of your query please?
0
PortletPaulfreelancerCommented:
2. CONVERT(varchar(8), [fos_datetime_new_op], 112)

i still get the error "the conversion of a varchar data type to a datetime data type resulted in an out-of-range value
This would indicate [fos_datetime_new_op] is not date or datetime

as Jim says; can you verify the datatype of that field?
0
PortletPaulfreelancerCommented:
apologies, should also ask: what version of sql server is it?
0
route217JuniorAuthor Commented:
Qry below....

SELECT 

snapshot_date, 
contact_id,
demand_type,
valid_case_ind,
date_in_bank_datetime,
CAST([date_in_bank_dateonly] AS DATETIME) AS [date_in_bank_dateonly],
date_in_cr_datetime,
CAST([date_in_cr_dateonly] AS DATETIME) AS [date_in_cr_dateonly],
case_created_datetime,
CAST([case_created_dateonly] AS DATETIME) AS [case_created_dateonly],
case_status,
Workstream,
product_01,
product_02,
product_03,
product_04,
cal_days_to_cr,
bus_days_to_cr,
cal_days_to_log,
bus_days_to_log,
case_created_hour,
CAST([init_dateonly_closed] AS DATETIME) AS [init_dateonly_closed],
init_tr_calls,
init_tr_contacts,
init_tr_calls_ind,
init_cal_days_os_bank,
init_bus_days_os_bank,
init_cal_days_os_cr,
init_bus_days_os_cr,
init_cal_days_os_created,
init_bus_days_os_created,
reopen_datetime,
CAST([reopen_dateonly] AS DATETIME) AS [reopen_dateonly],
reopen_hour,
reopen_und_calls,
reopen_und_contacts,
reopen_und_calls_ind,
reopen_und_contacts_ind,
reopen_cla_calls,
reopen_cla_contacts,
reopen_cla_calls_ind,
reopen_cla_contacts_ind,
reopen_cal_e2e_bank_closed,
reopen_cal_e2e_cr_closed,
reopen_bus_e2e_bank_closed,
reopen_bus_e2e_cr_closed,
reopen_cal_e2e_created_closed,
reopen_bus_e2e_created_closed,
paf_outcome,
paf_final,
paf_date,
CAST([ack_breach_dateonly] AS DATETIME) AS [ack_breach_dateonly],
ack_breach,
ack_breach_backdated,
CAST([four_week_breach_dateonly] AS DATETIME) AS [four_week_breach_dateonly],
four_week_breach,
four_week_breach_backdated,
CAST([eight_week_breach_dateonly] AS DATETIME) AS [eight_week_breach_dateonly],
eight_week_breach,
eight_week_breach_backdated,
CAST([ack_potential_dateonly] AS DATETIME) AS [ack_potential_dateonly],
CAST([four_week_potential_dateonly] AS DATETIME) AS [four_week_potential_dateonly],
CAST([eight_week_potential_dateonly] AS DATETIME) AS [eight_week_potential_dateonly],
qc_first_decision,
qc_first_decision_rating,
qc_first_decision_area,
qa_first_decision_rating,
qa_first_decision_area,
qa_first_decision_overturned,
qc_second_decision_overturned,
qa_second_decision,
qa_second_decision_overturned,
CAST(CONVERT(varchar(8), [fos_datetime_new_op], 112) AS datetime) AS [fos_datetime_new_op],
fos_dateonly_new_omb,
CAST(CONVERT(varchar(8), [fos_datetime_closed_op], 112) AS datetime) AS [fos_datetime_closed_op],
fos_dateonly_closed_omb,
fos_cal_days_to_close_op,
fos_cal_days_to_close_omb,
fos_bus_days_to_close_op,
fos_bus_days_to_close_omb,
fos_os_cal_day_op,
fos_os_cal_day_omb,
fos_os_bus_day_op,
Last_Updated,
original_group,
current_group,
Contact_Reference,
Status_Updated,
week_breach_4,
Breach_8_Date,
CAST([Breach_Ack_Dateonly] AS DATETIME) AS [Breach_Ack_Dateonly],
CAST([Breach_Area_Dateonly] AS DATETIME) AS [Breach_Area_Dateonly],
breached_1,
closed_non_reportable,
CR_Action_Taken,
cr_summary,
cust_req_amount,
date_handedin,
date_fos_closed,
executive_name,
Final_Desc_01,
Final_Desc_02,
FOS_Invoice,
Handed_In_From,
High_Profile,
Hot_Topic

FROM  tbl

Open in new window

0
route217JuniorAuthor Commented:
field in my access table is date/time...in the format...

19/03/2012 23:05:59
0
route217JuniorAuthor Commented:
Jim

quote...
If we're ultimately doing a CAST( as datetime) on it, any value that is not correctly formatted as a date will result in a "the conversion of a varchar data type to a datetime..." error.

what would be the alternative...????
0
route217JuniorAuthor Commented:
version of sql "Shouldn’t make a difference. Access is connecting to the SQL database"...

hope this helps
0
PortletPaulfreelancerCommented:
>>what would be the alternative...????
will depend on:
a. what the data type actually is
b. what values are stored in that column

lets say it's a varchar(200) data type
and somewhere there is a value stored like

'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

that stings of x's will not convert into a datetime no matter how hard you try.
0
route217JuniorAuthor Commented:
Hi

one question could i be missing something in the objects library???
0
PortletPaulfreelancerCommented:
>>could i be missing something in the objects library???
I don't think that is relevant to be honest
The symptom is this:

the field isn't datetime
the field holds values that cannot be converted to datetime
the error message is coming from SQL Server, not Access

please try this query on that table and provide the result

select
  count(*)
, sum(isdate([fos_datetime_new_op]))
from tbl
0
route217JuniorAuthor Commented:
PortletPaul

the result of the above qry are

a table showning the following:

Expr1000           Expr1001
         4781                  127
0
PortletPaulfreelancerCommented:
well that is NOT great for you!

of the 4781 records in that table
ONLY 127 are recognized as date/time by the function ISDATE()

Your choices include:
a. just import the field "as is" (probably varchar or nvarchar)
b. convert 127 records to datetime, the rest as NULLs
c. do much more investigation on the contents of [fos_datetime_new_op]

also note:
    something similar probably also applies to field [fos_datetime_closed_op]

do please note we only have the facts sent to us via this question, we are unable to tell you what values are stored in the fields of that table.

and we now know the SQL Server version is 2005+ (isdate didn't exist in 2000)


here are some other queries that might assist you

select
  count(*) as all_recs
, sum(isdate([fos_datetime_new_op])) as new_op_dates
, max(len([fos_datetime_new_op])) as new_op_max_len
, sum(isdate([fos_datetime_closed_op])) as closed_op_dates
, max(len([fos_datetime_closed_op])) as closed_op_max_len
from tbl
;

select top 10 -- for 10 rows, change this number to suit yourself
  [fos_datetime_new_op]
, [fos_datetime_closed_op]
from tbl
where   isdate([fos_datetime_new_op]) = 0
and  isdate([fos_datetime_closed_op]) = 0 -- change to OR if no results
;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
route217JuniorAuthor Commented:
a. just import the field "as is" (probably varchar or nvarchar)
b. convert 127 records to datetime, the rest as NULLs

is this something the sql guys need to do???
0
PortletPaulfreelancerCommented:
they are options, you will have to decide who to discuss this with onsite

my guess is "the sql guys" will be able to help you a lot because they should know what values are stored in those fields.

a. could also read as:

just QUERY the field "as is"

e.g.

select
... all the other fields, then those troublesome fields "as is"
  [fos_datetime_new_op]
, [fos_datetime_closed_op]
from tbl
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the assist.  I think you owe PortletPaul a beer for all his speedy help.  -Jim
0
PortletPaulfreelancerCommented:
Ditto (thanks for the grading). Good Luck with this endeavour. Cheers Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.