Check If Field Is All Date
Jan 18, 2008
Hey all,
I've been ehre off and on when I've needed help. And I know someone answered my question last time with the exact info I needed. But I seem to have lost the query I saved.
I pretty much need a query that can filter a field I have adn pull up any records that are not in in date format. My current field is text and I'm trying to convert it over to DateTime but am getting stuck. Seems at least one of the records does not have a date format.
Thank you all.
View 5 Replies
ADVERTISEMENT
Jan 27, 2008
I'm about ready to pull my hair out. I have a repeater control, and a date field. If the field is a valid date, I'll use it to calculate and display the person's age. Otherwise, I want to display "N/A". My problem is trying to determine if the date field is null or not.I'm using SQL Server 2005 which allows Nulls in the date field, and for many records I don't have a birth date. It makes sense for these records to leave the date Null. This is my code:Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater1.ItemDataBound If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label) Dim LblAge As Label = CType(e.Item.FindControl("LblAge"), Label) Dim inmate As WAP.prisonmembersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.prisonmembersRow) If System.IO.File.Exists(Server.MapPath("~/images/picts/" & inmate.FILE2 & ".jpg")) Then LabelIcon.Visible = True End If If inmate.DATE_OF_BIRTH Is DBNull.Value Then If IsDate(inmate.DATE_OF_BIRTH) Then LblAge.Text = "Age: N/A" Else LblAge.Text = "Age: " & GetBirthdate(inmate.DATE_OF_BIRTH) End If End If End IfEnd Sub How can I resolve this?Diane
View 7 Replies
View Related
Aug 16, 2006
I have a column say 'ActivationDate' which is a (database timestamp [DT_DBTIMESTAMP]) which I want to replace with an expression in derived columns
The condition is if 'ActivationDate' field is null or '' then 'Null' else 'ActivationDate'
I am struggling to write this condition. Without condition i.e. at present it saves the value in this database '1753-01-01 00:00:00.000'.
In the preview the 'ActivationDate'field does not show any thing so I recon it is either null or ''
View 6 Replies
View Related
May 4, 2006
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
View 3 Replies
View Related
Dec 30, 2003
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
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
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.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
View 6 Replies
View Related
May 17, 2015
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE"Â .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
View 3 Replies
View Related
Oct 17, 2007
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
View 1 Replies
View Related
Dec 28, 2005
Hi, ppleI am trying to check dates. I tried something like this but it doesn't work.
Dim strarray As Array
Try
Dim my As New Database
my.OpenConn()
Dim d As SqlDataReader
Dim dr2 As SqlDataReader
d = my.ExecuteQuery("Select lastLogin, ID from Student")
While d.Read
Dim ld As DateTime = d("lastLogin")
If ld > FormatDateTime(Now) Then
strarray() = strarray() + d("ID")
End If
End While
d.Close()
dr2 = my.ExecuteQuery("Insert into Audit (auditID, ID, auditMsg, updatedDate) values ('200','20','" & strarray() & "','" & FormatDateTime(Now) & "');")
dr2.Close()
my.CloseConn()
Catch x As Exception
Debug.WriteLine(x)
End Try
View 3 Replies
View Related
Jul 20, 2005
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View 2 Replies
View Related
Jul 2, 2007
hi guys, I'm using SQLServer 2005 and I was wondering how I check if some variable is null or not? I tryied to do as the code show but it doesn't work:
declare @number int
set @number = (select number from numbers where number_id = 1)
if (@number = null)
begin
set @number = 12
end
Thank you very much
View 3 Replies
View Related
Mar 11, 2008
help
i need to check each column
and if i don't have the value
than add to a new result row
if not show the missing value
if i have in the field A B C than OK (like day1 + day2)
if i don't have the value A B C than than show it like this a|b|c (like day3)
if i don't have the value c than than show it like this -|-|c (like day4)
if i don't have the value a than than show it like this a|-|- (like day5)
if i don't have the value b than than show it like this -|b|- (like day6)
day1 day2 day3 day4 day5 day6
-------------------------------------------------------------------------
* c * a c a
* a * a c *
a b # b b c
b * $ Q c s
c # 0 0 0 *
---------------------------------------------------------------------new result row
ok ok a|b|c -|-|c a|-|- -|b|-
TNX
View 7 Replies
View Related
Nov 13, 2007
Hi there.
I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE')
Can anyone out there help?
Please advise. Thanks.
Best.
K7
View 1 Replies
View Related
Oct 17, 2005
Obviously, I'm a complete n00b at SQL.
I have a table in Access 2003 with about 6,000 records and there are about 20 records that have duplicate data in the first field (CompID).
I'm trying to make the first field my primary key, so I need to fix these duplicate entry.
I could export to Excel and fix the problem that way, but in the interest of learning SQL I want to figure out how to do it properly.
Thanks in advance for what is hopefully a simple answer.
View 1 Replies
View Related
Oct 15, 2007
I have a long query that returns a table like:
ID Name bool
1 Joe 0
2 James 0
3 John 1
When the boolean field is equal to 1 for any of the records I would like to return an empty record set. What is the best way to do this?
I know I could use IF EXISTS but my query is so long that I would prefer not to repeat the query multiple times. Is there an alternative?
View 5 Replies
View Related
Jul 6, 2015
A date field in a report returns the date value as:
2015-07-01 13:30:27.000
Is there any way I can script this to appear as:
01 July 2015 (or 01-07-2015 or 01/07/2015)Â - basically to cut out the hours, minutes and seconds?
The best I have managed is: CONVERT (varchar(17),DATE,113) AS Date1 but this still leaves me with:
01 July 2015 13:30
View 9 Replies
View Related
Jun 18, 2007
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000". This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007"). I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.
View 1 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
Apr 23, 2015
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID Â Â Â Â Â StartDate
1 Â Â Â Â Â Â 2014-01-01
1 Â Â Â Â Â Â 2014-02-01
1 Â Â Â Â Â Â 2014-05-01-------After Gap Restarted
1 Â Â Â Â Â Â 2014-06-01
1 Â Â Â Â Â Â 2014-09-01---------After last gap restarted
1 Â Â Â Â Â Â 2014-10-01
1 Â Â Â Â Â Â 2014-11-01
2 Â Â Â Â Â Â 2014-01-01
2 Â Â Â Â Â 2014-02-01
2 Â Â Â Â Â Â 2014-03-01
2 Â Â Â Â Â Â 2014-04-01
2 Â Â Â Â Â Â 2014-05-01
2 Â Â Â Â Â Â 2014-06-01
2 Â Â Â Â Â Â 2014-07-01
For Id 1 the start date after the latest gap is  2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01
My Expected Output
id       Startdate
1 Â Â Â Â Â Â Â 2014-10-01
2 Â Â Â Â Â Â Â 2014-01-01
View 4 Replies
View Related
Sep 27, 2005
Hi,
I need to convert a var char field to date field (DD/MM/YYYY)
Current convertion format:
CAST(Report_Date as DATETIME)
How can i convert to Date field with date format of DD/MM/YYYY?
Thanks.
Onn Onn
View 2 Replies
View Related
Mar 27, 2008
Hi,
I am very new to using SQL. Our department usually uses Brio to query the various databases under our control. However, I have recently come against a problem that prompted me to create a custom SQL query which works well as far as it goes. My problem is looking for specific conditions in billing information I receive monthly. I would like to compare on of the date fields contained in the database with a field in the form of YYYYMM (200710, for October 2007) I have created a custom column generator that forms a date from the YYYYMM. I would like, however, do the translation on the fly and make the comparison during the query. The problem is that query without the date check returns a mass of data, only about 1 percent of which is what I want.
The beginning of the SQL query looks like this:
FROM From.T_Crs_Tran_Dtl WHERE T_Crs_Tran_Dtl.Crs_Bill_Yr_Mo IN ('200710', '200711', '200712') AND ((T_Crs_Tran_Dtl.Crs_Cde IN ('1G', '1V') AND (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND (T_Crs_Tran_Dtl.Prev_Stats_Cde IN (' ', 'TK', 'TL') AND T_Crs_Tran_Dtl.Cur_Stats_Cde IN ('TK', 'TL') AND T_Crs_Tran_Dtl.Std_Tran_Typ_Cde='B') OR (T_Crs_Tran_Dtl.Prev_Stats_Cde='UN' AND T_Crs_Tran_Dtl.Cur_Stats_Cde='XX' AND€¦
It is the €œ(T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND€? part of the query that is just plain wrong. The business part of this statement takes the YYYYMM field and turns it into a date which is the last day of YYYYMM.
I hope someone out there can help me with making this comparison.
I appreciate your help.
Bill
View 8 Replies
View Related
Jun 5, 2007
Check the field existence of a database table, if exist get the type, size, decimal ..etc attributes
I need SP
SP
(
@Tablename varchar(30),
@Fieldname varchar(30),
@existance char(1) OUTPUT,
@field_type varchar(30) OUTPUT,
@field_size int OUTPUT,
@field_decimal int OUTPUT
)
as
/* Below check the existance of a @Fieldname in given @Tablename */
/* And set the OUTPUT variables */
Thanks
View 4 Replies
View Related
Feb 11, 2003
Hi all,
I have a field defined as varchar(8) but this field should not contain any letters, needs to be only numbers. How can I validate the data if it contains only numbers? Any ideas?
Thanks,
Jannat.
View 5 Replies
View Related
Nov 11, 2006
Greetings all,
I have a bit of brainteaser that's going to take some serious thought.
I'm importing information from .xls files into a SQL table. The problem is I need to check for dupes and increment certain fields on success of dupe find and then not insert or delete the dupes.
For example, if I have Adam, Turner, 32, 50 already in the table and someone tries to insert Adam, Turner, 32, 50...I need it to increment to read Adam, Turner, 64, 100 and not insert the record. (Notice 2 fields were incremented.)
With that, I have created an INSERT trigger as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[trgInsertCheck]
ON [dbo].[MyTable]
FOR INSERT
AS
BEGIN
EXEC sp_UpdateDupes
EXEC sp_DeleteDupes
END
The first stored procedure checks for dupes and updates if any dupes are found as follows:
--------------------------------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_UpdateDupes] AS
DECLARE @FirstName varchar(20), @LastName varchar(20), @Age int, @Widgets int
DECLARE c1 CURSOR FOR
SELECT FirstName, LastName, Age, Widgets
FROM MyTable
GROUP BY FirstName, LastName, Age, Widgets
HAVING COUNT(*) > 1
OPEN c1
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE MyTable set Widgets = Widgets + @Widgets, Age = Age + @Age
WHERE FirstName = @FirstName AND LastName = @LastName
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
END
CLOSE c1
DEALLOCATE c1
Lastly, it finds all dupes, deletes them and inserts one row back in as follows:
--------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_DeleteDupes] AS
DECLARE @FirstName varchar(20), @LastName varchar(20), @Age int, @Widgets int --declare all fields in table
DECLARE c1 CURSOR FOR
SELECT FirstName, LastName, Age, Widgets
FROM MyTable
GROUP BY FirstName, LastName, Age, Widgets
HAVING COUNT(*) > 1
OPEN c1
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
WHILE @@FETCH_STATUS = 0
BEGIN
--Delete all dupes...the cursor remembers the current record
DELETE FROM MyTable
WHERE FirstName IN (SELECT FirstName FROM MyTable GROUP BY FirstName HAVING COUNT(FirstName) > 1)
AND LastName IN (SELECT LastName FROM MyTable GROUP BY LastName HAVING COUNT(LastName) > 1)
AND Age IN (SELECT Age FROM MyTable GROUP BY Age HAVING COUNT(Age) > 1)
AND Widgets IN (SELECT Widgets FROM MyTable GROUP BY Widgets HAVING COUNT(Widgets) > 1)
--insert the current record back into the table
INSERT INTO MyTable(FirstName, LastName, Age, Widgets) VALUES(@FirstName, @LastName, @Age, @Widgets)
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
END
CLOSE c1
DEALLOCATE c1
Is there an easier way to do this?
(I know Age doesn't make much sense in this example but just replace it with a field would logically be incremented such as wadgets.)
Adamus
View 7 Replies
View Related
Mar 16, 2014
I want to display only the date part of a date field which contains both date & time information.
For example I have the value '2013-11-14 00:00:00.000' in my result set, and ideally I would like to show only '2013-11-14'.
I have looked up the datepart() command, however I can't work out how to return all parts of the date, rather than just the year, month, or day.
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
Jul 4, 2014
I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.
ALTER PROCEDURE [dbo].[ByDateRange]
@Feed VARCHAR(50),
@CreatedDate DATETIME
select * from Date_table where Create_TM = @CreatedDate
View 1 Replies
View Related
Dec 29, 2005
I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett
View 7 Replies
View Related
Nov 3, 2006
Can someone tell me sql query for filtering date field for current day,not last 24hours but from 00:00 to current time?
View 2 Replies
View Related
Feb 26, 2008
Hi,
I need to insert into a column (lets say column x) a date based on the date on another column (lets say column y).
What I need is:
Take the day and month from column x (all records are formated yyyy-mm-dd)
Place it in column y
The yyyy in column y should be - currenct year +1 and no the year in column x.
All help welcome.
View 9 Replies
View Related
Jul 24, 2007
Hi All,
Is there a way to check the data type and the maximum length of a field?
View 1 Replies
View Related
Aug 11, 2015
I have to display check box against each value coming from the table in SSRS. I have tried using html tags in an expression but it is displaying only for the first field.
Ex: my table contains Medical details with Type column with 3 rows Type
Prevention
Relief
Required
in my report i have to display asÂ
View 3 Replies
View Related
Oct 27, 2004
i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:
Code:
IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))
Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table
but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"
thanks
View 2 Replies
View Related
Oct 27, 2004
i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:
IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))
Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table
but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"
thanks
View 3 Replies
View Related
