Date Range

Hi Everybody,
I need to return a values in a specific format
I have a Table which has Start_date and End_Date as
Start_Date End_date
'1/28/2006' and '1/31/2006'
I want to return values as
Start_date End_Date
1/28/2006 1/29/2006
1/29/2006 1/30/2006
1/30/2006 1/31/2006
How do I do this? I dont care If it is a function or a sp or a Select
statement.
Any help is sincerely appreciated.
TIA,
SunnyYou want to use ISO-8601 formats so your code will port. Next,
formatting in done in the front end and not in the database.|||Sunny
Inserting dates into SQL Server you'll have to use YYYYMMDD format.
If yopu want to display dates on the client , use FORMAT(VB6) for instance.
"Sunny" <Sunny@.sunny.com> wrote in message
news:evXXBWgJGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi Everybody,
> I need to return a values in a specific format
> I have a Table which has Start_date and End_Date as
> Start_Date End_date
> '1/28/2006' and '1/31/2006'
> I want to return values as
> Start_date End_Date
> 1/28/2006 1/29/2006
> 1/29/2006 1/30/2006
> 1/30/2006 1/31/2006
> How do I do this? I dont care If it is a function or a sp or a Select
> statement.
> Any help is sincerely appreciated.
> TIA,
> Sunny
>|||If I am understanding what you want correctly, you want a row for each day
between the start and end dates in your table.
For this you can use a calendar table as the example below shows:
CREATE TABLE Calendar
(
DateValue DateTime
)
GO
CREATE TABLE MyDates
(
ID INT NOT NULL PRIMARY KEY,
StartDate DateTime,
EndDate DateTime
)
GO
-- FIll The calendar Table
DECLARE @.TempDate DateTime
SET @.TempDate = '2005-11-01'
WHILE @.TempDate <= '2006-06-30'
BEGIN
INSERT INTO Calendar(DateValue)
VALUES (@.TempDate)
SET @.TempDate = DATEADD(d,1, @.TempDate)
END
-- Put data in MyDates
INSERT INTO MyDates (ID, StartDate, EndDate)
SELECT 1,'2005-12-28','2005-12-31'
UNION
SELECT 2,'2005-12-28','2006-01-05'
-- Now select the date ranges for a single row
SELECT DateValue AS StartDate,
DATEADD(d,1,DateValue) AS EndDate
FROM Calendar
INNER JOIN MyDates
ON Calendar.DateValue >= MyDates.StartDate
AND Calendar.DateValue < MyDates.EndDate
WHERE MyDates.ID = 1
-- Now demonstrate that it works using multiple rows
SELECT MyDates.ID AS ID,
DateValue AS StartDate,
DATEADD(d,1,DateValue) AS EndDate
FROM Calendar
INNER JOIN MyDates
ON Calendar.DateValue >= MyDates.StartDate
AND Calendar.DateValue < MyDates.EndDate
WHERE MyDates.ID IN (1,2)
DROP TABLE Calendar
GO
DROP TABLE MyDates
GO
I do agree with the other posts that you should use the ISO date standard
(as shown in my example) and then format the dates on the client to the
user's desired display format.
"Sunny" <Sunny@.sunny.com> wrote in message
news:evXXBWgJGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi Everybody,
> I need to return a values in a specific format
> I have a Table which has Start_date and End_Date as
> Start_Date End_date
> '1/28/2006' and '1/31/2006'
> I want to return values as
> Start_date End_Date
> 1/28/2006 1/29/2006
> 1/29/2006 1/30/2006
> 1/30/2006 1/31/2006
> How do I do this? I dont care If it is a function or a sp or a Select
> statement.
> Any help is sincerely appreciated.
> TIA,
> Sunny
>|||Hi Sunny,
You might want to use DD MMM YYYY format as in the UK there are not 28
months in a year and we use the format DD/MM/YYYY, alternatively use
YYYYMMDD.
Anyway, this SQL using a sequence table will do what you require...
declare @.seqtable table (
seqnumber tinyint not null
)
insert @.seqtable ( seqnumber ) values ( 1 )
insert @.seqtable ( seqnumber ) values ( 2 )
insert @.seqtable ( seqnumber ) values ( 3 )
insert @.seqtable ( seqnumber ) values ( 4 )
insert @.seqtable ( seqnumber ) values ( 5 )
insert @.seqtable ( seqnumber ) values ( 6 )
insert @.seqtable ( seqnumber ) values ( 7 )
insert @.seqtable ( seqnumber ) values ( 8 )
select start_date = dateadd( day, seqnumber, '28 jan 2006' ),
end_date = dateadd( day, seqnumber+1, '28 jan 2006' )
from @.seqtable
order by start_date
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Sunny" <Sunny@.sunny.com> wrote in message
news:evXXBWgJGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi Everybody,
> I need to return a values in a specific format
> I have a Table which has Start_date and End_Date as
> Start_Date End_date
> '1/28/2006' and '1/31/2006'
> I want to return values as
> Start_date End_Date
> 1/28/2006 1/29/2006
> 1/29/2006 1/30/2006
> 1/30/2006 1/31/2006
> How do I do this? I dont care If it is a function or a sp or a Select
> statement.
> Any help is sincerely appreciated.
> TIA,
> Sunny
>|||What formatting?
The poster wants to expand a date range into a range of rows, 1 day per row.
Are we now not to do these simplest of things in the RDBMS to keep things
simple sweet?
Why on earth would you want to go to the efforts of doing this in a 3gl when
its such a simple task using SQL?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138671987.369279.135430@.g49g2000cwa.googlegroups.com...
> You want to use ISO-8601 formats so your code will port. Next,
> formatting in done in the front end and not in the database.
>|||Sunny,
will it help you?
SELECT DATEADD(D, S.seq - 1, P.beg_date) AS today,
DATEADD(D, S.seq, P.beg_date) AS tomorrow
FROM (SELECT hundred * 100 + ten * 10 + unit + 1
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Hundreds(hundred)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Tens(ten)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Units(unit)) AS S(seq),
(SELECT CAST('20060128' AS DATETIME),
CAST('20060131' AS DATETIME)
UNION ALL
SELECT CAST('20060225' AS DATETIME),
CAST('20060303' AS DATETIME)
UNION ALL
SELECT CAST('20051225' AS DATETIME),
CAST('20060103' AS DATETIME)) AS P(beg_date, fin_date)
WHERE S.seq <= DATEDIFF(D, P.beg_date, P.fin_date);
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)

