SQL 2012 :: IsDate In SELECT To Validate / Correct Date
Dec 6, 2014
I am importing data from a legacy system. The date column is a varchar and in the destination DB it is a proper DateTime.
That said I have invalid data and need to clean / accommodate for it as I go. What I need is if the date is valid then insert it, if not then ue a default of '01/01/1900' I am doing the following and it is not working.
CASE
WHEN isDATE(RatifiedDate) = 1 THEN RatifiedDate
WHEN isDate(RatifiedDate) = 0 THEN '01/01/1900'
END as RatDate
<hr noshade size='1' width='250' color='#BBC8E5'>
View 6 Replies
ADVERTISEMENT
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Jan 29, 2008
Presently using CONVERT(VARCHAR(11), [ExpiryDate], 100) to get close to the correct format.Only stumbled across this by accident so am assuming there might be better hidden 'treasures' .Formatting dates seems to be my biggest time-consuming activity and I just don't seem to get better at it!
View 4 Replies
View Related
Apr 5, 2014
I have file name like clm_05-04-2014.I need to valid whether file name consist of todays date in SSIS or Sqlserver.
If the file name is clm_04-04-2014 then this is yesterday's file so not valid.When i run ths SSIS package today the file name should consist of todays date in file else i need to pass message as invalid file .CLM will be common..
View 1 Replies
View Related
Apr 6, 2015
Select '"' + CAST( GETDATE() as varchar(100) ) + '"' as Obs_dt_1
I get this output---> "Apr 6 2015 4:07PM"
But what I really need is for it to show in this format---> "2015-04-06 16:08:05.317" .... How do I do the select ?
View 3 Replies
View Related
Jun 16, 2014
I am working on a query that needs to return the record order number with the most recent requested delivery date.
It seems to work most of the time, but I have found some glitches for some of the items.
example is my item 10702, it is showing 2 records (both valid ones)
Record 1 is order # 10450-0, requested delivery date 03/21/2014
Record 2 is order # 10510-0, requested delivery date 04/29/2014
I need to only get the records with the most recent delivery date, in this example that would be 04/29/2014
This query is what I have so far:
SELECT
s.PriorQuoteNumber
,s.PriorItemNumber
,s.PriorQuoteDate
FROM
(
SELECT
s.SalesQuoteNumberAS 'PriorQuoteNumber'
[code]....
WHERE s.rn = 1What am I missing in my query> how can I change it so it only returns the most recent date?
View 9 Replies
View Related
Nov 29, 2014
I have a select query that results all the dbs and their several backup dates×. Howover I only need the latest backup dates for each of the db.How can I write that in tsql?
View 5 Replies
View Related
Aug 18, 2014
I have date field in table and data contain 2014-08-09 11:13:03.340
when I use smalldatefield I am getting 2014-08-09 11:13:00 I should have got 2014-08-09 11:13:03
Why the 03 is trimming in smalldatefield.
how do I use select query to get 2014-08-09 11:13:03 from original Date records
View 1 Replies
View Related
Jan 15, 2006
Hi,
How does one generate a date string from a DateTime object which is formatted in the correct way for the database?
I want to avoid the possibility of getting the month and the day the wrong way around.
Cheers, WT.
View 1 Replies
View Related
Dec 21, 2012
Code:
SELECT P.ID, P.QTY, T_DATE
FROM PARTS P
INNER JOIN INVENTORY I ON P.ID = I.PART_ID
WHERE P.QTY > 0 AND I.CODE = 'R'
GROUP BY P.ID, P.QTY, I.T_DATE
HAVING MAX(T_DATE) !> DATEADD(MONTH,-12,GETDATE())
[Code] ....
I am trying to make the sql only pull records where the highest date stored is older than a year ago.
So, based on today's date 12/21/2012:
if the highest (max) T_DATE is 3/12/2012 => don't pull that record
if the highest (max) T_DATE is 11/30/2011 => pull that record into the recordset
the statement is off, it is pulling records that should not be included.
View 4 Replies
View Related
May 18, 2004
In a procedure that I am writing I have a select statement and need to say give me such and such results from the table where DateEntered is the first of the previous month??
Could somebody help me with that please?
View 4 Replies
View Related
Nov 15, 2012
I have two table one call Employee and the other table call Target Ratio. They are related via FK EmployeeID
tblEmployee
EmployeeID FirstName LastName
1 John Doe
tblTargetRatio
TargetRatioID EmployeeID EffectiveDate Ratio
1 1 1/1/2012 8
2 1 6/1/2012 5
3 1 9/1/2012 7
My question is how can I query tblTargetRatio table to return correct record for the following cases:
1 EmployeeID = 1 and Date = 03/12/2012 (Expecting Ratio = 8)
2 EmployeeID = 1 and Date = 10/10/2012 (Expecting Ratio = 7)
3 EmployeeID = 1 and Date = 08/12/2012 (Expecting Ratio = 5)
View 2 Replies
View Related
May 24, 2006
Could anyone help med with a select statement with a join between totables. It is to be used in a OLAP cube.I Havde table LedgerBudget and Table Admin. In table admin I can setupa from and to date and also a budgetmodel.The admin have ONE record per OLAP cube.The statement below works fine if I have stated a budgetmodel in tableAdmin.But if no budetmodel stated in table Admin, I want the statement toselect every ledgerbudget with active = 1 and allocatemethod = 0Could anyone help me with this.SELECT LTRIM(dbo.LEDGERBUDGET.ACCOUNTNUM) AS ACCOUNT_ID,dbo.LEDGERBUDGET.STARTDATE AS TRANSDATE, - dbo.LEDGERBUDGET.AMOUNT ASBUDGETFROM dbo.LEDGERBUDGET INNER JOINdbo.ADMIN ON dbo.LEDGERBUDGET.STARTDATE >=dbo.ADMIN.FROMDATE ANDdbo.LEDGERBUDGET.STARTDATE <= dbo.ADMIN.TODATEANDdbo.LEDGERBUDGET.MODELNUM =dbo.ADMIN.BUDGETMODELIDWHERE (dbo.LEDGERBUDGET.ACTIVE = 1) AND(dbo.LEDGERBUDGET.ALLOCATEMETHOD = 0)BR/Thanks
View 6 Replies
View Related
Sep 29, 2015
I have a report that displays its date dropdown badly: 10AM,10PM, 11AM, 11PM etc when it should be 10AM, 11AM, etc. The used measure is Hour Name which looks like "01AM" "02AM" etc. And here is the current query
WITH
MEMBER [Measures].[ParameterCaption] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Code] ....
View 2 Replies
View Related
Jun 22, 2007
This (demo) statement is fine in Access, and so far as I can see, shouldbe OK in SQL Server.But Enterprise Manager barfs at the final bracket. Can anyone helpplease?select sum(field1) as sum1, sum(field2) as sum2 from(SELECT * from test where id < 3unionSELECT * from test where id 2)In fact, I can reduce it to :-select * from(SELECT * from test)with the same effect - clearly I just need telling :-)cheers,Jim--Jima Yorkshire polymoth
View 4 Replies
View Related
Jul 23, 2007
I have a text file I have to load an massage the data. There is a date field which is a varchar where I do a conversion. As of 7-19 the conversion to inline sql stoppped working. Look at example below
declare @dob varchar(08)
set @dob = '02291956'
select convert(datetime,Right(@dob,4) + Left(@dob,4)) as dob
select convert(datetime,Right(dob,4) + Left(dob,4)) as dob from marylanddoc
The 1st statement works, however the select statement now fails with a conversion failure. ANyone seen this pop up. Just as an FYI, I do have 20 records that were processed with a leap year 0229 date that were created by this process before 7/19
View 4 Replies
View Related
Aug 29, 2007
IS THERE AN EQUIVALENT FUNCTION OF ISDATE IN SQL SERVER.?
Funnyfrog
View 3 Replies
View Related
Aug 25, 2007
Hi,
I have a package with a script task. I need to check incoming values from a file to make sure they're dates. I read the file in as one line, then create output columns. Hence, that is why I refer to the AcquistionDate field as array value 5, as this is the 6th column in the file.
In my connection manager, output columns, I have AcquisitionDate field defined as a STRING, length 10. In the flat file, dates look like this: "2007-08-23". I remove the "" with the String.Empty.
So, then I do an IsDate check.
Here is my code:
'Check date columns for date values
If IsDate(rowValues(5)) Then
Row.AcquisitionDate = rowValues(5).Replace("""", String.Empty)
Else
Row.AcquisitionDate_IsNull = True
End If
Problem is, IsDate is evaluating to FALSE. That is, execution goes to the Else portion, and sets all the AcquistionDates to NULL.
What am I doing wrong here?
Thanks
View 4 Replies
View Related
Aug 5, 2014
I am doing search through Date Range but didn't get correct result.
For Example if i created a Task on 5/8/2014 and when i have take Date Range from 1/8/2014 to 6/8/2014 then didn't get 5/8/2014 data in result but when i taken range from 1/8/2014 to 5/8/2014 then got the 5th Date data.
I am taking Range Inclusive Operator(:).
SELECT ( STRTOMEMBER(@FromDate) : STRTOMEMBER(@ToDate) )
View 1 Replies
View Related
Feb 17, 2006
I have a interger stored in x.
I want to use x in a SELECT statement like so :
SELECT * from aTable WHERE A_Column = x
This select statement is then assigned to and passed as a string like :
sql = "SELECT * from aTable WHERE A_Column = x"
How does the x get interpreted correctly ?
View 1 Replies
View Related
Jun 10, 2015
Consider the below script
CREATE TABLE #TEMP(Id int,CreatedBy varchar(30),ModfiedBy varchar(30))
CREATE TABLE #TEMP2 (ID int,SearchedBy varchar(30))
INSERT INTO #TEMP VALUES(1,'James',NULL)
INSERT INTO #TEMP VALUES(1,'James','George')
INSERT INTO #TEMP VALUES(1,'James','Vikas')
INSERT INTO #TEMP2(ID) VALUES(1)
INSERT INTO #TEMP2(ID) VALUES(1)
INSERT INTO #TEMP2(ID) VALUES(1)
Now i want to get the result as
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
SELECT CASE WHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
But when i convert this select to update, i am missing something...
My update is
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE #TEMP2
SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
WHERE #TEMP2.ID=CTE.ID
Only the first record gets updated...
View 9 Replies
View Related
Jun 15, 2015
After SQL server service restart, a column which is set to auto increment jumped 1000. To fix the issue, I had to add T272 trace flag to SQL startup parameters. However, I did not see the column being reseeded after the service restart, it is still showing the 1000 jump. Am I doing something wrong?
Below the log showing the flag being added to the error log:
LogDateProcessorInfoErrorMSG
2015-06-15 22:29:53.850ServerRegistry startup parameters:
-d E:DATAmaster.mdf
-e E:logERRORLOG
-l E:DATAmastlog.ldf
-T 272
View 3 Replies
View Related
Apr 14, 2008
There is a MSSQL function that check the value. Like ISNULL(), ISDATE() and ISNUMERIC(). I don't see a function that check for decimal. If there isn't any then is there an user-defined function for it? I need to be able to validate the string value for decimal before it get assigned to a decimal datatype or T-SQL will run into an error.
I'm using MS-SQL 2000...
Thanks...
View 3 Replies
View Related
Nov 17, 2007
I have a column which is current stored as nvarchar(10), and all of the enteries are either NULL or mm/dd/yyyy.
I am trying to convert the column to smalldatetime using CONVERT or CAST and each time I get an arithmetic overflow error message.
I also tried selecting just the enteries with ISDATE()=1 and converting those to smalldatetime, and still got an arithmetic overflow message.
(I've run ISDATE() several times on the column, and the only rows without ISDATE()=1 are those which have NULL values).
help!
View 3 Replies
View Related
May 30, 2015
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I have table containing 5 columns.
caseid
referenceid
startdate
enddate
caseopendate
[code]...
View 4 Replies
View Related
Mar 18, 2014
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
View 2 Replies
View Related
Jun 2, 2014
I'm writing a view to check record counts in a table that has numerous datasets and therefore various "Activity Dates". Is it possible as part of the SQL statement to have a CASE statement for example so that it can identify the field to use as the activity date?
The field to use is being identified using a seperate table so at the moment I have CASE WHEN FieldToUse = '2' THEN MapCol ELSE '[Activity_Date]' END, where FieldToUse = '2' identifies the date field to use and the MapCol data is the field name to be used as the activity date.
Is this even possible?
View 3 Replies
View Related
Jul 14, 2014
I have a date held in a varchar field in a temporary sql table and I want to convert it into a sql date and it doesn't work. I can replicate this as below -
So I run
select
cast ('14/02/2014' as date)
and I get a conversion failed error.
View 2 Replies
View Related
Jan 11, 2006
hello
how can i select all dates upto todays date and include the first next future date in a given data base
say todays date was the 01/06/2006 (MM,DD,YYYY)
below is a mock data base
id date (MM,DD,YYYY)
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
5 01/20/2006
i want to select all dates equal or less that 01/06/2006 and include the first next future date .. and in this case it would be 01/09/2006
so the results would return
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
View 2 Replies
View Related
Aug 31, 2015
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
View 2 Replies
View Related
Oct 21, 2013
Aim – Convert the following field ”[INSTALLATION_DATE]” date format from “20090709” Into this “2009-07-09” ,
Also create a new column called “BegMonth” which selects first day of the given month of the converted date column
The table is ;
SELECT
[FDMSAccountNo],
[INSTALLATION_DATE]
FROM [FDMS].[dbo].[stg_LMPAB501]
Results
FDMSAccountNoINSTALLATION_DATE
87800000088420030521
Required Results
FDMSAccountNoINSTALLATION_DATEBegMonth
8780000008842003-05-212003-05-01
View 3 Replies
View Related
Dec 6, 2013
I have like 100 columns and most of them work fine, but some will not work. I need to select the columns with correct width for fixed width flat file.
Here is typical SQL statment that works for most of them.
left(RTRIM(A.City) + Replicate(' ', 25) ,25) as [City]
The above one is not working, but many other works fine like the address. Why would it work for some, but not others?
It works fine if I use any char, but not space. i.e.
left(RTRIM(A.City) + Replicate('*', 25) ,25) as [City]
View 5 Replies
View Related
Feb 15, 2008
At the moment i have a query that is dependent on a date which is 42 days before whatever the date may be today.
The statement in my query I have to use is:
MailDate <= '2008-01-05'
I am wondering if i could make that statement automatically take off 42 days from todays date?
View 8 Replies
View Related
