SELECT DISTINCT
CONVERT(varchar(20), CAST(MONTH(dbo.classgiven.classdate) AS varchar(2)) + '-01-' + CAST(YEAR(dbo.classgiven.classdate) AS varchar(4)), 1) AS ok,
dbo.classT.discount
FROM dbo.classT INNER JOIN
dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode
WHERE (dbo.classT.coned IS NOT NULL) AND (dbo.classT.discount = '-1')
why wont ok come out as a date and sort as a date?
i looks like a date but sql server will not sort OK in order. my god please help
Hi, I have julian date in the format of 2003182 and I need to convert into our regular calendar date. Anybody have any SQL statement for this. Thanks, Ravi
The dB dates are store in db as 20140303 when I bring this to my application to pick a date, the apps shows the date as 20140303 how can I change this to show in the format 03/03/2014 in my application?
I am trying to convert a decimal date value to a date value that I can use in formulas but am having a lot of trouble.My date value currently shows in decimal format YYYYMMDD. I want to convert this to a date so I can then find the number of days between two dates.I have tried convert (datetime, convert(varchar(8),left(qhstdt,8))) with qhstdt as my decimal date field but I receive the error message below:
Error: SQL0204 - CONVERT in *LIBL type *N not found.
I have also tried converting it using (year(QHSTDT)*10000+100*month(QHSTDT)+ day(QHSTDT))) but when I convert the dates using this formula, I can get an incorrect number of days when I try to subtract one from the other.What formula can I use to convert my YYYYMMDD field to a format that will allow me to compare number of days between two dates?
I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).What I want is to do the following:Say you have these records:person | date-time-------+---------------------------jim | 06/02/2004 00:05:52jim | 06/02/2004 05:06:21jim | 06/02/2004 05:46:21jim | 06/15/2004 11:26:21jim | 06/15/2004 11:35:21dave | 06/04/2004 09:35:21dave | 06/04/2004 11:05:21dave | 06/06/2004 10:34:21dave | 06/08/2004 11:37:21I'd like the results to count how many days and returnperson | days-------+-------jim | 2dave | 3How would I do this?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]
I have an old table (table1) and a new table (table2). I need to move some of the data from table1 to table2. For my example, table1 contains 1 field that is a DateTime, we’ll call it table1_Date. table2 also contains 1 field that is a SmallDateTime, we’ll call it table2_Date. I want to do something like this: Insert into table2 table2_Date Select table1_Date From table1 Where ….. I am getting the following error: The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. How can I go about converting this on the insert?
hey guys, i need help in converting int to date. i've googled all over and most of all recommend using CAST or CONVERT. however, when i tried it to my SQL command, it didn't work. was i using it wrong? please help me figure it out.
here is the command i used:
Code:
select convert(datetime, starttime, 120) as starttime from table
one of the example of starttime contained in the db is 1170349200.
i'm almost desperate.. help please.. thanks in advance..
I have a function on a MSSQL 2000 db like the following:create function GetDateOnly (@pInputDate datetime)returns datetimeasbeginreturn cast(convert(varchar(10), @pInputDate, 111) as datetime)endwhich returns the date with the time all zeros ( '2006-05-09 00:00:00' ). I tried to implement this same function on an MSSQL 7 server and I get errors. Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'function'.How can I code something similar in version 7? I'm assuming it's a version difference that is causing my problems.Thanks,Randy
I have a table that has a nvarchar field of (12) I need to convert this to a smalldatetimefield.
I get the following message
Error Source: Microsoft Data Transformation Services (DTS) Data Pump Error Description:Insert error, column 1 ('timeid', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Error Help File:sqldts80.hlp Error Help Context ID:30702
Can someone please tell me how to convert this field without getting this message?????
I have a field that stores a date as text (121205). I need to convert this field to a date, but since it is text, I cannot figure out how to do it. Any ideas??? Thanks! :D
I am using SQL server 2008 R2. I have a query where I have dates in different columns pulled from my database among other things. The client is requiring that the date be formatted as mmddyyyy. The database returns the date value as varchar (8) and formatted as yyyymmdd. I have tried several things to get this to be converted but haven't had any luck. My query is as follows.
select appointments.last_name as 'patient last name', appointments.first_name as 'patient first name', appointments.address_line_1, appointments.address_line_2, appointments.city, appointments.state, appointments.zip, appointments.home_phone, appointments.appt_nbr, appointments.appt_date, person.date_of_birth, person.sex, patient.med_rec_nbr, provider_mstr.national_provider_id, provider_mstr.first_name,
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 ?
Hello,I try to convert a pseudo datetime string into a date. In Oracle I can doto_date( MyDate, 'yyyymmddhh24miss' ); how I can do this with MS SQL ?thanks and regardsMark
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Here is the About information from . Microsoft SQL Server Management Studio 9.00.1399.00 Microsoft Analysis Services Client Tools 2005.090.1399.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 5.0 6.0 Microsoft Internet Explorer 7.0.5730.13 Microsoft .NET Framework 2.0.50727.1433 Operating System 5.1.2600
I know this is not the best way to use datetime or conversions, but I did not write it, I am just supporting it.
The strange thing is, the above code has worked every week since January 2006?
The even stranger thing is, it runs on perfectly well on Server 2000 and on the version below that has not had any recent service packs installed.
Microsoft SQL Server Management Studio 9.00.3042.00 Microsoft Analysis Services Client Tools 2005.090.3042.00 Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447) Microsoft MSXML 2.6 3.0 6.0 Microsoft Internet Explorer 6.0.3790.1830 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790
Is this a bug, or have I lost the plot?
Thanks in advance for any feedback.
P.S: The style code 101, if changed to 103 allows the code to work. But, when apllied to the pre-RTM server, is returns the same error message.
i want to display a column of my table which is interger into date part. i seems to get error. i know how to cast the individual integer part into date and time.
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
I am trying to import a txt file that has a string for it's date. How do I change it over in the import wizard I've tried going to advance then changing from string to date or date time but I get an error.
I am trying to convert the birth dated from 19591229 to 12/29/1959 and using below code but its not giving me in that format its printing line 1959-12-29. how do I do this?
converting date and/or time from character string.Got this error message: "Msg 241, Level 16, State 1, Line 7...Conversion failed when converting date and/or time from character string."
Here's my query: DECLARE @StartDate AS varchar(30) DECLARE @EndDate AS varchar(30) SET @StartDate = (CONVERT(varchar(20),'01-05-2014', 101)) SET @EndDate = (CONVERT(varchar(20),'31-05-2014', 101))
I have a query where I need to see in 2 seperate columns, the Actual date of deliveries and collections, and the Invoice Week number of the same. The problem is that in our ERP system delivery date (di.ddate) is a date field, and the collection date is nvarchar. This is my query and sample results below, as you can see I'm getting nulls for the collection lines. How can I do this efficiently
select di.dticket, di.item, case di.item when 'DEL' then di.ddate when 'COL' then CONVERT(datetime,di.text2,103) end ActDate, Case when di.item = 'DEL' then (select YearWeek from MCSReports.RptRevenueGuideWeekLookup where di.ddate between FromDate and ToDate) end InvWeek
from deltickitem di where item in ('DEL','COL')
Sample results below:
dticket item ActDate InvWeek -------------------- -------------------------------------------------- ----------------------- -------- 01-000002 DEL 2011-07-05 15:53:00.000 2011-27 01-000002 COL NULL NULL 01-000004 DEL 2011-07-05 16:04:00.000 2011-27 01-000004 COL NULL NULL 01-000005 DEL 2011-07-05 16:08:00.000 2011-27 01-000005 COL NULL NULL 01-000006 DEL 2011-07-05 16:19:00.000 2011-27 01-000006 COL NULL NULL 01-000007 DEL 2011-07-05 16:24:00.000 2011-27 01-000007 COL NULL NULL 01-000008 DEL 2011-07-05 16:29:00.000 2011-27 01-000021 DEL 2011-07-05 08:48:00.000 2011-27
I am very new to SSIS though I have written hundreds of SQL 2000 dts' with all kinds of active scripts doing all kinds of things. I am now trying to convert them to SSIS and since scripts don't come through the migrator I am doing them by hand once again. I have the package created, but one of the input fields on the text file is YYYYMMDD , but the database field it is going into is a datetime so I need to convert it to a valid date format so that the conversion will take place. I tried data conversion task but that doesn't work, now I'm assuming I need to use the derived column, but not sure and don't have a clue how to use that, can anyone help? Thanks bostonrose
