Conversion failed when converting datetime from character string

Hello:

I get the following error message in SQL 2005 Management Studio:
Conversion failed when converting datetime from character string.

I get this when I run a "select * from" query against a view called 
"DEBITMEMOGLFISCALYEARS".  Here is the programming of this view:

ALTER VIEW [dbo].[DEBITMEMOGLFISCALYEARS] AS
SELECT CASE WHEN GLPOSTDT BETWEEN '2007-03-01 00:00:00.000' AND '2008-02-29 
00:00:00.000'
THEN '2007' WHEN GLPOSTDT BETWEEN '2008-03-01 00:00:00.000' AND '2009-02-28 
00:00:00.000' THEN '2008' WHEN
GLPOSTDT BETWEEN '2009-03-01 00:00:00.000' AND '2010-02-28 00:00:00.000' 
THEN '2009' 
WHEN GLPOSTDT BETWEEN '2010-03-01 00:00:00.000' AND '2011-02-28 
00:00:00.000' THEN '2010'
END AS GLFISCALYEAR, GLPOSTDT
FROM dbo.DEBITMEMOFISCALYEARS

As you can see, this view references another view called 
"DEBITMEMOFISCALYEARS".   Here is the programming for it:

ALTER VIEW [dbo].[DEBITMEMOFISCALYEARS] as
SELECT DATEPART(year, GLPOSTDT) AS YEAR, GLPOSTDT
FROM dbo.RM20101
WHERE RMDTYPAL = '3'

What syntax do I need to place in the first view (DEBITMEMOGLFISCALYEARS) in 
order to not have this message appear?

Thanks!