Date problem

I have a simple query which returns records between specific dates however
it seems I have to use the mm/dd/yy method however im in the UK and we use
dd/mm/yy how can I get sql server to use the right one?
--
Regards
Gary Howlett
Systems Developer
www.rainbowgrp.co.ukJust ust the "yyyymmdd" format, it is independent of your current settings.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Gary Howlett" <gary@.rainbowgrp.co.uk> wrote in message
news:QPI_a.3395$z7.558306@.wards.force9.net...
> I have a simple query which returns records between specific dates however
> it seems I have to use the mm/dd/yy method however im in the UK and we use
> dd/mm/yy how can I get sql server to use the right one?
> --
> Regards
> Gary Howlett
> Systems Developer
> www.rainbowgrp.co.uk
>|||Hi Gary,
The problem here is not SQL Server but the machine SQL
runs on.
You will need to change the local setting for that machine
to England (i.e. Pounds, dd/mm/yyyy ect) on that machine.
If its a case that you are remove connecting then you can
follow Dejan sugestion or use the 'style' option on a cast
or convert function (for further details see bol).
Peter
>--Original Message--
>I have a simple query which returns records between
specific dates however
>it seems I have to use the mm/dd/yy method however im in
the UK and we use
>dd/mm/yy how can I get sql server to use the right one?
>--
>Regards
>Gary Howlett
>Systems Developer
>www.rainbowgrp.co.uk
>
>.
>|||Use the ISO standard YYYYMMDD format when passing dates to SQL Server, then
there is no confusion no matter where you put your server, who messes with
regional settings, or what SET DATEFORMAT or SET LANGUAGE options have been
configured.
"Gary Howlett" <gary@.rainbowgrp.co.uk> wrote in message
news:QPI_a.3395$z7.558306@.wards.force9.net...
> I have a simple query which returns records between specific dates however
> it seems I have to use the mm/dd/yy method however im in the UK and we use
> dd/mm/yy how can I get sql server to use the right one?
> --
> Regards
> Gary Howlett
> Systems Developer
> www.rainbowgrp.co.uk
>

Date formating - Military

Hello all,

I am trying to import an excel spreadsheet that has a specific date column. The column is saved as military time. For example, one of the records is:

04-12-2007 09:30

But, when i import the data into sql 2005, the above date is displayed as:

