date manipulation
-
declare @tbldate table(stdid int, sdate date, eddate date) insert into @tbldate values (100, '20100102', '20100505'), (101, '20100203', '20100302' ) i want my result set as check if day in date is 01 if not then make day as 01 and increment month my 1. i have the solution but thats in 2 temporary table and i want full solution in a single table.
-
declare @tbldate table(stdid int, sdate date, eddate date) insert into @tbldate values (100, '20100102', '20100505'), (101, '20100203', '20100302' ) i want my result set as check if day in date is 01 if not then make day as 01 and increment month my 1. i have the solution but thats in 2 temporary table and i want full solution in a single table.
You mistake is that you are treating a date as a string - lesson 1: A DATE IS NOT A STRING. - learn this lesson now and it will save you a lot of pain in the future. You can use
DATEADDwithin a loop. You still need to generate the sequential numbers (@day) 1-100, then you can do something likeDECLARE
@Day INTSET @Day = 1
WHILE @Day < 101
BEGIN
SELECT DATEADD(d,@Day,GETDATE())
SET @Day = @Day+1END
Never underestimate the power of human stupidity RAH
-
You mistake is that you are treating a date as a string - lesson 1: A DATE IS NOT A STRING. - learn this lesson now and it will save you a lot of pain in the future. You can use
DATEADDwithin a loop. You still need to generate the sequential numbers (@day) 1-100, then you can do something likeDECLARE
@Day INTSET @Day = 1
WHILE @Day < 101
BEGIN
SELECT DATEADD(d,@Day,GETDATE())
SET @Day = @Day+1END
Never underestimate the power of human stupidity RAH
dear i also can do this but i by taking variable u can pass single value but i want to achieve this by query.
-
dear i also can do this but i by taking variable u can pass single value but i want to achieve this by query.
scottichrosaviakosmos wrote:
achieve this by query
I don't think it can be done! The only work around I can think of is to create a UDF (function) which returns a table. Basically move the code into a function, have it return aresult set and use the result set to join to some thing else. I have one called GenDates and use it like
Select * from Gendates(StartDate, Days)
Never underestimate the power of human stupidity RAH
-
declare @tbldate table(stdid int, sdate date, eddate date) insert into @tbldate values (100, '20100102', '20100505'), (101, '20100203', '20100302' ) i want my result set as check if day in date is 01 if not then make day as 01 and increment month my 1. i have the solution but thats in 2 temporary table and i want full solution in a single table.
How about "case when" ?
case when
cast(right(@dates,2) as int) > 1
then
convert(char(8),dateadd(month,1,cast(left(@dates,6) + '01' as datetime(8))),112)
else
@dates
end