have a field called sigdate datatype varchar that saves dates
like these
01/2/2007
12/2/2007
06/19/2007
01/21/2007
i want to show distinct months/year in a drop down
so the query should output
01/2007
12/2/2007
06/2007
here is my code
SELECT Distinct(convert(varchar(2),month([sigdate]))+convert(varchar(4),year([sigdate]))) as [monthyear] from stdreg
but its not working
any ideas
todd
I have a column of data in a table that has date formatted as '2006-03-26 00:00:00.000'
What T-SQL command that will alter the column so that it is now Varchar '03-26-2006'?I also want to know how to do the opposite... if I have '03-26-2006' via command, how do I convert the column of the table to be datetime from varchar
This is probably a very simple question, but there it goes.
I have a DB table with the following varchar value:
1/12/2004
I want to convert the specified data to the following:
Monday, January 12th, 2004
I guess i want to:
Convert(DATETIME, '1/12/2004'), But...
I want it to be inserted into the new table as a long date type value.
Is this possible?
Thanks in advance!
Hi:
here i am giving one example with Emp table and fields ssn,empjoindate
I have a table with field empjoindate as varchar(10).I need number of employees joined between 12/23/01 and 01/04/02.I ran this query using select ssn from EMP where convert(varchar(10),empjoindate,101) between '12/23/01' and '01/04/02'.it is not fetching any data.
Please help me
Thanks
Tej
I have a preexisting database that has dates in the format mm/dd/yyyy but it is set up as varcar. How can I apply numeric operators to it to find how old someone is from todays date? I then need to take that value and populate another column on the same row.
View 1 Replies View Relatedi am pulling data from a read only database and the dates are i think in varchar format (they sort by 01-01-2012, 01-02-2011...etc)the dates are also in US format.how can i convert these to a EUROPE date format and also datetime so that i can use the > and just show logged events greater than a specific date
Code:
SELECT logId, logdate, logdesc
FROM dbo.log
WHERE (logdate >'07/07/2012')
the code about will not work as the logdate is in varchar.
I have a datetime column and I would like to update another column in the same table with the varchar value of date. e.g. date column has 06-08-2008 as a value. I would like to have the same value in the varchar field.
wondering which function can I use to accomplish this task?
thanks
I have Field which contains Date in dd/mm/yy fromat. I need to convert it into DateTime format so that it can be processed.
Please help
I cannot execute a dynamic SQL function when I convert a datetime to a string:
declare @date_key datetime
set @date_key = '5/1/08'
select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI'
,'Execute dbFinance..spCalc ''' + convert(varchar(20), @date_key, 101) +'''')
The code below produces the exact same text but DOES work:
select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI','Execute dbFinance..spCalc ''05/01/2008''')
Hi
I have a varchar field with values like "Jun 13 1995 12:" I want to change the type of the field to Date and the format of the values to "mm/dd/yyyy".
If I change the type of the field in the design view to Date, SQL gives me an error saying that the type cannot be changed and data would be lost.
So I think changing the dates to the mm/dd/yyyy format first and then changing the field type from varchar to date would work.
Can someone please help me as how to do this.
Thanks
Hi All,
We are trying to create a new view within SQL 2000. We have two colums from seperate tables, 1 is a datetime data type which stores the Date the other is a varchar that stores the time.
What we would like to do is merge the two into one datetime colum so we can work with this one datetime column. Unfortunaly it is not posible to have this entered into the one field at data entry. If needed we can change the time column from a varchar to a datetime datatype.
Also our SQL knowledge is minimal.
Hope this makes sense any help would be deeply appreciated.
Regards
Darren
I have developed a CF application and now need to add some charts to it for admin purposes. I have a form page that uses a calendar widget to let a manager select a date range and submits to display data for that timeframe. I need to convert the formfields to a date for my WHERE clause in my query. My experience to this point has just been simple queries of one table. below is a sample I found and modified numerous time to try and make it work with no success.
Can anyone give me a hand with this?
SELECT COUNT ((CHG_TYP_CD)) AS CHG_TYP,
CHG_TYP_CD
FROM WRK_RQS
WHERE(CAST('form.startdate' AS datetime) >= RQS_SBM_TS)
AND
(CAST('form.startdate' AS datetime) <= RQS_SBM_TS)
GROUP BYCHG_TYP_CD
Hi
I have a table named prodwin in which i have to update a column name mfgdt which is varchar(10) to 7 months which I am able to do but the problem is that since it is only 10 character it is cutting the end of the year for eg
Before the date was
5/22/2000
UPDATE PRODWIN_MM SET MFGDT = DATEADD(MM,7,MFGDT)
Now it is this
Dec 23 200
Please tell me what to do , should i use cast or convert ?
Thanks in Adv
Hi all I would like anyone to help I want 2 convert a datetime to 2008-02-02 21:00 the output must leave the time just 2008-02-02
If (@DateCreated <> 0)
Begin
Set @varSelectSql = @varSelectSql + ' And convert(varchar(10),DateCreated,121) = '+ cast(@DateCreated as varchar(10))
End
Hi,
Can any one help me in that case?
I have 2 colomns one have the month (1,2,3,4,....,12) and the other have the years (2007,2008,....)
I want to merge both in one date field MM/DD/YYYY and the day will always be '01'
Thank you
M.Maraghy
for some odd reason our other programmer used varchar datatype to store dates. he claims it gives him more control. now i am trying to sort it based on date. so i create a procedure:
CREATE PROCEDURE GetAllWeekEnding
AS
Select convert(datetime, we) as we2 FROM tblArchive order by we2
GO
if i use the convert function in the procedure, i'll get an error msg when i run the code. this is the code i am using.
Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringSQL"))
Dim MyCommand As SqlCommand
MyCommand = New SqlCommand("GetAllWeekEnding", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
MyConnection.Open()
Dim mydr As SqlDataReader = MyCommand.ExecuteReader()
While mydr.Read()
DropDownList1.Items.Add(mydr("we2"))
End While
mydr.Close()
MyConnection.Close()
the error message is: No accessible overloaded 'ListItemCollection.Add' can be called without a narrowing conversion
any ideas?
Hi,
I have to change a due_date field with a varchar(10) to a datetime. I created a new table with datetime but when I imported data into the table it errored out due to the datatype. A varchar(10) going into a datetime(8).
Is there way to resolve this without losing data?. or how do you result this problem?.
Thanks for your help,
Jeff.A.
Having an issue getting this done. The datatype in the table is varchar(50). The values in the table are
11122015
11122015
11122015
Nothing has worked so far.
Hi guys.
i have a field in a table which datatype is varchar and i m storing datetime and number in it.. but when i try to use select statement like ( between startdate and enddate) or number (= ) operator it's not working..
select * from table
where cast(Field as datetime) between @startdate and @enddate
second query that i m trying to exectue is
select * from table
where cast(Field as bigint) = @Fieldid
but both are not returning any value...can anyone tells me where i m doing mistake
Hi everyone.
I know, I know, it should have been a datetime from the start...but here's the problem.
I'm trying to sort by my date field but because it looks like: "04/03/2004 12:14:21 PM" it's not ordering it properly using:
ORDER BY [Date]
Are there any work arounds for this? Is there some way of doing:
ORDER BY covert(datetime, [Date], 103) or something?
Cheers
Andrew
In my table I am using varchar to save a date like this 2-dec-2011. Now I have a requirement where i need to select employee who where present in a month so for that I need a query so that i can pull out all the employees who are present in dec like :
Code:
select * from emp_attendence where date = 'dec-2011'
But this returns nothing ...
My current Query takes the DATE value stored in P.CreatedDate and makes it VARCHAR, then stores it in the table. I need to format this to return YYYYMMDD. How would I go about this?
Current Code:
Code:
CAST(P.CreatedDate AS VARCHAR) AS DateEntered,
I have a column (varchar (50)), It has values from 1-12.
I want to cast them to their appropriate months.
Example: 1 = January
2 = Febuary
3 = March
etc.
I have a varchar field which should contain dates but unfortunately it is fill with all sorts of imaginary formats which only the one who has typed may understand.
9+03/24/2006
3/16/2006
11:15AM
5-Oct
8/8/2006
9
.
`06/27/2006
12505
1/9/2007
1/12/2007
1/12/2007
1/15/2007
in a data set of 15000 rows most of them are like '1/15/2007'
and 305 roes are like the rest in the sample.
can you sugest ma a simple way to convert posible data to format '1/15/2007' format which i can use in a SELECT...INSERT STATEMENT and convert the rest to NULL
how do you get it to sort correctly for the date? at the minute its sorting on the characters rather than actual date value
Code Snippet
INSERT INTO @TempItems (OrderID)
SELECT OrderID
FROM Orders o
INNER JOIN Customers c ON c.CustomerID = o.CustomerID
INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID
ORDER BY
CASE @SortOrder --Order ASC
WHEN 0 THEN cast(OrderID as varchar(100))
WHEN 1 THEN cast(c.CompanyName as varchar(100))
WHEN 2 THEN cast(e.FirstName as varchar(100))
WHEN 3 THEN cast(o.OrderDate as varchar(100))
WHEN 4 THEN cast(o.RequiredDate as varchar(100))
WHEN 5 THEN cast(o.ShippedDate as varchar(100))
ELSE '1'
END,
Hi all
With this query
select convert(varchar(16), getdate(), 101)+' '+LEFT(convert(varchar, getdate(), 108),5)
I get
mmddyyyy hh:mm
How can I change my query to get
mmddyyyy hh:mm but no 0 when month from 1 to 9
example can not be: 02/12/2007 03:34
but should be: 2/12/2007 03:34
Don't know why excel not accept month with 0 from 1 to 9
Thks
Daniel
Hi All,
I have 2 varchar fields on MS 2005 table
First field is date and format is 080118(YYMMDD)
and second is salary field like 00002000(positive) and 00002000- (negative)
how can I move them to date and numeric fields on another table....
thanks
I have a column which is
Data type : varchar(10)
Date format : 8/5/2013
I want to convert that column to the following
Data type varchar: (8)
Date format : 20130805
With this clause "select convert(varchar(16), getdate(), 101)" I can get mmddyyyy but
How can I get time such as mmddyyyyhhmm
Thanks
Daniel
I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.
Year Month Productname
2013 11 ACB
2013 11 CDE
I would now like to add another column called date and store these Year Month as a date to my existing table
Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01
Is there a way I can do it for all the columns of the existing table ??
I am using T-SQL I have a column (ColA)that has datetime format and I simply want to pull the next day but date only into ColB
ColA
3/12/2014 12:00AM
3/19/2014 12:00AM
ColB
3/13/2014
3/20/2014
I have been trying the command below but keep getting the error "Conversion failed when convertint the varchar value '03-03-2014' to data type int."
Convert (varchar(10), "StartDate", 110)+1 as Next Day
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
I am trying to create a store procedure inside of SQL Management Studio console and I kept getting errors. Here's my store procedure.
Code Block
CREATE PROCEDURE [dbo].[sqlOutlookSearch]
-- Add the parameters for the stored procedure here
@OLIssueID int = NULL,
@searchString varchar(1000) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @OLIssueID <> 11111
SELECT * FROM [OLissue], [Outlook]
WHERE [OLissue].[issueID] = @OLIssueID AND [OLissue].[issueID] = [Outlook].[issueID] AND [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
ELSE
SELECT * FROM [Outlook]
WHERE [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
END
And the error I kept getting is:
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 18
The data types varchar and varchar are incompatible in the modulo operator.
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 21
The data types varchar and varchar are incompatible in the modulo operator.
Any help is appreciated.