SQL Programmer (it's just a name)
0
Utf
4/18/2010 4:34:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
1537 Views

Similar Articles

[PageSpeed] 11

Disregard....I figured this out.  The actual query had spaces between the 
date and time.  Once i eliminated those spaces, all was well

SQL Programmer (it's just a name)

"SQL Programmer" wrote:

> Hello:
> 
> I get the following error message in SQL 2005 Management Studio:
> Conversion failed when converting datetime from character string.
> 
> I get this when I run a "select * from" query against a view called 
> "DEBITMEMOGLFISCALYEARS".  Here is the programming of this view:
> 
> ALTER VIEW [dbo].[DEBITMEMOGLFISCALYEARS] AS
> SELECT CASE WHEN GLPOSTDT BETWEEN '2007-03-01 00:00:00.000' AND '2008-02-29 
> 00:00:00.000'
> THEN '2007' WHEN GLPOSTDT BETWEEN '2008-03-01 00:00:00.000' AND '2009-02-28 
> 00:00:00.000' THEN '2008' WHEN
> GLPOSTDT BETWEEN '2009-03-01 00:00:00.000' AND '2010-02-28 00:00:00.000' 
> THEN '2009' 
> WHEN GLPOSTDT BETWEEN '2010-03-01 00:00:00.000' AND '2011-02-28 
> 00:00:00.000' THEN '2010'
> END AS GLFISCALYEAR, GLPOSTDT
> FROM dbo.DEBITMEMOFISCALYEARS
> 
> As you can see, this view references another view called 
> "DEBITMEMOFISCALYEARS".   Here is the programming for it:
> 
> ALTER VIEW [dbo].[DEBITMEMOFISCALYEARS] as
> SELECT DATEPART(year, GLPOSTDT) AS YEAR, GLPOSTDT
> FROM dbo.RM20101
> WHERE RMDTYPAL = '3'
> 
> What syntax do I need to place in the first view (DEBITMEMOGLFISCALYEARS) in 
> order to not have this message appear?
> 
> Thanks!
> 
> SQL Programmer (it's just a name)
0
Utf
4/18/2010 4:52:01 PM
"SQL Programmer" <SQLProgrammer@discussions.microsoft.com> wrote in message 
news:899459A9-B3EC-4558-BC3A-AE55C40DE6C4@microsoft.com...
> Disregard....I figured this out.  The actual query had spaces between the
> date and time.  Once i eliminated those spaces, all was well

Couldn't GLFISCALYEAR be derived like this?

    DATEPART(yyyy, DATEADD(mm, -2, GLPOSTDT))

That should return the current year on and after March 1st, and the previous 
year up to and including the lasy day of Februrary (leap year or not.)


-MM



> SQL Programmer (it's just a name)
>
> "SQL Programmer" wrote:
>
>> Hello:
>>
>> I get the following error message in SQL 2005 Management Studio:
>> Conversion failed when converting datetime from character string.
>>
>> I get this when I run a "select * from" query against a view called
>> "DEBITMEMOGLFISCALYEARS".  Here is the programming of this view:
>>
>> ALTER VIEW [dbo].[DEBITMEMOGLFISCALYEARS] AS
>> SELECT CASE WHEN GLPOSTDT BETWEEN '2007-03-01 00:00:00.000' AND 
>> '2008-02-29
>> 00:00:00.000'
>> THEN '2007' WHEN GLPOSTDT BETWEEN '2008-03-01 00:00:00.000' AND 
>> '2009-02-28
>> 00:00:00.000' THEN '2008' WHEN
>> GLPOSTDT BETWEEN '2009-03-01 00:00:00.000' AND '2010-02-28 00:00:00.000'
>> THEN '2009'
>> WHEN GLPOSTDT BETWEEN '2010-03-01 00:00:00.000' AND '2011-02-28
>> 00:00:00.000' THEN '2010'
>> END AS GLFISCALYEAR, GLPOSTDT
>> FROM dbo.DEBITMEMOFISCALYEARS
>>
>> As you can see, this view references another view called
>> "DEBITMEMOFISCALYEARS".   Here is the programming for it:
>>
>> ALTER VIEW [dbo].[DEBITMEMOFISCALYEARS] as
>> SELECT DATEPART(year, GLPOSTDT) AS YEAR, GLPOSTDT
>> FROM dbo.RM20101
>> WHERE RMDTYPAL = '3'
>>
>> What syntax do I need to place in the first view (DEBITMEMOGLFISCALYEARS) 
>> in
>> order to not have this message appear?
>>
>> Thanks!
>>
>> SQL Programmer (it's just a name) 


0
Mark
4/19/2010 2:38:50 AM
Reply:

Similar Artilces:

moving mailbox failed
I took the plunge and decided to move mailboxes. The second mailbox I moved failed. I have 2 exchange 2003 servers fully patched. On the source exchange server I get event ids 8507 and 8505. On the destination exchange server I get event ids 1006, 9172, and 1008. These errors are close to article 839217. I got the hot fix but I can not install it because it says I have too high of service pack. Any suggestions. ...

Recipient can't see special characters (apostrophe, double quote, hyphen, etc.)
When I write to some people, they don't see the characters. For examples: my single quote gets replaced with "=E2=80=99", my double quotes get replaced with "=E2=80=9C", my hyphen gets replaced with "=E2=80"" and my "..." gets replaced with "=E2=80=A6" Everything looks ok (as I sent it in my sent box). But when the person replies with history I can see that the text got converted in the interim. (And they tell me they see the funny characters, too. I've tried to send the message as Plain text, but the same thing happens. I us...

Converting Leads 01-04-06
When converting multiple Leads associated with the same Account - shouldn't CRM 3.0 recognize that association and put them under one Account? I am seeing our CRM create duplicate Accounts (and contacts for that matter but I know it does not do dup-detection) I convert 5 ABC company Leads to Contacts and Accounts I should end up with ONE Account and 5 contacts associated with that Account. Is that right? Any advice? No, it is doing what you are telling it to do. You are telling it to create a new Account, so it is creating a new Account. Unfortuanately, there is no provided way ...

Data Type Conversion
Hi, I am looking for a data type, method that allows me to store mixed data types, eg CString,int,double etc. At the moment i am converting my mixed data types them into CStrings and storing them in a CString Vector, vector<CString> m_tEvents. Now when i want access to the variables i then have to convert them from CString back to their original data type. I would like to get rid of this conversion to CString then conversion back to data type as i think it's inefficient and unnecessary. Does anyone know a method or data type that will stop me from having to do these conve...

Convert expression to code
Hi All, I have a conditional formatting expression i would like to convert to code, and need some help! I want the code to be in the OnOpen event of a report (called rptAllInformation). If the value of [DIA FORM ISSUED] is more than 6 weeks ago, and [DIA RESPONSE RECEIVED] is null, then label (lblOverdueWarning) becomes visible At the moment the expresson is: [DIA RESPONSE RECEIVED] Is Null And [DIA FORM ISSUED]<DateAdd("w",-6,Date()) Can anyone help? Kirstie "Kirstie Adam" <kirstiea@ecosse.net(nospam)> wrote: >Hi All, > >I have a conditiona...

check writing conversion
I'm looking for a way to take a numeric amount and convert it to alpha-numeric text -- for use in a check writing application. I've successfully writen a series of functions to do this, but it's very sloppy. Is there perhaps an ADD-IN to assist in this? Asked often http://tinyurl.com/us67 -- Don Guillett SalesAid Software donaldb@281.com "Kit Carson" <xqqme2day@yahoo.com> wrote in message news:57e801c3a979$7f0b9bb0$7d02280a@phx.gbl... > I'm looking for a way to take a numeric amount and > convert it to alpha-numeric text -- for use in a check >...

Converting to Money 2004 from Quicken2002
I am trying to decide whether to stick with Quicken or move to Money. TaxCut and MS are offering a good rebate but I cannot convert my data with the trial edition of Money. It provides the very useful message,"Your Quicken file could not be converted." I sometimes wonder if they pay people to think these up. Checking this group suggests that this is not an isolated incident. If I cannot convert, I cannot switch. There is too much to enter. Is there a log file somewhere that might tell me more? Thanks In microsoft.public.money, Otis Bricker wrote: >I am trying to decide whe...

Has anyone converted an MS Access VBA project to .Net?
I am looking for any information regarding the conversion of an MS Access VBA project with backend of Oracle to either Visual Basic or VB.Net. Has anyone done any conversion like this and if so, do they have any recommendations, issues to share? Thanks! ...

Conversion Of Unix Time
Hi All, I have a field on my excel report which comes as a UnixTime (1235502582).I want this to be in Excel readable format for e.g 24- SEP-2009 21:20.The char is string type I want to use this in a macro,since affer the data is downloaded,i want the code to convert.The data is several thousand rows.How would I make a column reference to call the macro. If I am using a macro like , Public Function UnixTime2DT(lUnixTimeStamp As Long) As Date UnixTime2DT = DateAdd("s", lUnixTimeStamp, #1/1/1970#) End Function how would i call it to a particular column? I tried to call it as .Co...

Time field conversion to SQL 2000
I have an Access 2000 db that I want to upsize to SQL server 2000. Some fields are of date/time data type and show only time in hours and minutes, These fields will not convert. What should I do??? (Fields of date/time type that show short date will convert.) Thanks a M for your help. Magnus Add a phony date to them. Usually the system minimum is used. (Sometimes the max.) Depends on what you need. Once the data is stored in SQL Server you can still display just the hours and minutes. Actually, Access *does* store the date/time field with a date. So you may not be displaying it but i...

Ctrl-c fails to work
Unusual issue. I don't know if anyone knows a workaround. When a user opens GP (v7.5 sp4 and 7), ctrl-c and ctrl-v work fine for copying and pasting. Once Transactions>>Financial>>Reconcile is opened, ctrl-c no longer works. You can click edit>>copy, but the keyboard shortcut to copy fails (ctrl-v still works). Closing GP and reopening resolves the issue, until you open that window again. Any thoughts? I have a client that has the same issue that the Ctrl-C will fails to work on the SOP item line. However have not yet been able to determine what conditions cause i...

unicode conversion
I have a sqlserver 2008 database that is currently using varchars. I need to move this to unicode. What is the best way to accomplish this task? Should I recreate the database using a certain character set (like utf-8) and then migrate the data or do I just do an alter table and move the fields from varchars to nvarchars? I am not real familiar with best practices for SQL Server and unicode. Thanks Joel (nowhere@nowhere.com) writes: > I have a sqlserver 2008 database that is currently using varchars. I need > to move this to unicode. > > What is the...

Converting mails to tasks
Hi All, Is there a way where we can convert the incoming mails to tasks using some task template? If so, please let me know how. Thanks, Vinay. Check this page for some possibilities: http://www.slipstick.com/addins/tasks.htm -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:0b0301c3a9af$814353b0$a101280a@phx.gbl, Vinay Urs wrote: > Hi All, > > Is there a way where we can convert the incoming mails to > tasks using some task template? > > ...

Converting Money 2006 Business to Money Plus Premium
Last year I installed the Home & Business version of Money, but have found I do not really use the business features. I may begin to use the Accounting software. Will my present money file convert ok if I go back to using the Premium version for my personal finances? Thank you in advance. In microsoft.public.money, NaStein wrote: >Last year I installed the Home & Business version of Money, but have found I >do not really use the business features. I may begin to use the Accounting >software. Will my present money file convert ok if I go back to using the >Prem...

Character set not supported??
Dear all, Our email server is EX5.5. I found that some inbound emails contain the following text and its original email (header + body) is putted into a plain text file "message.txt". Why?? How can I fix it? Thank you very much, Keith <Start Quote> This message uses a character set that is not supported by the Internet Service. To view the original message content, open the attached message. If the text doesn't display correctly, save the attachment to disk, and then open it using a viewer that can display the original character set. <<message.txt>> <...

Data Protection Mangager 2010 failed to start
One server that was backing up just fine is now no longer backing up. I'm not sure where to go from here as searching newsgroups and forums are not providing any answers. The following is the error code that I'm getting: Backup attempted at '4/20/2010 3:47:48 PM' failed to start, error code '2155348081'. Log Name: Microsoft-windows-Backup/Operational Source: Backup EventID: 19 Level: Error User: SYSTEM OpCode: Info Task Category: None Any help pointing me into the right direction would be greatly appreciated :) The event that ...

Count partial matched text string
I have 3 spreadsheets. The first, “Supplies Requests Received” where Column B contains the (duplicated) names of internal offices placing supply requests (e.g., SNF, DGEN, CANM, etc.) and Column G contains each date a request for supplies was received from that office. Column B Column B SNF 1//29/2009 DGEN 4/19/2008 CNMN 2/4/2009 ASEV 12/11/2008 SNF 2/3/2009 ASEV 9/16/2008 DGEN 5/1/2008 The second spreadsheet “Supplies Delivered” where Column A is a link of Column B from the above “Supplies Requests Received spreadsheet and Column G (of Supplies Delivered”) contains the names of ALL ...

converting a number to a Julian Date
I am trying to take part of a claim number that is a Julian Date and translate it into a calendar date. Any suggestions on what to use in my query. Here are some examples of the 4 digit numbers: 9248 9230 9342 9345 Thank you - Lisa There are actually several different definitions of Julian Date. Which one are you trying to use? (In other words, to what should those four sample values equate?) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "LisaK" <LisaK@discussions.microsoft.com> wrote in message news:...

Converter
I can not open Publisher documents created in another version of Publisher. I have office publisher 2000. Is there a converter file or something I need to download? thank you, Which version are you trying to open your P2000 files in? -- JoAnn Paules MVP Microsoft [Publisher] "Susan" <anonymous@discussions.microsoft.com> wrote in message news:2ad4201c46794$2d844380$a401280a@phx.gbl... >I can not open Publisher documents created in another > version of Publisher. I have office publisher 2000. > > Is there a converter file or something I need to download?...

Database conversion
I'd like to convert an Access 2000 that i had on an xp machine to an Access 2007 database on my new Windows 7 machine. Whenever i attempt to either 'Save As' or 'Convert' I get a message that the database is already opened exclusively by the 'Admin' user and to try again when the database is available. What am i doing wrong? Thanks for the help. On Tue, 29 Dec 2009 13:15:01 -0800, BudinPA <BudinPA@discussions.microsoft.com> wrote: >I'd like to convert an Access 2000 that i had on an xp machine to an Access >2007 database on my new ...

Converting Comments to Footnote Type References #2
I understand that Excel is not a word processor. What I wanted to kno is if there was a better way to anotate a spreadsheet. This i important for providing spreadsheet data (budgets in this case) i printed form to others. Referencing cell notes for these hardcop users would be much better if there were a supersrcipt reference numbe in a cell which then would match to comments at the bottom of the page. I'll try some of those macro suggestions you provided though. Thanks, Scot -- Planne ----------------------------------------------------------------------- Planner's Profile: ht...

non ascii characters
I have some old files made in WordStar for windows. If I open them in word pad I can read them except most words end in a non ascii character. I should be able to run that file thru a filter that turns the 8th bit into a 0 but don't know how. Can any suggest a filter? charles You can download the old WordStar filter from my web site. I have not tested with Word 2010 yest, but it works with versions up to 2007. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP M...

Wild Card / Character
Hi Using the formula in excel = sumproduct(-- (left(A1:A200;1)="2");c1:c200) it means that take the first digit in column a if this i equal to 2 then sumarize C column this vorks fine. But if I change the "2" to a wild Character such as "*" or "?" it does not function. nor if I Use "~*" or "~?" The tilde I use is [ALT}126 The A colunm does include text only I use XP 2003 Anyone have an Idea Kurt What are you trying to do? Summarize column C if the first character of column A is any character? Doesn't make sense. Pete On ...

The Microsoft CRM Outlook add-in failed to initialize the user's language setting
Hello I have CRM 4 + RU7 on server; CRM 4 + RU5 + Office 2007 SP2 + Win 7; I try to upgrade Outlook Client to RU7 but after success install it do not start in Outlook with error : The Microsoft CRM Outlook add-in failed to initialize the user's language setting. Restart Microsoft Outlook and try again. HR=0x80131534. Context=. Function=CAddin::HrActivateAddin. Line=671. How to solve this problem ? B.I. "B.I." wrote: > Hello > > I have CRM 4 + RU7 on server; > CRM 4 + RU5 + Office 2007 SP2 + Win 7; > > I try to upgrade Outlook Client to RU7 but a...

I need to convert name 3 part name into Last, First MiddleNameorMI
I have a column of names, most of which are FirstName LastName in format. Some, however are First MiddleName (or MI) Last. I need to convert these values into LastName Firstname MI. I have a formula that gets close, but doesn't account for the possible presence of a Middle Name or initial. For ex, data could be Jill Smith, or Jo Ann Smith. I need to make Smith, Jill and Smith, Jo Ann. Seems like I need to go backwards thru the string with a Search function to find the 2nd space for this to work, but can't. Or should I try it by parsing consecutive columns with incremental chan...