Find The Newest Date Of 2 Date Fields
Aug 20, 2007I've 2 date fields clidlp,clidlc in my data base table. How do I find the newest dates between the fields? Thanks for your help!
View 1 Replies
ADVERTISEMENT
Help - Finding The Newest Date Without Using Max()
Sep 18, 2006Hi,Can anyone help please?select notefield, modifiedonFROM Table1WHERE id = '100426' and(statusfield like '%criteria1%' ORstatusfield like '%criteria2%')Produces a list of records based upon the criteria. I would like to beable to only show the newest dated record, from the modifiedon field.I've tried max(modified) on, but as I am using an aggregate function inthe query I have to use GROUP BY, which notefield does not like as thisis a ntext field.(I get:Server: Msg 306, Level 16, State 2, Line 1The text, ntext, and image data types cannot be compared or sorted,except when using IS NULL or LIKE operator.)Any ideas please?
View 1 Replies View RelatedOnly Need One Record Per Newest Date.
Feb 7, 2008Hey all,
I just discovered this cool forum. I am fairly new to T-SQL, so please bear with me.
Here is my problem. I am trying to return 1 record for each distinct MachineName, based on the most current CreateTime.
I have tried a bunch of different things, but the follwing query seems to get me close, except for all of the records, per MachineName, that aren't the newest CreateTime. Can anyone offer a suggestion I may be missing?
Here is my query:
SELECT
rcv.Name AS MachineName, r.CreateTime, rpv.Path + rpv.Name AS ResourcePool
FROM
dbo.Result_View AS r WITH (NOLOCK) INNER JOIN
dbo.ResourceConfiguration_View AS rcv WITH (NOLOCK) ON r.ResourceConfigurationId = rcv.Id INNER JOIN
dbo.Resource_View AS rv WITH (NOLOCK) ON rcv.ResourceId = rv.Id INNER JOIN
dbo.ResourcePool_View AS rpv WITH (NOLOCK) ON rv.ResourcePoolId = rpv.Id
WHERE
(rpv.Name NOT LIKE 'Archive')
AND
(r.CreateTime > '2/1/2008')
AND
(r.CreateTime = (SELECT TOP (1) r.CreateTime FROM dbo.Result_View))
ORDER BY MachineName
I have added the '2/1/2008' filter, so as not to hammer the DB too hard. It goes back for some time.
Thanks in advance,
Allen
Transact SQL :: Find Latest Start Date After A Gap In Date Column
Apr 19, 2015 My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.
Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01
[code]...
In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01
Transact SQL :: Find Latest Start Date After A Gap In Date Field For Each ID
Apr 23, 2015My 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
Update Fields With Searched First Date Record Fields
Jul 23, 2005Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies View RelatedSQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?
Aug 5, 2015I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
How To Use Convert Date Statement In CmdInsert.Parameters.Add(Date,SqlDbType.DateTime).Value = Date
Sep 21, 2006HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies View RelatedFields Within A Certain Date
Mar 11, 2006Hello.
I need an SQL Statement for SQL Server in VB.Net that will return values from a table for parts that are due for maintenance within 1 month.
Select * from Part Where DateNOverhaul <= '" & DateAdd(mm,-1,Date) & "' is not working.
I have been trying various solutions on the web and am getting confused because none seem to work. Please help me.
Thank you.
SQL Date Fields ASP.NET
Jul 23, 2005Hello !How to save on SQL server a date in this format?dd-mmm-yyyyAlso, I have a web form in ASP.net and I want the users to enter datesonly in the above format. I am going to need to make calculationsbeased on the above.Many Thanks,Marios Koumides
View 2 Replies View RelatedDate Fields
Dec 18, 2007
Im creating a table that links employees to jobs, each employee can only be in one place at once. I have fields for start date and end date. Whats the best way of ensuring an employee cannot be assigned to more than one job at the same time.
Im thinking update/insert trigger that ensure Start Date or End Date being added is NOT >/=existing Start Date and </=existing End Date.
Anyone any better ideas?
Thanks in advance
Alex
How Do You Sort On Two Date Fields ?
Nov 29, 2006I am trying to order two date columns. One Ascending theother Descending. I am using the Orders table of Northwind in SQLServer. No idea why, but it only sorts on the first column I setcriteria on. I think you have to convert it to date as it gets storedas date/time...I triedSELECT TOP 100 PERCENT OrderID, CustomerID, EmployeeID, OrderDate,CONVERT(char(10), RequiredDate, 103) AS RequiredDate,CONVERT(char(10), ShippedDate, 103) AS ShippedDate, ShipVia,Freight, ShipName, ShipAddress, ShipCity, ShipRegionFROM dbo.OrdersORDER BY RequiredDate, ShippedDate DESCBut odd results occuredAny ideas appreciated
View 3 Replies View RelatedMerging Date Fields In Sql
Feb 11, 2008i have two columns in sql which is datetime datatype
fromdate & todate, now i want to display it like fromdate-todate in my front end
select fromdate +'-'+ todate as date from table.
when i use this query , the error is
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Thanks for any help
Sorting Of Date Fields
Dec 27, 2004hi,
When the autogenerate property of datagrid is set to true, how to perform sorting on date fields.
Example:
SELECT DC_NO AS [Document No],CONVERT(CHAR(10),DC_DT,103) AS [Document Date],
[Name] FROM XYZ
Here we convert the date field to the format "dd/mm/yyyy" so when sorting is done it is done in the format "dd/mm/yyyy". But instead sorting should be done in "yyyy/mm/dd" format.
Please Help Me Populate My Date Fields
May 7, 2004Hi,
I have the following table:
CREATE TABLE [Orders] (
[OrdID] [int] ,
[OrderDate] [datetime] ,
[OrderDateONLY] [datetime],
[OrderDayName] [nvarchar] (15),
[OrderMonth] [nvarchar] (25),
[OrderDayOfMonth] [smallint] NULL ,
[OrderWeekofYear] [smallint] NULL
)
GO
The field OrderDate contains BOTH Date and Time information. What I want to do is populate the fields (in bold above) from the OrderDate field... I have tried all that I could think of but nothing works. The last and the best try that I gave was the {B}following[/B] but that too does not work. Can you PLEASE help. Many thanks in advance:
Insert ORDERS
(OrderDateONLY, OrderDayName, OrderMonth, OrderDayOfMonth, OrderWeekofYear)
select
d, datename (dw, d), datename (mm, d), Year (d), datepart (ww, d)
from
(select convert (char (8), OrderDate, 112)as d
from ORDERS
) as x
SQL- How To Compare Max Date Across Fields
Jan 4, 2006Hello, I am wondering how to select the max date from a database view containing multiple date fields for a particular record. I am rusty at SQL and can't seem to get started. Here's an example of the data:
PRCL_IDPROJ_STRT_DTORD_DT ...
1242/3/20063/5/2006
5206/3/20068/2/2006
6412/31/2005
1872/14/20063/16/2006
I need to be able to compare the max date for each PRCL_ID record ("where prcl_id="). In the example above, prcl_id 124 has a max date of 3/5/2006, and prcl_id 520 has a max date of 8/2/2006, etc. The results of the SQL query should return the PRCL_ID and the max date.
Example query results:
PRCL_ID ORD_DT
124 3/5/2006
Please let me know if you can help or if I can provide more info.
Thanks,
Alexkav
Comapring Date Fields
Feb 28, 2006I am trying to compare two date fields; one is a string and one is a getdate() field. I am trying to get them into the same format so I can compare them. What am I doing wrong??? :eek:
select convert(integer, substring(loc_86, 1, 2)) as tmonth,
convert(integer, substring(loc_86, 3, 2)) as tday,
convert(integer, '20'+right(loc_86, 2)) as tyear,
datepart(month, (dateadd(day, -1, (getdate())))) as ymonth,
datepart(day, (dateadd(day, -1, (getdate())))) as yesterday,
datepart(year, (dateadd(day, -1, (getdate())))) as yyear
from ub_chg_tbl join ubmast_tbl
on (ubmast_tbl.patient_nbr = ub_chg_tbl.patient_nbr)
where tmonth = ymonth and tday = yesterday and ty= yyear
Please Help Me Populate My Date Fields
May 7, 2004Hi,
I have the following table:
CREATE TABLE [Orders] (
[OrdID] [int] ,
[OrderDate] [datetime] ,
[OrderDateONLY] [datetime],
[OrderDayName] [nvarchar] (15),
[OrderMonth] [nvarchar] (25),
[OrderDayOfMonth] [smallint] NULL ,
[OrderWeekofYear] [smallint] NULL
)
GO
The field OrderDate contains BOTH Date and Time information. What I want to do is populate the fields (in bold above) from the OrderDate field... I have tried all that I could think of but nothing works. The last and the best try that I gave was the following but that too does not work. Can you PLEASE help. Many thanks in advance:
Insert ORDERS
(OrderDateONLY, OrderDayName, OrderMonth, OrderDayOfMonth, OrderWeekofYear)
select
d, datename (dw, d), datename (mm, d), Year (d), datepart (ww, d)
from
(select convert (char (8), OrderDate, 112)as d
from ORDERS
) as x
Nullable Date Fields ?
Dec 7, 2007Is it bad design to allow nulls on a date field ? I can think of one case such as a sale of an item and populating a field for the date of purchase, only when the purchase took place (and null until then).
comments ?
Date Only Fields In SQL Server
Jul 20, 2005Does anyone know if Microsoft is planning to add a DATE only data typeto SQLServer.I know that you could use a datetime and convert/cast or use datepartto compare, but this can be tedious and error prone.What is the recommended way to compare date-only fields?eg if convert(char(11), @date_field) = convert(getdate(), @date_field)-- do something??
View 3 Replies View RelatedCumulating Fields By Date
Nov 18, 2007Below is the schema and data for a simple table dat looks similar in structure to what I have.
Code Block
/****** Object: Table [dbo].[cumulative] Script Date: 11/17/2007 17:29:58 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cumulative]') AND type in (N'U'))
DROP TABLE [dbo].[cumulative]
GO
/****** Object: Table [dbo].[cumulative] Script Date: 11/17/2007 17:29:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cumulative]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[cumulative](
[PK] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[date] [datetime] NULL,
[wear] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[distance] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
END
GO
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'1', N'21', CAST(0x000098B200000000 AS DateTime), N'1', N'10')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'2', N'22', CAST(0x000098B200000000 AS DateTime), N'2', N'11')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'3', N'23', CAST(0x000098B200000000 AS DateTime), N'3', N'23')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'4', N'21', CAST(0x000098B300000000 AS DateTime), N'4', N'22')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'5', N'22', CAST(0x000098B300000000 AS DateTime), N'3', N'32')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'6', N'23', CAST(0x000098B300000000 AS DateTime), N'1', N'12')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'7', N'21', CAST(0x000098B400000000 AS DateTime), N'4', N'15')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'8', N'22', CAST(0x000098B400000000 AS DateTime), N'2', N'17')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'9', N'23', CAST(0x000098B400000000 AS DateTime), N'2', N'10')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'10', N'21', CAST(0x000098B500000000 AS DateTime), N'2', N'8')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'11', N'22', CAST(0x000098B500000000 AS DateTime), N'6', N'9')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'12', N'23', CAST(0x000098B500000000 AS DateTime), N'3', N'11')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'13', N'21', CAST(0x000098B600000000 AS DateTime), N'4', N'24')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'14', N'22', CAST(0x000098B600000000 AS DateTime), N'3', N'5')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'15', N'23', CAST(0x000098B600000000 AS DateTime), N'2', N'34')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'16', N'21', CAST(0x000098B700000000 AS DateTime), N'3', N'12')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'17', N'22', CAST(0x000098B700000000 AS DateTime), N'1', N'12')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'18', N'23', CAST(0x000098B700000000 AS DateTime), N'3', N'13')
INSERT [dbo].[cumulative] ([PK], [ID], [date], [wear], [distance]) VALUES (N'19', N'23', CAST(0x000098B800000000 AS DateTime), N'5', N'14')
I need a sql code that will calculate and create a cumulative wear and distance fields. Im working in SQL server 2005 (sp2).
What I will like to see for the new fields is like:
Cum_Dist
Cum_wear
10
1
11
2
23
3
32
5
43
5
35
4
47
9
60
7
45
6
55
11
69
13
56
9
79
15
74
16
90
11
91
18
86
17
103
14
117
19
Happy thanksgiving!!
DTS - Clean Up Date Fields Upon Insertion
Feb 4, 2003I am importing Visual FoxPro (6) views into SQL 2000 tables and I am looking for a snippet of code to "clean up" date fields upon insertion. I can insert the views into the SQL tables fine using the tables/views selection of DTS as my source. However, I would like to ensure the date fields are in fact vaild dates and not garbage using the SQL query option of DTS source. I would like to do the insert and cleanup in one step. Do you have a snippet of code to validate a date field that I can use? Thank you for any assistance in advance, Terry.
View 3 Replies View RelatedBETWEEN With Date/Time In Separate Fields
Apr 22, 2008My group is working on a project for school and neither of us have much experience with SQL or ASP. We are pretty much just writing an SQL query to get the data in a certain date range and using ASP to write the tables with the data in it.
Our problem is the SQL database has separate fields for date and time. There is nothing we can do about this because the VB.NET program that populates the tables automatically does this. We can't figure out how to do the BETWEEN statement when they are separate fields. We can do it for a date range and it works fine. An example of what we want to do is show the data from 11:00 am on 04/01/2008 until 4:00pm 04/03/2008.
Does anyone have any idea what we can do? I really want to learn this but it's frustrating because we can't seem to get much help at school.
Creating A String From Date Fields
Jul 20, 2005I have a table with a startdatetime and an enddatetime column such as:StartDateTime EndDateTime what I want to see returnedis:01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/20041:30PM"01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/200410:30AM"Maybe someone has accomplished this aready in a stored procedure andhas an example of how to do it?lq
View 2 Replies View RelatedImporting Null Date Fields
Nov 27, 2006I'm using SQL Server Express and am trying to import a CVS file. The CVS file contains a string field (named DAS) that represents a Date. This field can be null.
I've tried using the DTS Wizard to import this CVS file and convert the DAS field to a Date, which works great until it hits a record with a NULL DAS field. It then throws a convertion error.
Still using the DTS Wizard, I've changed the DataType of the DAS field in the Source file to [DT_DATE], it works fine but all the null dates are converted to 12/30/1899.
Is there a way (DTS Wizard or something else) that will allow me to import these CVS files with null Date fields and keep them as null in SQL Server table.
Thanks for any help,
Jon
Prorating The Date Fields With Cost
Jun 19, 2006Hi all
I have got 3 columns in my table- start date,finish date and cost..in the
following format...
start_date finish_date cost
12/12/2000 20/12/2000 $2000
01/09/2000 12/10/2000 $400
Now if the month and year of the start and finish date is same, the cost
remains same...
but if the month of the two dates are different, i have to distribute the
cost between the two months by calculating the cost for the number of days
for both of the months..
but i am not able to figure out how?
i am using sql 2005 ..
my table has got about 1 million rows...
pls help.....dll is as followsGO
CREATE TABLE [dbo].[DSS](
[Service Start] [datetime] NULL,
[Service End] [datetime] NULL,
[FMIS Code] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Client NHI] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[No of Units] [float] NULL,
) ON [PRIMARY]
Find The Max And Min Date
Feb 1, 2001I'm doing a query using SQL Server. How can I get the max and min date from a database? I can't find any function related to it. Can you help me please ....
Thanks in advance
Andy
How To Convert A Date (date/time) To A Julian Date
Jun 13, 2002In SQL Server 2000:
How do I convert a Julian date to a Gregorian date?
How do I convert a Gregorian date to Julian?
Examples please.
Many thanks in advance.
Gary Andrews
Is It Possible To Save Single Date To A Samlldatetime Fields ?
May 29, 2007Hi,All:
I am using Sql 2000 db to storage my data,and I have a table including a column named "ScanDate" (Type: SmallDateTime),Now
I just want to save the current system date to this column when I save my system data.
when I saved it and I found that column value include the time data,not only date .
So How can I just save date to my samlldatetime typed column ?
thanks in advanced!
Concatenating Three Longint Fields Into A Date Field
Apr 20, 2001Good afternoon one and all,
I have the following problem that I can use some help with :
I have a table in a linked server that has the date stored in three fields (i.e. day,month and year (I have no idea why)). I would like to concatenate these three fields together into a datetime format in a SQL statement
Something like
SELECT ([stc_dd] & '/' & [stc_mm] & '/' & [stc_yy]) AS stkdate
(the above line does not work)
Hope that is clear, thanx in advance for any and all help
Gurmi
UNION With A Max On Split Date/Time Fields
Mar 30, 2000I have 2 tables, each with one ID field, a separate
Date and Time fields and a number of other fields.
The tables contain duplicates on the ID field.
I want to do a UNION keeping only the record with the latest
Date and Time.
This would work:
SELECT MyTab.myKeyField, Max(MyTab.myDate) AS myDate
FROM (SELECT myKeyField, myDate
from Table1
union
SELECT myKeyField, myDate
from Table2) AS MyTab
GROUP BY MyTab.myKeyField
But is only taking care of Date, not Time (some records have
the same date but different times)
The other problem is, when I add more fields, I have to
include them in the GROUP BY clause, and this way I end up
with duplicates (because some other fields have different
values)
Is there a way to do this?
Finding 'null' Date Fields With Query?
Apr 19, 1999I know I am missing something basic, here.
I have a date field in a table. The field is 'allowed Nulls'. When a certain thing happens in the program, the date is filled in.
At various times, I need to do a query to find all the rows that have no dates entered.
What do I use in my where clause? SQL server does not like 'where date = null.'
Thanks,
Judith
Select Null Value Date Fields Record
Sep 28, 1999Hi:
I used this statement, select * from table1 where date1 = null, in SQL Query window and got a few records back. Now, I used the same statement in my VB 5 code and no record is found. How do I select all the records in table1 which do not have values in field date1? Thanks for the help.
-Nicole-