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.
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.
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
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.
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.
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
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 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:
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.?
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
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
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]
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
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.
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 ???
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.
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
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?
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
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.
