DATE Cast
Oct 19, 2006Hello Everyone,
How do I change the format of the Date:
2006-06-01 00:00:00.000
to this:
06-01-2006.
I believe this can be done with a Cast.
Am I correct?
TIA
Kurt
ADVERTISEMENT
Cast A Varchar As A Date
Mar 5, 2007I 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
Cast From String To Date
Sep 21, 2006I created an SSIS package that pulls in legacy data from a DB2 AS400. There is an In Date field that is stored on the AS400 as OdbcType.Date or DATE. However, when I use SSIS to pull in the data, SQL wants it to be a string so I gave up and let SQL have it's way.
Now in my SELECT statement, I have this:
SELECT TLMST.TLNUMBER, TLMST.DOGNAM, BRDMST.BRDesc, TLMST.INDT
FROM TLMST INNER JOIN
BRDMST ON TLMST.BRDCOD = BRDMST.BreedNumber
WHERE (TLMST.INDT >= @startdate) AND (TLMST.INDT <= @enddate)
ORDER BY TLMST.INDT, TLMST.TLNUMBER
Because I see the dates stored in TLMST are yyyy-mm-dd format, this is not working. It is not pulling any records. I tried to use the CAST statement but I keep getting errors about casting from a string to a date and data overflow errors.
Should I go back and re-do the SSIS or is there a way to pull the records for this? Thanks for the information
Transact SQL :: Cast Zero AS DATETIME2 Or DATE
Jul 22, 2015Casting 0 to SMALLDATETIME or DATETIME is allowed in T-SQL which produces to 1900-01-01. However, you can't cast 0 to DATE or DATETIME2. Why is that?
View 15 Replies View RelatedCast Question - Converting Datetime Into Date
Sep 21, 2005Hi guys,
I knew that the Function Cast can do this but I tried a lot and I dont want to use the MONTH, YEAR, DAY function.
I have a smalldatetime field with a value of this 12/18/2004 4:02:00 PM
I just like to see it like this 12/18/2004
Hope you can help me up. Thanks.
-vince
Cast Varchar Number Into Date Format?
Jul 12, 2013I 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.
Correctly ORDER BY Date That Has Been Cast As A Varchar
May 23, 2008how 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,
Transact SQL :: Using CAST To Change Timestamp Into Date?
Nov 6, 2015I have a server on SQL Server 2008 R2.
I have the following code:
SELECT '1' AS Join_Field, T1.ID AS T1_ID, T2.ID AS T2_ID, T1.TimeStamp AS T1_Timestamp, T2.TimeStamp AS T2_Timestamp, DATEDIFF(Minute, T1.TimeStamp, T2.TimeStamp) AS Difference_Mins, T1.eventid, T1.shiftID,
T1.Value, SD.Shift_Start_Date, T1.StopCode, T1.Status, T1.JobID, T1.StatusDesc, T1.StopDesc, T1.MachineID,
CAST(CASE WHEN CONVERT(date, T1.TimeStamp, 103) < CONVERT(date, SD.Shift_Start_Date, 103)
THEN 1 ELSE 0 END AS int) AS flag, CAST(T1.TimeStamp as DATE) AS TS_Date
FROM PolReporting.Event_M2_T1 AS T1 INNER JOIN
PolReporting.Event_M2_T2 AS T2 ON T1.RowID = T2.RowID INNER JOIN
Intouch.ShiftDates_Grouped AS SD ON T1.shiftID = SD.ShiftID
However when I run it I get a message:
Date Conversion Woes: Cast? Convert?
Nov 7, 2007I have been having some trouble trying to get the date format YYYYMMDD 00:00:00.00 to convert to MM/DD/YYYY. I am using SQL 2005 and Reporting Services. I attempted several variations to solve this problem as outlined below, with the final working conclusion at the end. I am not saying that this is the BEST or the ONLY way to achieve the solution, but it worked for me.
Code Block
---------------------------------------------------------------------------------------
SELECT DATE_TIME AS DATE1
FROM TRANSLOG
--returns value of 20071027 02:26:24.06
---------------------------------------------------------------------------------------
SELECT CAST(DATE_TIME AS SMALLDATETIME) AS DATE1
FROM TRANSLOG
--returns value of 2007-10-27 02:26:00
---------------------------------------------------------------------------------------
SELECT LEFT(CAST(DATE_TIME AS SMALLDATETIME),11) AS DATE1
FROM TRANSLOG
--returns value of Oct 27 2007
---------------------------------------------------------------------------------------
SELECT CAST(SUBSTRING(DATE_TIME,0,9) AS SMALLDATETIME) AS DATE1
FROM TRANSLOG
--returns value of 2007-10-27 00:00:00
---------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(10), DATE_TIME, 103)
FROM TRANSLOG
--although this works with GETDATE(), it does not work with the DATE_TIME field
---------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(10), CAST(DATE_TIME AS SMALLDATETIME), 101) AS DATE1
FROM TRANSLOG
--THIS WORKS!!! Displaying 10/27/2007
Hope this helps somebody someday!
-Jody
Conditional Statement With A Cast From String To Date
Feb 5, 2007My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.
[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]
Cast From String &"&" To Type 'Date' Is Not Valid
Mar 12, 2006Hello, I am using an SQLDataReader called DrCoInfo to check if any matching records exist in a table.
My code is as such:
Line 119:DrCoInfo = comm1.ExecuteReader()
Line 120:While DrCoInfo.Read()
Line 121: If DrCoInfo("DateOfMan") <> "" Then
Line 122: BValid = True
Line 123: End If
However, it fails to work with the message :System.InvalidCastException: Cast from string "" to type 'Date' is not valid.
Any suggestions? Thanks.
Transact SQL :: Cast Or Convert Date In Format YYYY-MM-DD Into New Format Of MM/DD/YYYY?
Nov 27, 2015I have a table that has a DATE field named. AccountingDate that is in the format YYYY-MM-DD. It's not a VARCHAR field. I simply want to convert this date field into the format MM/DD/YYYY and call it New_Accounting_Date.
I've played with various combinations of CAST & CONVERT but haven't been able to get it to work.
Below is my latest effort which returns the error:
Incorrect syntax near the keyword 'as'
What code would work to return a MM/DD/YYYY value for New_Accounting_Date?
Select GLBATCH.AccountingDate,
convert(GLBATCH.AccountingDate as date),101) AS New_Accounting_Date
from GLBATCH
CAST
Jan 16, 2007Hi
Can anyone tell me how I can CAST these fields AS DECIMAL (19, 2) please?
(SELECT ([Total students] - [withdrawn] - [transferred] - [cancelled]) / [total Students] * 100 AS [Percentage Retained])
Thanks
Daniel
CAST
Dec 14, 2007I have
CASE
WHEN (a.type_id in (9))
THEN a.duration
ELSE 0 --CAST ( expression AS data_type )
END 'Time'
I need the duration to be displayed if type is 9 only.
so otherwise id ideally want 'N/A' displayed..
but it won't display it as its not an int ..
is there a way i can just display it temporarily
instaed of 0 ?
Need Help With A CAST
Feb 5, 2008I am having a little trouble with the CAST in my SELECT statement below. Any help is greatly appreciated.
SELECT
group_id_ AS [Group ID],
vendor_id_ AS [Vendor ID] ,
project_id_ [Project ID],
resource_id_ [Resource ID],
vendor_price_ [Old Price],
new_price_ [New Price],
(CAST (new_price_/vendor_price_)-1 AS DECIMAL (4, 2)) AS [Difference]
FROM hbs_vnpq
WHERE (group_id_ = '210') AND (vendor_id_ = '08416') AND (new_price_ >0) AND (vendor_price_ >0)
ORDER BY [Difference]
Server: Msg 1035, Level 15, State 10, Line 7
Incorrect syntax near 'CAST', expected 'AS'.
Specified Cast Is Not Valid
Sep 11, 2006im doing a sum on a table and it either returns a number in decimal format or 'null' . The problem is when it returns null i want it to just make the text say '0.00'. So i did a test on the object that if it returns NULL just print '0.00' but if it is not null it tells me that there is a number there and i want to store that as a decimal and print it out. But i get an error for a type cast when im not it should not even be going to that part of the code. In the code below the first executescaler will return null so it should just go straight to the else. But it gives me the type cast error in the if that shouldnt be seen. The error and code are below. //Borrower NSF FEES
cmd.CommandText = "select sum(itemamount) from postmtdtls where loanid='" + LoanID + "' and Transactioncode = '310'";
object temp = cmd.ExecuteScalar();
if (temp != null)
{
decimal B_NSFFees = ((decimal)cmd.ExecuteScalar());
borrowerPayoff_NSFFees.Text = String.Format("{0:#,#.##}", B_NSFFees).ToString(); //Borrower NSF FEES
}
else
{
borrowerPayoff_NSFFees.Text = "0.00"; //borrowerPayoff_NSFFees.Text = "0.00";
} Server Error in '/WebSite5' Application. Specified cast is not valid. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Specified cast is not valid.Source Error: Line 774: if (temp != null)
Line 775: {
Line 776: decimal B_NSFFees = ((decimal)cmd.ExecuteScalar());
Line 777: borrowerPayoff_NSFFees.Text = String.Format("{0:#,#.##}", B_NSFFees).ToString(); //Borrower NSF FEES
Line 778: }Source File: c:ProgrammingFilesWebSite5InvestorPool.aspx.cs Line: 776 Stack Trace: [InvalidCastException: Specified cast is not valid.]
InvestorPool.GetLoanInfo(String LoanID) in c:ProgrammingFilesWebSite5InvestorPool.aspx.cs:776
InvestorPool.MortAccountText(Object sender, EventArgs e) in c:ProgrammingFilesWebSite5InvestorPool.aspx.cs:660
System.Web.UI.WebControls.TextBox.OnTextChanged(EventArgs e) +75
System.Web.UI.WebControls.TextBox.RaisePostDataChangedEvent() +124
System.Web.UI.WebControls.TextBox.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +7
System.Web.UI.Page.RaiseChangedEvents() +138
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4507
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
Using CAST From Varchar To Int
Oct 15, 2006I am pretty new to SQL for SQL Server 2005. In a view I have a column CAST(Field1 as int). Field1 is a varchar.All works well except when the data is not numeric such as a '?' or '*'.How can I get around this, with a case statement, coalese?I only want to perform the CAST on valid numeric values.The valid values in the varchar field are blank, null,'0','1','2','3','4','5','?','*' and maybe othersThanks
View 11 Replies View RelatedCast Problem
Aug 31, 2007If I run the following query in SQL Server Management Studio it returns the correct results: (Searching the table for the field "SpecimenID (an INT)" against the data entered (a Text Field - "7575-01") from the submitted form.
SELECT ClinicalID, SpecimenID, PatientID, LabID, Accession, Bacillus, Francisella, Yersinia, Brucella, Burkholderia, Coxiella, Staphylococcus, Other, OtherExplanation, CollectionDate, strddlTransportMedium, strddlSpecimenSource, UserName, Test, SpecimenCount, DateAndTimeFROM ClinicalSpecimenWHERE (SpecimenID = CAST('7575-01' AS VARCHAR(50)))ORDER BY SpecimenID DESCHowever, when I try to use the same logic in the ASPX.VB code behind page, as follows below, I either get an error message (Syntax error converting the varchar value '' to a column of data type int.) or record not found.... Can someone please explain what I am missing here....
MySQL = "SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent & "') AS VARCHAR(50))"
*"AccessionPresent" is the value of the text field retrieved from the form.
I guess what I am really asking is how can I search for an INT value in a table using a VARCHAR Field.
Thank you for any or all assistance !!!
Cast Or Convert
Feb 18, 2008Hi,I want to turn int to double/decimal in microsoft sqlSHould i use cast or convert?if so, how i do it thanks,
View 1 Replies View RelatedIf / IIf - Cast Exception
May 29, 2008Hi over there,I hope this question is not too simple, but I didn't manage to figure out why...I would need an explanation for following issue:I'm reading data from a database (MSSQL) and it the column "PersonBirthday" is DBNull.I wanted to prevent the error (Textbox.Text = DBNull) with an IIF. The thing is I get thistypecast exception:"Conversion from type 'DBNull' to type 'Date' is not valid." This code is NOT working, why? txtPersonBirthday.Text = IIf(IsDBNull(.Item("PersonBirthday")) =
True, String.Empty,
CDate(.Item("PersonBirthday")).ToString("yyyy-MM-dd")) When I'm using this code, which is for me obviously the same, just with an if-block it works,and I want to know why - please explain. If IsDBNull(.Item("PersonBirthday")) Then txtPersonBirthday.Text = String.Empty Else txtPersonBirthday.Text = CDate(.Item("PersonBirthday")).ToString("yyyy-MM-dd") End IfThanks in advance,cheers,uquandux If IsDBNull(.Item("PersonBirthday")) Then txtPersonBirthday.Text = String.Empty Else txtPersonBirthday.Text = CDate(.Item("PersonBirthday")).ToString("yyyy-MM-dd") End If
Varbinary Cast
Sep 16, 2004Can anyone tell me what varbinary casts to ?? can I do this int[] temp=(int)DataReader["varbinaryColumn"]. Or is byte[] temp the appropiate protocol.
View 1 Replies View RelatedSpecified Cast Is Not Valid
Apr 7, 2006Can't seem to find why I'm getting this error: Specified cast is not valid.
Ok, using a stored procedure for SQL Server 2000 and here is the main part of it:
SELECT id, rank, firstName, lastName, service, status, createdTime FROM accessRequest WHERE lastName LIKE @tLastName AND firstName LIKE @tFirstName
And the C# code behind from the class file:
SqlDataReader spResults;
conn.Open();
spResults = command.ExecuteReader();
while( spResults.Read() )
{
AccessRequestSearch request = new AccessRequestSearch( (int)spResults.GetInt32( 0 ), spResults.GetString( 2 ), spResults.GetString( 3 ), spResults.GetString( 1 ), spResults.GetString( 4 ), spResults.GetString( 5 ), Convert.ToDateTime(spResults.GetString( 6 )));
searchResults.Add( request ); // Add to Array List
}
spResults.Close();
The part in red is where I think it's happening because that is what I just added to the request. createdTime in the table is set as DateTime.
Can anyone see what I am missing here?
More info is available if needed.
Thanks,
Zath
Help With Conver/Cast
Oct 26, 2002I have a table with a column called Sample_Date_and_Teime with a row definition of char(16). What is stored in this table is a date (ie. 1020621141517000) The first char is the century, the next are the year, the next two are the month, the next two are the day, the next 2 the hour, the next two are the minutes, and the next 2 are the seconds and the last 3 are to be ignored... what I need to do is write a select statement that converts this column into a datetime so that I can then do a insert into a new table by selecting from this table based on a date range. PLEASE HELP:confused: :confused: :confused:
View 1 Replies View RelatedCAST/Convert
Mar 15, 2006I need Query syntax to cast/convert values as follws.
Val.: 00005000010260002180 - Result must be: 5.1.2600.2180
Val.: 00005000000213400001 - Reslut must be : 5.0.2134.1
Dots must also be contained in result
Check If Cast Is Possible
Feb 10, 2005I use to import data from DBF Clipper databases into SQL Server. When a table is just imported its date fields have string format. I need to copy their data to tables of database where they ahve to be converted into date. Direct operator INSERT doesn't convert properly (I've not successed in changing default date format so it'll be covertable) but using CAST I can get result of strings like 13.05.1970 0:00:00 as datetime type. But not all records can be coverted this way. For ones can't be converted I've solved to make NULL fields there. But I don't know how to make CAST operation return NULL when convertion isn't possible. The query
INSERT INTO people_temp
(reg_num, surname, stname, patronymic, foreing, gender, birthdate, fam_pos, dwell_type, children, nation, par_not, region, stud_fml, parn_fml,
com_prob, sp_prob, sn_passport, nn_passport, dv_passport, wg_passport)
SELECT STUDENTs_temp.REG_NOM, STUDENTs_temp.FAMILY, STUDENTs_temp.NAME, STUDENTs_temp.PARN_NAME, STUDENTs_temp.INOSTR,
STUDENTs_temp.SEX,
CAST(PSPR_temp.DATA_BORN AS smalldatetime), PSPR_temp.SEM_POL, PSPR_temp.XAR_JT, PSPR_temp.CHILDREN,
PSPR_temp.NATION,
PSPR_temp.SV_ROD1 + PSPR_temp.SV_ROD2 AS Expr1, PSPR_temp.REGION, PSPR_temp.STUD_FML,
PSPR_temp.PARN_FML,
PSPR_temp.OB_STAJ, PSPR_temp.SP_STAJ, PSPR_temp.SN_PASPORT, PSPR_temp.NN_PASPORT, PSPR_temp.DV_PASPORT,
PSPR_temp.WG_PASPORT
FROM STUDENTs_temp INNER JOIN
PSPR_temp ON STUDENTs_temp.REG_NOM = PSPR_temp.REG_NOM
gets an error 'The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value'. Tell me please how can make type casting return NULL if convertion isn't possible.
CAST Statement
Feb 28, 2006It must be something I'm overlooking but I keep getting an error message that this statement can't parse.
UPDATE product SET supplier = LEFT(supplier,LEN(supplier-4)) + CAST( '2100' AS varchar(4)) WHERE actualid = 'IS2100-CO2-CO2-0-4-I'
Any help would be greatly appreciated.
CAST Or Convert
Oct 8, 2014Have the following in my SELECT statement, which I'm having issues with when I modify it to include a CAST or a CONVERT.
CONCAT(PER.[PERSON-REF],ROW_NUMBER() OVER (PARTITION BY PER.[PERSON-REF] ORDER BY TEN.[tenancy-ref])) AS 'ID'How do I convert or cast it to a varchar (20)?
How To Run CAST On Duration
May 13, 2015I found this nifty code on stackoverflow that works well but I'm trying to send the results to a text file and the column lengths are huge. I used CAST for the first line and it worked great but I can't seem to make it work with duration. Here's the original code:
SELECT
j.name,
h.run_status,
durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
' ','0'),4,0,':'),7,0,':'),
[code]....
how to run a CAST on DURATION?
Cast Formula
Apr 10, 2008I have a quick question. created a report with this formula below. It works when I put a date range from 1-1-2007 to 2-1-2007, but when I put in a date range of 03-01-2008 through 03-31-2008. i get an error message of arithmetic overflow error converting numeric to data type numberic.
So instead of haveing the formula below be (4,2) I put it as (5,2) and now it work. Why is that?
CAST(clm_sppo / clm_tchg * 100 AS decimal(4, 2)) AS PercentSavings
Cast DATETIME
Apr 24, 2008Hi all,
i need help for deleting the time from a datetime type
what i mean is that instead of
2007-08-15 01:30:00.000
i would get
2007-08-15 00:00:00.000
many thanks
Cast String To Int
Apr 27, 2008Hi ,
I have a problem with casting a string number to int.
for exmaple i have the number '0110' (string) and i would like it to be 110. or '0001' to be 1.
i tried to do:
cast(MyNUM as int) .
but i get "Syntax error converting the nvarchar value 'O322' to a column of data type int."
is anyone have any idea how to solve it ?
thanks
Help In Cast Function!
Mar 8, 2006Hi,
I have a table with the following structure:
Table: Meeting
ID integer not null
Desc text null
Here Col 'Desc' is a text type stores description. I have used the following UPDATE query:
UPDATE Meeting SET Desc='New Updated text......' + ' ' + CAST(Desc as varchar) WHERE ID=100
Issues:
1. Since i did'nt specify lenth for varchar it takes only 30 chars from existing value and added to the edited text.
2. If i give like this CAST(Desc as varchar(8000)); it might take only first 8000 chars; remaining would be truncated correct? I assume that will happen.
3. If i don't use CAST i get error msg: "Invalid operator for type.."
Is there any work around in this situation? or better way to do it.
Looking for your help.
Thanks
BoB
Error When Using Cast
Dec 10, 2007Hi, I'm very new to SQL. Trying to sum two fields, but I have to change the datatype first. Here's the code and the error message I receive. Any help would be appreciated.
SELECT TS_RESPONSIBLE, TS_STATUS, TS_USER_07, SUM(TS_USER_07 * TS_STATUS) AS value
FROM TEST_54_VW
GROUP BY TS_RESPONSIBLE, TS_STATUS, TS_USER_07
WHERE CAST(TS_USER_07 AS INTEGER)
Incorrect syntax near the keyword 'WHERE'.