2007-04-12 09:30

I need it to display like the same way that it's being displayed in the excel spreadsheet.

My field in sql is nvarchar(100) and it still doesn't work.

Any ideas?
Thanks in advance.

Hi Friend,

You can play with the data at UI Level while binding the data you can change the Date Format as you wish, by the way are u using GridView to show the records ??

|||Hi You can also refer to http://support.microsoft.com/kb/173907Thanks...Akhil|||

Nvarchar for dates is not recommended. You will not be able to sort by date or perform any date calculations, because your value will be treated as text. The recommended format for dates in Sql Server is YYYY-MM-DD hh:mm:ss. Once you extract a date/time value for display, you can fomat it in any way you want using the String.Format() method. Here are the expressions used to generate custom formats:http://blog.stevex.net/index.php/string-formatting-in-csharp/. The page is for C#, but the expressions are equally valid for VB.

Date Format

Hi everyone, I need help with the date formatting in ssrs 2000. I'm writting reports with date ranges, specific dates, and all are working fine. Now, I was asked to create a report that shows current month, a different one for the QTR, and and also for the current year. I've tried different formats, keep gettin stuck where it doesnt return data. Please help.

Abner

Are you using stored procedure? where do you need a help? in SQL or Report designer, please give me some more details.

|||

I need help in report designer. I was able to run my report and get current dates, GETDATE(), but when it comes to current month, I've tried different ways but it dont work. So, I'm seeking as much help as possible. I'm still new at reporting service, but eager to learn as much as possible.

Thanks advance

Abner

|||

Hi, Abner:

IDoes this meet your needs?

Month(ToDay())

Year(ToDay())

|||

Hi REX,

Thanks for the reply, but I have tried those formats and i just get a blank page. Well, when I type MONTH(TODAY()) it dont reconize today, so I wrote MONTH(GETDATE()), and it just returns a blank page...ive tried it with =DatePart(m, GETDATE()), also =DATEADD(m, 0, GETDATE()), and DatePart("m", field!Promise_Date.Value). and it still just give me a blank page. Is it because I also have it divided by week? even thought I test it without breaking it into weeks. Pleasssssssssseeeeeeeeee help. I dont know how else to do it.

Abner

|||

Hi Abner,

Date related functions in report viewer are not same as we have in dot net, they have some weired behavior, so we cant not use abbreviated Dateparts like "mm", "m" or "d". Check this post for more info on this -

http://blogs.msdn.com/bimusings/archive/2005/09/13/464836.aspx

DATE Conversion

Hello,

I want to take a date/time and convert it to a string of a specific format, like this :

2002-11-18 14:51:30 to '20021118145130'

Since we both work on Oracle and SQL Server, I did this in Oracle :
SELECT to_char(date_field, 'YYYYMMDDHH24MISS') FROM table.

There's got to be a way to do that as simply with SQL Server...

Thanks in advance for the hand.

SvRider.Using Sql Server 2000, I have typically implemented functions that collect and concatenate each portion of the date time (as multiple short varchar strings, appropriatly using '0' placeholders to "pad out" the resulting datetime string correctly).|||Try this:
(convert(char(17),(convert(char(8),getdate(),112) + left(convert(char(12),getdate(),114),2) + substring(convert(char(12),getdate(),114),4,2) + substring(convert(char(12),getdate(),114),7,2) + right(convert(char(12),getdate(),114),3)))).
It's my default value for a varcharfield called timestamp...|||Originally posted by lconsonni
Try this:
(convert(char(17),(convert(char(8),getdate(),112) + left(convert(char(12),getdate(),114),2) + substring(convert(char(12),getdate(),114),4,2) + substring(convert(char(12),getdate(),114),7,2) + right(convert(char(12),getdate(),114),3)))).
It's my default value for a varcharfield called timestamp...

Thanks lconsonni

I came up with this :

CONVERT(CHAR(8),DM_MAJ,112) + LEFT(CONVERT(CHAR(8),DM_MAJ,114),2) + SUBSTRING(CONVERT(CHAR(8), DM_MAJ, 114), 4, 2) + SUBSTRING(CONVERT(CHAR(8), DM_MAJ, 114), 7, 2)

I just thought there would be a more "clean" way to do this. But anyways it works.

Thanks again.|||What you need is to convert the date to ISO format:

CONVERT( DATETIME, your_expression, 112 )|||Give me an example, please...