Convert Character String To Date && Use In Calcs.
Apr 21, 2008
Hi There,
I am trying to create a report that sums up new cases weekly based on the previous 5 months. I have included some sample data below. The only field that displays a date is called monthSubmitted, varchar 7 and not very useful. How can I first convert this firld to a date ddmmyy?
How would you the code it so the user can run the report on demand at any given time and display the data for the last 5 months on a weekly basis? I am not sure as to how the date calculations should be coded.
12-Jan
19-Jan
26-Jan
2-Feb
9-Feb
16-Feb
23-Feb
MSS
New Cases Received
85
84
79
98
79
95
65
S1
30
32
27
40
42
38
29
S2
47
34
37
23
23
37
32
S3
3
7
-
17
4
1
9
Thanks,
Rhonda
View 5 Replies
ADVERTISEMENT
Apr 5, 2014
I'm new to SQL and I've been trying this for a while now.
I have let's say a loop of numbers from one to ten. It appears like this:
1
2
3
4
5
What I want to do is to have it appear on one column like this: 12345, the problem is I can't seem to figure out if I need to only have one variable or I'm missing something else, I figured you need to convert them into a string character but I'm still unable to do it.
View 7 Replies
View Related
Mar 18, 2014
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
View 7 Replies
View Related
Nov 16, 2015
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
View 24 Replies
View Related
Sep 28, 2007
Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,
I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.
I have no choice of making date column in SQL to string or Varchar etc,
is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).
many many thanks,
View 2 Replies
View Related
Oct 22, 2013
My Query is as shown below
Declare @FROMDATE DATETIME
Declare @THRUDATE DATETIME
Declare @Parm Varchar(250)
set @FROMDATE = '09/25/2013'
set @THRUDATE = '09/28/2013'
set @Parm = 'FROMDATE=' + @FROMDATE + ';THRUDATE=' + @THRUDATE
I am getting an error at above line saying that
Conversion failed when converting date and/or time from character string.
View 8 Replies
View Related
Feb 15, 2008
Hi,
I Have this simple convertion in a Script component
Dts.Variables("dateOfProcess").Value = CDate(lineMCF.Substring(30, 2) + "/" + lineMCF.Substring(28, 2) + "/" + lineMCF.Substring(24, 4))
this works fin in my development environment which has a spanish version of SQL Server and uses "DD/MM/YYYY" as date format.
but the production environment has an english version of SQL Server and "MM/DD/YYYY" date format, so the package crashes in this server.
How do I convert the string to date not depending on the SQL server language.
thanks.
View 2 Replies
View Related
May 15, 2015
This is my code and I don't know why this error keeps coming out : PS : I did cursor to execute query.Th error showed is bold:
DECLARE RegCreatedDate CURSOR FOR
SELECT DISTINCT (CONVERT(NVARCHAR,CreatedDate,103))
FROM CA_Registration WHERE Month(CreatedDate)= @paMonthIn AND YEAR(CreatedDate)=@paYearIn
OPEN RegCreatedDate
FETCH NEXT FROM RegCreatedDate INTO @RegCreatedDate
WHILE @@FETCH_STATUS = 0
[Code] ....
View 9 Replies
View Related
Sep 4, 2015
I'm trying to select only July from show_held but I keep on getting the error message saying:
Conversion failed when converting date and/or time from character string.
I get error message after I write this code:
ANDshow.show_held = '&July&'
As you can see from the below code, How do I select July from times_held_in_July?
SELECTevent_name,
DATENAME (MONTH, show_held) AS times_held_in_July
FROMevent,
show
WHEREevent.show_id = show.show_id
Result:
event_name times_held_in_July
DressageJuly
Jumping July
Led in July
Led in September
Led in May
DressageApril
DressageJuly
Flag and PoleJuly
SELECTevent_name,
DATENAME (MONTH, show_held) AS times_held_in_July
FROMevent,
show
WHEREevent.show_id = show.show_id
ANDshow.show_held = '&July&'
Result:
Msg 241, Level 16, State 1, Line 24
Conversion failed when converting date and/or time from character string.
View 6 Replies
View Related
Sep 14, 2015
BEGIN TRAN;
INSERT INTO [dbo].[QuestManualProcess]
([ProcessFromDate]
,[LastProcessedFileDateStamp]
,[ProcessedOnDate]
[code]....
Conversion failed when converting date and/or time from character string/
View 4 Replies
View Related
Jan 8, 2013
I have a table in which a date value is stored as varchar.some of these values are stored ina dd/mm/yyyy format and other values are stored in a yyyy-mm-dd format..Now I wish to retrieve some data by querying between two dates. However I need to convert the varchar date value to datetime in order to do this but since the date value is in two different formats, the following doesn't work.
select date_value
from my_table
where CONVERT(DATETIME, date_value, 103) between @date1 and @date2
How can you convert the date value to datetime when its stored in mutiple formats. I can't change the table itself as I dont have admin privelages.
View 14 Replies
View Related
Sep 26, 2014
I am trying to write a stored procedure that loops through the list of user tables, gets the record count for each one and write a record to an audit table with DATE, TABLENAME, RECORDCOUNT.I keep getting an error "Conversion failed when converting date and/or time from character string".Here is the script...
DECLARE @table nvarchar(500)
DECLARE @sql nvarchar(520)
DECLARE CursorSelect CURSOR FOR
select table_name from INFORMATION_SCHEMA.tables where table_name not like 'sys%' order by table_name
[code]....
View 2 Replies
View Related
Nov 16, 2015
I've imported a CSV file into a table in SQL Server 2012. It's a large file, 140,000+ rows, so I couldn't covert it to Excel first to preserve the date format due to Excel's row limit. In the CSV file, there were 3 column with date data in "31-Aug-09" format, and the import automatically transformed these in "31AUG09" format (varchar(50)) in SQL Server. Now I need to convert these 3 columns from varchar to datetime so I could work with them in date format.
I've tried several things,e.g,
select
convert(datetime,
right(admdate,4)+left(admdate,2)+substring(admdate,3,3))
or
select
convert(datetime,
substring(admdate,6,4)+substring(admdate,1,2)+substring(admdate,3,3))
but keep getting the same error message (Msg 241) "Conversion failed when converting date and/or time from character string".
View 4 Replies
View Related
Dec 7, 1999
I'm using SQL 7.0 SP1.
I am using DTS to read in a date with the format of YYYYMMDD. I am trying to convert this date to MM/DD/YYYY and then use the CDate function with the following code to load it into a Datetime column in SQL Server:
Function Main()
strDate = DTSSource("Col002")
strYear = Left(strDate, 4)
strMonth = Mid(strDate, 5, 2)
strDay = Right(strDate, 2)
strHoldDate = strMonth + "/" + strDay + "/" + strYear
DTSDestination("DateName") = strHoldDate
Main = DTSTransformStat_OK
End Function
My DTS package will execute without errors, but it does not add the row. I have been successful using CDate when the source date is in the format MM/DD/YYYY.
Also, do you have any tips on how to debug DTS? How to see what's in a variable, etc.?
Thanks!
Lisa
View 1 Replies
View Related
Oct 29, 2014
In our ERP system we have a field which is a date-picker in the user front end, but the value is stored in an NVARCHAR field and not always consistently. How can I convert this to a date (preferably in the format YYYY-MM-DD HH:MM) that I could use in a calculation?
select code, spec_value from spec_checklist_remind where spec_checklist_id = 17
code spec_value
------------ -----------------------------------------------------------------------
05MC0001 22/07/2014
05MC0002 23/07/2014
06MT0001 01-May-2014
06MT0002 01-May-2014
06MT0006 01/05/2014
06MT0007 01-May-2014
06MT0008 01/05/2014
View 5 Replies
View Related
May 13, 2004
i have a sql statement that i created in code and it is sending a query to the database
when i dim the variable a datetime variable it says that it cant convert it
if i make the variable a varchar it works but it only returns one result when it should be returning about 10
here is the code
Public Function dbDGQSSearch(ByVal BatchID As String, ByVal CreatedBy As Integer, ByVal CreatedFor As Integer, ByVal DateCreatedMod As Integer, ByVal DateCreated As String, ByVal DateCompletedMod As Integer, ByVal DateCompleted As String, ByVal DateStartedMod As Integer, ByVal DateStarted As String, ByVal SearchType As Integer, ByVal Completed As Integer, ByVal PriorityMod As Integer, ByVal Priority As Integer, ByVal RemainingCallsMod As Integer, ByVal RemainingCalls As Integer, ByVal TotalCallsMod As Integer, ByVal TotalCalls As Integer, ByVal Bonus As Integer, ByVal Keyword1 As String, ByVal Keyword2 As String, ByVal Keyword3 As String, ByVal Keyword4 As String, ByVal Keyword5 As String)
Dim strQueSearch As String
strQueSearch = "SELECT tlkup_Rep.RepID, tlkup_Rep.PositionID, tlkup_Rep.RepFName, tlkup_Rep.RepLName, tlkup_Rep.RepPassword, tlkup_Rep.RepUserName, tlkup_Rep.RepFName + ' ' + tlkup_Rep.RepLName AS RepName, t_Que.QueID, t_Que.BatchID, t_Que.AdminID, t_Que.Manager, t_Que.BonusID, t_Que.QueCompleted, t_Que.QueDate, t_Que.QueNotes, t_Que.QuePriority, t_Que.QueQuantity, t_Que.QueStartDate, t_Que.Mail, t_Que.QueDateComplete, t_Que.QueTotal FROM t_Que INNER JOIN tlkup_Rep ON t_Que.Manager = tlkup_Rep.RepID AND t_Que.Manager = tlkup_Rep.RepID WHERE BatchID<>'' and BatchID<>'2' and BatchID<>'3' and BatchID<>'4' "
'Creates statement for selecting the add to batch data where the criteria appear
If BatchID <> "" Then
strQueSearch = strQueSearch + " and t_Que.BatchID= @BatchID "
End If
If CreatedBy > 1 Then
strQueSearch = strQueSearch + " and t_Que.RepID =@RepID "
End If
If CreatedFor > 1 Then
strQueSearch = strQueSearch + " and t_Que.Manager = @Manager "
End If
If DateCreated <> "" Then
If DateCreatedMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueDate >@QueDate "
ElseIf DateCreatedMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueDate <@QueDate "
ElseIf DateCreatedMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueDate =@QueDate "
End If
End If
If DateCompleted <> "" Then
If DateCompletedMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueDateComplete >@QueDateComplete "
ElseIf DateCompletedMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueDateComplete <@QueDateComplete and t_Que.QueDateComplete >'1/1/1900' "
ElseIf DateCompletedMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueDateComplete =@QueDateComplete "
End If
End If
If DateStarted <> "" Then
If DateStartedMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueStartDate >@QueStartDate "
ElseIf DateStartedMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueStartDate <@QueStartDate "
ElseIf DateStartedMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueStartDate =@QueStartDate "
End If
End If
If SearchType = 0 Then
'Both
'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 "
ElseIf SearchType = 1 Then
'Mail
strQueSearch = strQueSearch + " and t_Que.Mail=1 "
ElseIf SearchType = 2 Then
'Phone
strQueSearch = strQueSearch + " and t_Que.Mail=0 "
End If
If Completed = 0 Then
'Both
'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 "
ElseIf Completed = 1 Then
'Yes
strQueSearch = strQueSearch + " and t_Que.QueCompleted=1 "
ElseIf Completed = 2 Then
'No
strQueSearch = strQueSearch + " and t_Que.QueCompleted=0 "
End If
If Priority > 0 Then
If PriorityMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QuePriority >@QuePriority "
ElseIf PriorityMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QuePriority <@QuePriority "
ElseIf PriorityMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QuePriority =@QuePriority "
End If
End If
If RemainingCalls > 0 Then
If RemainingCallsMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueQuantity >@QueQuantity "
ElseIf RemainingCallsMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueQuantity <@QueQuantity "
ElseIf RemainingCallsMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueQuantity =@QueQuantity "
End If
End If
If TotalCalls > 0 Then
If TotalCallsMod = 0 Then
'>
strQueSearch = strQueSearch + " and t_Que.QueTotal >@QueTotal "
ElseIf TotalCallsMod = 1 Then
'<
strQueSearch = strQueSearch + " and t_Que.QueTotal <@QueTotal "
ElseIf TotalCallsMod = 2 Then
'=
strQueSearch = strQueSearch + " and t_Que.QueTotal =@QueTotal "
End If
End If
If Bonus > 1 Then
strQueSearch = strQueSearch + " and t_Que.BonusID =@BonusID "
End If
If Keyword1 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword1+'%' "
End If
If Keyword2 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword2+'%' "
End If
If Keyword3 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword3+'%' "
End If
If Keyword4 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword4+'%' "
End If
If Keyword5 <> "" Then
strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword5+'%' "
End If
'makes statement into sqlcommand
C.daQueSearch.SelectCommand.CommandText = strQueSearch
'var declaration
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@BatchID", SqlDbType.VarChar, 12))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Manager", SqlDbType.Int))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@RepID", SqlDbType.Int))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueDate", SqlDbType.VarChar, 20)) '<--- This is what,when i change to datetime, says it cant convert
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueStartDate", SqlDbType.VarChar, 20))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueDateComplete", SqlDbType.VarChar, 20))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QuePriority", SqlDbType.Int))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueQuantity", SqlDbType.BigInt))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@QueTotal", SqlDbType.BigInt))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@BonusID", SqlDbType.SmallInt))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword1", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword2", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword3", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword4", SqlDbType.VarChar, 50))
C.daQueSearch.SelectCommand.Parameters.Add(New SqlParameter("@Keyword5", SqlDbType.VarChar, 50))
'data entry
C.daQueSearch.SelectCommand.Parameters("@BatchID").Value = BatchID
C.daQueSearch.SelectCommand.Parameters("@Manager").Value = CreatedBy
C.daQueSearch.SelectCommand.Parameters("@RepID").Value = CreatedFor
C.daQueSearch.SelectCommand.Parameters("@QueDate").Value = DateCreated
C.daQueSearch.SelectCommand.Parameters("@QueStartDate").Value = DateStarted
C.daQueSearch.SelectCommand.Parameters("@QueDateComplete").Value = DateCompleted
C.daQueSearch.SelectCommand.Parameters("@QuePriority").Value = Priority
C.daQueSearch.SelectCommand.Parameters("@QueQuantity").Value = RemainingCalls
C.daQueSearch.SelectCommand.Parameters("@QueTotal").Value = TotalCalls
C.daQueSearch.SelectCommand.Parameters("@BonusID").Value = Bonus
C.daQueSearch.SelectCommand.Parameters("@Keyword1").Value = Keyword1
C.daQueSearch.SelectCommand.Parameters("@Keyword2").Value = Keyword2
C.daQueSearch.SelectCommand.Parameters("@Keyword3").Value = Keyword3
C.daQueSearch.SelectCommand.Parameters("@Keyword4").Value = Keyword4
C.daQueSearch.SelectCommand.Parameters("@Keyword5").Value = Keyword5
Try
C.ndConnection.Open()
C.daQueSearch.SelectCommand.ExecuteNonQuery()
Catch ex As Exception
lblMainError1.Text = err("dbDGQSSearch " + ex.Source, ex.Message, CurUsr)
lblMainError1.Visible = True
Finally
C.ndConnection.Close()
End Try
FillQSDG()' this fills the datagrid
End Function
View 8 Replies
View Related
Apr 16, 2004
Hi,
Can anyone tell me how to convert date and time to string.
I got one field inside my database which contain data like below:
4/16/2004 10:19:01 AM
if i wan to call out a record which have to refer to the data above
Select *
From table_A
where field_date= ???????
what to fill in the ??????
Thanx
View 3 Replies
View Related
Sep 5, 2014
I'm importing dates into a table with Bulk insert
SET DATEFORMAT DMY
it works with dates e.g. "14/01/2009"
However sometimes I get dates in the format
"Fri 14/01/2009"
What is the best way to convert these.
I can only think of putting them in a staging table with all date fields as varchars Then updating these varchar fields
LTRIM(REPLACE(dtField, 'Mon', ''))
LTRIM(REPLACE(dtField, 'Tues', ''))
View 9 Replies
View Related
Jan 6, 2015
I am trying to convert a date-string of this format (DDMMMYY - ex. 06JAN15) to datetime but I keep on getting errors:
codes that I tried:
convert(datetime, '06JAN15', 6)
convert(datetime, '06JAN15', 112)
What date format code should I use for DDMMMYY?
View 13 Replies
View Related
Jul 15, 2014
All source and target date fields are defined as data type "smalldatetime". The "select" executes without error though when used with "insert into" it fails with the error:
Msg 295, Level 16, State 3, Line 25: Conversion failed when converting character string to small date-time data type..I am converting from a character string to smalldatetime since the source and target date columns are "smalldatetime". All other columns for the source and target are nvarchar(255). I assume there is an implicit conversion that I don't understand. In a test, I validated that all dates selected evaluate ISDATE() to 1.
USE [SCIR_DataMart_FromProd_06_20_2014]
GO
IF OBJECT_ID ('[SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]', 'U') IS NOT NULL
DROP TABLE [SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]
[code]....
View 9 Replies
View Related
Jan 20, 2006
Hiya
I'm trying to run the below but getting errors on the convert statement, can anyone point me in the right direction?
exec PurgeRevisionsByDate convert(datetime,'01/01/2005',103)
Error message
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'convert'.
Thanks
Bav
View 12 Replies
View Related
May 28, 2007
Hello forum,
Is it possible to convert a date stored as a string into a datetime with integration services 2005? My attempts with the data conversion? fail. The string type form of the date is yyyy-mm-dd and the desired result for use in a Union All is dd/mm/yyyy 12:00:00AM. This outcome is needs so that match on the date can populate a fact table, as the results are coming from two different databases.
All advice/help welcomed.
Ian
View 4 Replies
View Related
Apr 7, 2008
Hello Everyone,
How can I convert the string column to date in SSIS.
Example:
I have a column which is having a value as 19890213?
Basically this values is a date
Now I wanted this value to be a date value as 01/01/2007?
i had try it out by doing this ways
(DT_DATE)(SUBSTRING(DATE_SEEN_SPEC,5,2) + "/" + SUBSTRING(DATE_SEEN_SPEC,7,2) + "/" + SUBSTRING(DATE_SEEN_SPEC,1,4))
but still i'm getting the error message.
I know the this can be achived through type cast in Derived Column compunent, but not sure with which cast please help me out in resoulving this issue.
Thank you
View 1 Replies
View Related
Jul 27, 2004
Im working with a breaking system and I wont to convert the (FROM) datetime column to accept just time like (4:00:00 AM) without the date (7/23/2004) but it doesn’t have column format like the access ??
I found something in the SQL help :
How to convert the format of a Date Time String transformation (Enterprise Manager)
To convert the format of a Date Time String transformation
1.On the Transformations tab of the Transform Data Task Properties or Data Driven Query Task Properties dialog box, click the Source column containing the date or time to be modified, and then click the Destination column where you want the modified string to be placed.
2.Do one of the following:
•If there is a mapping arrow connecting the two columns, click Delete, and then click New.
•If there is no mapping arrow, click New.
3.In the Create New Transformation dialog box, click DateTime String.
4.Click the General tab, and then click Properties.
5.In the Date Format list, select the format you want.
6.Click Naming to display the Calendar Names dialog box, where you can select long or short day or month names and the A.M. and P.M. designators you want.
7.In the Language list, select the language you want, and then click Set Language Defaults.
But unfortunately I didn't find the "Transformations tab" I look a lot in the SQL Enterprise Manager
Do anybody work with the Transformations or at least know where is it please ???
View 1 Replies
View Related
Nov 11, 2015
I have a column value as '201303' for the date. I need to get the output into two columns as 2013 and Mar. The date column is a integer one.
How can I convert this date column in a Data Convertion task.
View 6 Replies
View Related
Apr 3, 2008
I have two fields DSRHADTI which is an isodate and DSRHTIME which is 8 char time field in format 10.31.00. I want to take both these fields and put them into a field that is database timestamp so I have converted DSRHDATI to 10 character field. I am then trying to use substring to put both into 18 character field using derived column transformation editor. but it does not like the below. It's red syntax error what am I missing.
(SUBSTRING(Copy of DSRHDATI,1,4) +' /' + SUBSTRING( Copy of DSRHDATI,6,2) + '/ ' + SUBSTRING(Copy of DSRHDATI,9,2)) + SUBSTRING(DSRHTIME,1,2) + '.' + SUBSTRING(DSRHTIME,4,2) + '.' + SUBSTRING(DSRHTIME,7,2)
One I get the above to work I plan on convert 18 char to datetimestamp.
Am I on the right track on how to do this?
View 16 Replies
View Related
Feb 19, 2008
Note the code below, running on the version noted.
I just found this today, figured I'd share. Not sure if it's a known bug or a "special" feature. The only difference between the two queries is the 3rd line, everything else is the same. Notice that the sort order changes, yet no errors or warnings are given. I assume that the table aliases are ignored for the order by, unless there are duplicate column names in the results.
I abstracted this from a bug I discovered in one of my apps today, where I have sales reps assigned to their clients with start and end dates. A while back, a developer asked me to format the dates without the time portion, and when I did so, I introduced the problem. I resolved it temporarily by ordering by convert(datetime, startdate) but I found it strange that the column alias match overrides the table alias attempted match in the order by. Another way to get around this would be to change the column aliases, then the sort order would be as desired, but I didn't want to have to change the app code for something so trivial.
My apologies if this is a duplicate.
@@version = Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
select convert(varchar(10), table_alias.startdate, 101) as startdate,
convert(varchar(10), table_alias.enddate, 101) as enddate
from
(
select convert(datetime, dateadd(mm, -4, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -4, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -3, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -3, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -2, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -2, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -1, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -1, getdate())) as enddate
union
select convert(datetime, getdate()-1) as startdate, convert(datetime, getdate()) as enddate
) as table_alias
order by table_alias.startdate
select convert(varchar(10), table_alias.startdate, 101) as startdate,
convert(varchar(10), table_alias.enddate, 101) as enddate,
table_alias.startdate, table_alias.enddate
from
(
select convert(datetime, dateadd(mm, -4, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -4, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -3, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -3, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -2, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -2, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -1, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -1, getdate())) as enddate
union
select convert(datetime, getdate()-1) as startdate, convert(datetime, getdate()) as enddate
) as table_alias
order by table_alias.startdate
View 4 Replies
View Related
Jan 28, 2008
Hello,
I have tried a few different things within a Derived Column transform to convert a unicode yymmdd date string to SQL Server DateTime.
Does anyone have ideas on how to do this?
Thank you for your help!
cdun2
View 4 Replies
View Related
May 17, 2015
I have a table that contains file paths as
ServernamefolderAfilenameA
ServernameFolderBFilenameB
and I need a query to return
ServernamefolderA
ServernameFolderB
I tried
SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table
But that removes everything after the first and I need it to return all data before the last
View 5 Replies
View Related
Apr 15, 2004
I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).
How do I select everything to the right of the dash when the number of characters to the left of the dash varies?
View 3 Replies
View Related
Oct 1, 2014
I'd like to return the left-most character from an 8 character string & the third from the left character too.
Like this ABC00123 returns AC
$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());
echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){
[Code] ....
View 5 Replies
View Related
Nov 2, 2006
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset
at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
Can anyone please tell me what's wrong?
View 6 Replies
View Related
Dec 30, 2003
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
View 6 Replies
View Related
