Re: Syntax error converting Date time string?
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 08/24/04
- Next message: Partha Mandayam: "Re: alter table failing"
- Previous message: Anith Sen: "Re: How to split rows to columns?"
- In reply to: CB: "Re: Syntax error converting Date time string?"
- Next in thread: Tamas Bojcan - bojci: "Re: Syntax error converting Date time string?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 24 Aug 2004 19:21:37 +0200
A couple of comments. All assumes that L.Date_Last_Used is a datetime datatype column.
> - WHERE (L.Date_Last_Used > CONVERT(DATETIME, '20040804', 102))
No need for a formatting code for above CONVERT. Actually, no need for a CONVERT at all. The format is a
"safe" format (doesn't depend on any national settings) and a character string is implicitly converted to
datetime.
> - WHERE (L.Date_Last_Used > CONVERT(DATETIME, '@prog_beg_date', 102))
No. above doesn't work. Remove the single quotes so it look like:
WHERE (L.Date_Last_Used > CONVERT(DATETIME, @prog_beg_date', 102))
However, what datatype is the variable. I assume that variable is a datetime datatype. Then just do:
WHERE (L.Date_Last_Used > @prog_beg_date')
I strongly suggest you read http://www.karaszi.com/SQLServer/info_datetime.asp for a better understanding of
the datetime datatypes in SQL Server.
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "CB" <clb39-at-cornell-dot-edu> wrote in message news:eRqbrzfiEHA.4056@TK2MSFTNGP09.phx.gbl... > Roji - thanks for the fast repsone, but it did not work. Here's the testing > I did with your suggestion: > > I tried changing the following line: > - SET @prog_beg_date = '20040804' > - WHERE (L.Date_Last_Used > CONVERT(DATETIME, '20040804', 102)) > Which still works, but failed when I put the variable in there, > > - WHERE (L.Date_Last_Used > CONVERT(DATETIME, '@prog_beg_date', 102)) > I also tried changing the convert string to just the variable, that also > failed > - WHERE (L.Date_Created > '@prog_beg_date'). > > Thanks again, anyone else have an idea? > > Chris > > "Roji. P. Thomas" <lazydragon@nowhere.com> wrote in message > news:ezfb3mfiEHA.3548@TK2MSFTNGP09.phx.gbl... > > Try > > > > SET @prog_beg_date = '20040804' > > > > > > -- > > Roji. P. Thomas > > Net Asset Management > > https://www.netassetmanagement.com > > > > > > "CB" <clb39-at-cornell-dot-edu> wrote in message > > news:OLbihifiEHA.3548@TK2MSFTNGP09.phx.gbl... > > > When I attempt to substitute the @prog_beg_date into the queries, I > > recieve > > > a syntax error converting Date time string - Here's a few of my > attempts, > > > but thus far the problem has eluded me: > > > - b/c variable date time is declared, there is no need for the Convert > > > string (this fails) > > > - Put the Convert string in the variable (this fails) > > > > > > Any insights will be greatly appreciated! > > > > > > This works: > > > -- Declare Year Variable, pulled from the Admin_Settings table. > > > DECLARE @ap_year INT > > > SELECT @ap_year = DB_Setting > > > From Admin_settings > > > Where Set_id = '2' > > > -- Declare Date variable for the start of the program > > > DECLARE @prog_beg_date DATETIME > > > SET @prog_beg_date = '2004-08-04 00:00:00' > > > > > > This fails (the only thing that changed is the variable is added to the > > > queries) > > > USE Admissions > > > GO > > > --- Global Variables > > > -- Declare Year Variable, pulled from the Admin_Settings table. > > > DECLARE @ap_year INT > > > SELECT @ap_year = DB_Setting > > > From Admin_settings > > > Where Set_id = '2' > > > -- Declare Date variable for the start of the program > > > DECLARE @prog_beg_date DATETIME > > > SET @prog_beg_date = '2004-08-04 00:00:00' > > > > > > -- Update Year > > > --Update Login_Accounts > > > --Set Acad_Year = '@ap_year' > > > Select L.Date_Created, L.User_Login, L.Acad_Year, A.Term > > > FROM Login_Accounts L > > > INNER JOIN Application A ON L.User_Login = A.User_Login > > > WHERE (L.Date_Last_Used > CONVERT(DATETIME, '@prog_beg_date', 102)) > > > AND (A.Program = 'MBA' OR A.Program = 'PHD' OR A.Program = 'EMBA') > > > AND (L.Acad_Year <> '@ap_year' OR L.Acad_Year IS NULL) > > > > > > > > > > > >
- Next message: Partha Mandayam: "Re: alter table failing"
- Previous message: Anith Sen: "Re: How to split rows to columns?"
- In reply to: CB: "Re: Syntax error converting Date time string?"
- Next in thread: Tamas Bojcan - bojci: "Re: Syntax error converting Date time string?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
