Hi all
I have a problem in using date range
If i select a date range,need to get values for proveious years june 1st to may 31st of current year
ex: 02/01/2006 - 02/28/2006(Current)
need to get values for 06/01/2005 - 05/31/2006 date range
could any one help me in writing formula for this
Thanks in advancedoesn't look like real question ,, , pls be specific|||Thanks for your reply
i would like to give you more specific
In my requiremnet if i selected a date range ex:01/06/2006 - 31/06/2006
need to get the values of preveious years of June 1st - current years May 31st
01/06/2005 - 31/05/2006 values should display
actually we have two fields in that in first field need to display the first date range values and in second one need to do like that
We have two fields named
current and year to date
current means date range the user selected
Year to date means data from June 1 of previous year to May 31 st of Current year|||Hi
use this formula
datetime('01-jan-2006')-365
include if condition to handle leap year
Hope it helps|||It's very simple just use this formula in formula field
formula =DateAdd ("yyyy",-1 , {YOUR DATE })
and use this formula wherever you want to use same date of previous year
Best Luck...|||Thanks for your reply
its useful for me
could you pls tell how to take fixed date 1st june to end date 31st may
Showing posts with label values. Show all posts
Showing posts with label values. Show all posts
Thursday, March 29, 2012
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)
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)
Sunday, March 25, 2012
date parmeter converts into drop down
I am working in SSRS 2005. I have three parameters on the reports
Parmeter 1 is Date filter which is drop down and values are MTD,QTD,YTD...
2nd and 3rd parameters are fromdate and todate which are datetime parameters.
When user select let's say MTD from parameter1 then i have Stored procedure which populate the fromdate paramter with 1st date of the month and, todate populate the todays's date. The problem i am facing is the moment these date parameters get populated it converts into drop down. i want these date parameters should be still datetime so user can select the date. The value i am passing to these parameters are datetime type( Now()) .Still the date parameters controls are showing as dropdown. I don't know how to handle it. Please help me if you have faced this kind of problem.
Thanks,
Ranjay
Dropdown appears because parameter's "available values" option is set. In this case, it is not possible to enter value. Try to set default value instead of available values.|||Lev,thanks for your reply. I know that it will work for default but i want to populate the date parameters based on some other parameters selcted in report. Default value just shows one time. it doesn't change the value next time when i change the other parameter.|||Default value can be query based.|||That's true but problem with default value is it's not getting refreshed when my other parameter change. Default value is set only the first time when it initialize. I don't know if i was able to explain the problem properly
Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097549&SiteID=1
date parmeter converts into drop down
I am working in SSRS 2005. I have three parameters on the reports
Parmeter 1 is Date filter which is drop down and values are MTD,QTD,YTD...
2nd and 3rd parameters are fromdate and todate which are datetime parameters.
When user select let's say MTD from parameter1 then i have Stored procedure which populate the fromdate paramter with 1st date of the month and, todate populate the todays's date. The problem i am facing is the moment these date parameters get populated it converts into drop down. i want these date parameters should be still datetime so user can select the date. The value i am passing to these parameters are datetime type( Now()) .Still the date parameters controls are showing as dropdown. I don't know how to handle it. Please help me if you have faced this kind of problem.
Thanks,
Ranjay
Dropdown appears because parameter's "available values" option is set. In this case, it is not possible to enter value. Try to set default value instead of available values.|||Lev,thanks for your reply. I know that it will work for default but i want to populate the date parameters based on some other parameters selcted in report. Default value just shows one time. it doesn't change the value next time when i change the other parameter.|||Default value can be query based.|||That's true but problem with default value is it's not getting refreshed when my other parameter change. Default value is set only the first time when it initialize. I don't know if i was able to explain the problem properly
Check this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097549&SiteID=1
Thursday, March 22, 2012
date parameter problems
Hello Im having a date problem
I have a parameter called @.Param_Daterange with the following values
Last 30 days =Today.AddDays(-30)
Last 60 days =Today.AddDays(-60)
My SQL has a chart which uses the following SQL
SELECT Sum(dbo.tbl_OMDQ_Aggregates.Value) AS SumOfValue, dbo.tbl_OMDQ_Aggregates.status_desc, dbo.tbl_OMDQ_Aggregates.DateRun
FROM dbo.tbl_OMDQ_Aggregates
WHERE (((dbo.tbl_OMDQ_Aggregates.SaleMonth) IN (@.Param_SaleMonth))and (status_desc IN (@.Param_ChurnType))) and dbo.tbl_OMDQ_Aggregates.DateRun > @.Param_DateRange
GROUP BY dbo.tbl_OMDQ_Aggregates.status_desc, dbo.tbl_OMDQ_Aggregates.DateRun;
In visual studio the chart is working fine, however in IE the data getting returned is only a small subset off the entire data, ...my IE language setting is at English Aus, I also created a textbox so i can see what value the daterange parameter is passing and it seems to be in the correct format...the graph is also in the right day/time format of dd/MM/yyyy. However i have no idea which date is getting passed incorrectly, somewhere the date is getting switched to American format however what is outputting on the screen is in the right format...just not the correct records are returning
can anyone please help
Fixed it !!! it wasnt the notorious Amercian format problem after all but rather the daterange parameter had turned into a string datatype for some reason..changed it back and all is well !!!
Date Parameter Default format
Hi,
I would like the default format ("Default values/Non-queried") of a date
parameter to bein the format of: 20-Aug-2004. I have made the parameter a
string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
return 20-8-2004. What can I do to format the "08" to "Aug".
Thank you in advance.You could use
=day(now) & "-" & MonthName(month(now),True) & "-" & year(now)
"Johan" wrote:
> Hi,
> I would like the default format ("Default values/Non-queried") of a date
> parameter to bein the format of: 20-Aug-2004. I have made the parameter a
> string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
> return 20-8-2004. What can I do to format the "08" to "Aug".
> Thank you in advance.
>|||Brilliant! Thanks, it works. Where did you look this up and what code is used
here (C#?)
"Sangfroid" wrote:
> You could use
> =day(now) & "-" & MonthName(month(now),True) & "-" & year(now)
> "Johan" wrote:
> > Hi,
> >
> > I would like the default format ("Default values/Non-queried") of a date
> > parameter to bein the format of: 20-Aug-2004. I have made the parameter a
> > string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
> > return 20-8-2004. What can I do to format the "08" to "Aug".
> >
> > Thank you in advance.
> >|||It is VB code.
Whatever you write in the Expressions tab for any control is basically VB
code.
Go through this link
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp
"Johan" wrote:
> Brilliant! Thanks, it works. Where did you look this up and what code is used
> here (C#?)
> "Sangfroid" wrote:
> > You could use
> > =day(now) & "-" & MonthName(month(now),True) & "-" & year(now)
> >
> > "Johan" wrote:
> >
> > > Hi,
> > >
> > > I would like the default format ("Default values/Non-queried") of a date
> > > parameter to bein the format of: 20-Aug-2004. I have made the parameter a
> > > string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
> > > return 20-8-2004. What can I do to format the "08" to "Aug".
> > >
> > > Thank you in advance.
> > >|||Thanks Ravi!
"Ravi" wrote:
> It is VB code.
> Whatever you write in the Expressions tab for any control is basically VB
> code.
> Go through this link
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp
>
> "Johan" wrote:
> > Brilliant! Thanks, it works. Where did you look this up and what code is used
> > here (C#?)
> >
> > "Sangfroid" wrote:
> >
> > > You could use
> > > =day(now) & "-" & MonthName(month(now),True) & "-" & year(now)
> > >
> > > "Johan" wrote:
> > >
> > > > Hi,
> > > >
> > > > I would like the default format ("Default values/Non-queried") of a date
> > > > parameter to bein the format of: 20-Aug-2004. I have made the parameter a
> > > > string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
> > > > return 20-8-2004. What can I do to format the "08" to "Aug".
> > > >
> > > > Thank you in advance.
> > > >|||Hello.
I have a question about the same thing I think. What is the write way to
write the expression below.
=Datename(Month( Fields!AVG_MONTHLY1_BAL.Value ))
Any help would be appreciated.
Thank you.
"Johan" wrote:
> Thanks Ravi!
> "Ravi" wrote:
> > It is VB code.
> > Whatever you write in the Expressions tab for any control is basically VB
> > code.
> > Go through this link
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp
> >
> >
> > "Johan" wrote:
> >
> > > Brilliant! Thanks, it works. Where did you look this up and what code is used
> > > here (C#?)
> > >
> > > "Sangfroid" wrote:
> > >
> > > > You could use
> > > > =day(now) & "-" & MonthName(month(now),True) & "-" & year(now)
> > > >
> > > > "Johan" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I would like the default format ("Default values/Non-queried") of a date
> > > > > parameter to bein the format of: 20-Aug-2004. I have made the parameter a
> > > > > string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
> > > > > return 20-8-2004. What can I do to format the "08" to "Aug".
> > > > >
> > > > > Thank you in advance.
> > > > >|||Problem fixed. Thak you
"wilerwin" wrote:
> Hello.
> I have a question about the same thing I think. What is the write way to
> write the expression below.
> =Datename(Month( Fields!AVG_MONTHLY1_BAL.Value ))
> Any help would be appreciated.
> Thank you.
> "Johan" wrote:
> > Thanks Ravi!
> >
> > "Ravi" wrote:
> >
> > > It is VB code.
> > > Whatever you write in the Expressions tab for any control is basically VB
> > > code.
> > > Go through this link
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp
> > >
> > >
> > > "Johan" wrote:
> > >
> > > > Brilliant! Thanks, it works. Where did you look this up and what code is used
> > > > here (C#?)
> > > >
> > > > "Sangfroid" wrote:
> > > >
> > > > > You could use
> > > > > =day(now) & "-" & MonthName(month(now),True) & "-" & year(now)
> > > > >
> > > > > "Johan" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I would like the default format ("Default values/Non-queried") of a date
> > > > > > parameter to bein the format of: 20-Aug-2004. I have made the parameter a
> > > > > > string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
> > > > > > return 20-8-2004. What can I do to format the "08" to "Aug".
> > > > > >
> > > > > > Thank you in advance.
> > > > > >
I would like the default format ("Default values/Non-queried") of a date
parameter to bein the format of: 20-Aug-2004. I have made the parameter a
string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
return 20-8-2004. What can I do to format the "08" to "Aug".
Thank you in advance.You could use
=day(now) & "-" & MonthName(month(now),True) & "-" & year(now)
"Johan" wrote:
> Hi,
> I would like the default format ("Default values/Non-queried") of a date
> parameter to bein the format of: 20-Aug-2004. I have made the parameter a
> string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
> return 20-8-2004. What can I do to format the "08" to "Aug".
> Thank you in advance.
>|||Brilliant! Thanks, it works. Where did you look this up and what code is used
here (C#?)
"Sangfroid" wrote:
> You could use
> =day(now) & "-" & MonthName(month(now),True) & "-" & year(now)
> "Johan" wrote:
> > Hi,
> >
> > I would like the default format ("Default values/Non-queried") of a date
> > parameter to bein the format of: 20-Aug-2004. I have made the parameter a
> > string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
> > return 20-8-2004. What can I do to format the "08" to "Aug".
> >
> > Thank you in advance.
> >|||It is VB code.
Whatever you write in the Expressions tab for any control is basically VB
code.
Go through this link
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp
"Johan" wrote:
> Brilliant! Thanks, it works. Where did you look this up and what code is used
> here (C#?)
> "Sangfroid" wrote:
> > You could use
> > =day(now) & "-" & MonthName(month(now),True) & "-" & year(now)
> >
> > "Johan" wrote:
> >
> > > Hi,
> > >
> > > I would like the default format ("Default values/Non-queried") of a date
> > > parameter to bein the format of: 20-Aug-2004. I have made the parameter a
> > > string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
> > > return 20-8-2004. What can I do to format the "08" to "Aug".
> > >
> > > Thank you in advance.
> > >|||Thanks Ravi!
"Ravi" wrote:
> It is VB code.
> Whatever you write in the Expressions tab for any control is basically VB
> code.
> Go through this link
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp
>
> "Johan" wrote:
> > Brilliant! Thanks, it works. Where did you look this up and what code is used
> > here (C#?)
> >
> > "Sangfroid" wrote:
> >
> > > You could use
> > > =day(now) & "-" & MonthName(month(now),True) & "-" & year(now)
> > >
> > > "Johan" wrote:
> > >
> > > > Hi,
> > > >
> > > > I would like the default format ("Default values/Non-queried") of a date
> > > > parameter to bein the format of: 20-Aug-2004. I have made the parameter a
> > > > string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
> > > > return 20-8-2004. What can I do to format the "08" to "Aug".
> > > >
> > > > Thank you in advance.
> > > >|||Hello.
I have a question about the same thing I think. What is the write way to
write the expression below.
=Datename(Month( Fields!AVG_MONTHLY1_BAL.Value ))
Any help would be appreciated.
Thank you.
"Johan" wrote:
> Thanks Ravi!
> "Ravi" wrote:
> > It is VB code.
> > Whatever you write in the Expressions tab for any control is basically VB
> > code.
> > Go through this link
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp
> >
> >
> > "Johan" wrote:
> >
> > > Brilliant! Thanks, it works. Where did you look this up and what code is used
> > > here (C#?)
> > >
> > > "Sangfroid" wrote:
> > >
> > > > You could use
> > > > =day(now) & "-" & MonthName(month(now),True) & "-" & year(now)
> > > >
> > > > "Johan" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I would like the default format ("Default values/Non-queried") of a date
> > > > > parameter to bein the format of: 20-Aug-2004. I have made the parameter a
> > > > > string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
> > > > > return 20-8-2004. What can I do to format the "08" to "Aug".
> > > > >
> > > > > Thank you in advance.
> > > > >|||Problem fixed. Thak you
"wilerwin" wrote:
> Hello.
> I have a question about the same thing I think. What is the write way to
> write the expression below.
> =Datename(Month( Fields!AVG_MONTHLY1_BAL.Value ))
> Any help would be appreciated.
> Thank you.
> "Johan" wrote:
> > Thanks Ravi!
> >
> > "Ravi" wrote:
> >
> > > It is VB code.
> > > Whatever you write in the Expressions tab for any control is basically VB
> > > code.
> > > Go through this link
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp
> > >
> > >
> > > "Johan" wrote:
> > >
> > > > Brilliant! Thanks, it works. Where did you look this up and what code is used
> > > > here (C#?)
> > > >
> > > > "Sangfroid" wrote:
> > > >
> > > > > You could use
> > > > > =day(now) & "-" & MonthName(month(now),True) & "-" & year(now)
> > > > >
> > > > > "Johan" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I would like the default format ("Default values/Non-queried") of a date
> > > > > > parameter to bein the format of: 20-Aug-2004. I have made the parameter a
> > > > > > string type and used: =day(now) & "-" & month(now) & "-" & year(now) but it
> > > > > > return 20-8-2004. What can I do to format the "08" to "Aug".
> > > > > >
> > > > > > Thank you in advance.
> > > > > >
Wednesday, March 21, 2012
date or time cutoff ?
is there a way to cut off the date or time in ms sql.........colum 1 date , colum 2 time...........at the moment it inputs the defaut values for date or time ......is there a way to leave it out so it shows only date or time,,,,,,,,,,,
set date1 = convert(datetime, convert(char(10), getDate(), 101))
i am trying this..............
update cir_temp_user_info
set date1 = convert(datetime, convert(char(10), getDate(), 101))
update cir_temp_user_info
set time1 = convert(datetime, convert(char(10), getDate(), 108))
which is not what i want ........the above is converting the whole colum..........is there a way to use the convert during an insert cmd.??..............i am still trying to trim the date and time colum.......can anybody help
set date1 = convert(datetime, convert(char(10), getDate(), 101))
2) update cir_temp_user_info
set time1 = convert(datetime, convert(char(10), getDate(), 108))
--------
number 1 is kinda working ......it has trim the time off , but its not displaying the correct format for the date.....i want 12/05/2005 but its displaying 12/5/2005 is there a way to change this.............
number 2 is not working at all........i want only the time (hh:mm) but its displaying the default date and secs....is there a way to correct this ...........anyone ??
Thursday, March 8, 2012
Date formats
I'm running the same insert 'insert into jftemp values ('2006/09/11')' on 2
different servers, one saves this date as 11th Sep the other saves it as 9th
Nov. Both servers have the same regional settings, language settings, default
language - both are SQL Server 2000 (SP4). Can someone suggest why the dates
are being stored differently?
thanks, JohnJohn - have a look at the default languages of the Logins you are using
(sp_helplogins) - are they different on each server?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||that's it! Thanks very much Paul, it's obvious when you know the answer!
"Paul Ibison" wrote:
> John - have a look at the default languages of the Logins you are using
> (sp_helplogins) - are they different on each server?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:
>I'm running the same insert 'insert into jftemp values ('2006/09/11')' on 2
>different servers, one saves this date as 11th Sep the other saves it as 9th
>Nov. Both servers have the same regional settings, language settings, default
>language - both are SQL Server 2000 (SP4). Can someone suggest why the dates
>are being stored differently?
>thanks, John
Hi John,
They are not stored differently, the string constant is interpreted in
different ways.
To prevent this in the future, always use one of the unambiguous
formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
--
Hugo Kornelis, SQL Server MVP|||>> (i.e. '20060825' for 11th Sep) <<
This may be non-ambiguous, but it's also very non-intuitive.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:t1uue21q4qa741hvpb898lnv55ennsehs3@.4ax.com...
> On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:
>>I'm running the same insert 'insert into jftemp values ('2006/09/11')' on
>>2
>>different servers, one saves this date as 11th Sep the other saves it as
>>9th
>>Nov. Both servers have the same regional settings, language settings,
>>default
>>language - both are SQL Server 2000 (SP4). Can someone suggest why the
>>dates
>>are being stored differently?
>>thanks, John
> Hi John,
> They are not stored differently, the string constant is interpreted in
> different ways.
> To prevent this in the future, always use one of the unambiguous
> formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
> date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
> milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
> --
> Hugo Kornelis, SQL Server MVP|||On Mon, 28 Aug 2006 15:14:35 -0700, Kalen Delaney wrote:
>> (i.e. '20060825' for 11th Sep) <<
>This may be non-ambiguous, but it's also very non-intuitive.
>:-)
Ouch!
Good catch, Kalen.
--
Hugo Kornelis, SQL Server MVP
different servers, one saves this date as 11th Sep the other saves it as 9th
Nov. Both servers have the same regional settings, language settings, default
language - both are SQL Server 2000 (SP4). Can someone suggest why the dates
are being stored differently?
thanks, JohnJohn - have a look at the default languages of the Logins you are using
(sp_helplogins) - are they different on each server?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||that's it! Thanks very much Paul, it's obvious when you know the answer!
"Paul Ibison" wrote:
> John - have a look at the default languages of the Logins you are using
> (sp_helplogins) - are they different on each server?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:
>I'm running the same insert 'insert into jftemp values ('2006/09/11')' on 2
>different servers, one saves this date as 11th Sep the other saves it as 9th
>Nov. Both servers have the same regional settings, language settings, default
>language - both are SQL Server 2000 (SP4). Can someone suggest why the dates
>are being stored differently?
>thanks, John
Hi John,
They are not stored differently, the string constant is interpreted in
different ways.
To prevent this in the future, always use one of the unambiguous
formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
--
Hugo Kornelis, SQL Server MVP|||>> (i.e. '20060825' for 11th Sep) <<
This may be non-ambiguous, but it's also very non-intuitive.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:t1uue21q4qa741hvpb898lnv55ennsehs3@.4ax.com...
> On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:
>>I'm running the same insert 'insert into jftemp values ('2006/09/11')' on
>>2
>>different servers, one saves this date as 11th Sep the other saves it as
>>9th
>>Nov. Both servers have the same regional settings, language settings,
>>default
>>language - both are SQL Server 2000 (SP4). Can someone suggest why the
>>dates
>>are being stored differently?
>>thanks, John
> Hi John,
> They are not stored differently, the string constant is interpreted in
> different ways.
> To prevent this in the future, always use one of the unambiguous
> formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
> date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
> milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
> --
> Hugo Kornelis, SQL Server MVP|||On Mon, 28 Aug 2006 15:14:35 -0700, Kalen Delaney wrote:
>> (i.e. '20060825' for 11th Sep) <<
>This may be non-ambiguous, but it's also very non-intuitive.
>:-)
Ouch!
Good catch, Kalen.
--
Hugo Kornelis, SQL Server MVP
Date formats
I'm running the same insert 'insert into jftemp values ('2006/09/11')' on 2
different servers, one saves this date as 11th Sep the other saves it as 9th
Nov. Both servers have the same regional settings, language settings, defaul
t
language - both are SQL Server 2000 (SP4). Can someone suggest why the dates
are being stored differently?
thanks, JohnJohn - have a look at the default languages of the Logins you are using
(sp_helplogins) - are they different on each server?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||that's it! Thanks very much Paul, it's obvious when you know the answer!
"Paul Ibison" wrote:
> John - have a look at the default languages of the Logins you are using
> (sp_helplogins) - are they different on each server?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:
>I'm running the same insert 'insert into jftemp values ('2006/09/11')' on 2
>different servers, one saves this date as 11th Sep the other saves it as 9t
h
>Nov. Both servers have the same regional settings, language settings, defau
lt
>language - both are SQL Server 2000 (SP4). Can someone suggest why the date
s
>are being stored differently?
>thanks, John
Hi John,
They are not stored differently, the string constant is interpreted in
different ways.
To prevent this in the future, always use one of the unambiguous
formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
Hugo Kornelis, SQL Server MVP|||>> (i.e. '20060825' for 11th Sep) <<
This may be non-ambiguous, but it's also very non-intuitive.
:-)
HTH
Kalen Delaney, SQL Server MVP
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:t1uue21q4qa741hvpb898lnv55ennsehs3@.
4ax.com...
> On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:
>
> Hi John,
> They are not stored differently, the string constant is interpreted in
> different ways.
> To prevent this in the future, always use one of the unambiguous
> formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
> date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
> milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
> --
> Hugo Kornelis, SQL Server MVP|||On Mon, 28 Aug 2006 15:14:35 -0700, Kalen Delaney wrote:
>This may be non-ambiguous, but it's also very non-intuitive.
>:-)
Ouch!
Good catch, Kalen.
Hugo Kornelis, SQL Server MVP
different servers, one saves this date as 11th Sep the other saves it as 9th
Nov. Both servers have the same regional settings, language settings, defaul
t
language - both are SQL Server 2000 (SP4). Can someone suggest why the dates
are being stored differently?
thanks, JohnJohn - have a look at the default languages of the Logins you are using
(sp_helplogins) - are they different on each server?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||that's it! Thanks very much Paul, it's obvious when you know the answer!
"Paul Ibison" wrote:
> John - have a look at the default languages of the Logins you are using
> (sp_helplogins) - are they different on each server?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:
>I'm running the same insert 'insert into jftemp values ('2006/09/11')' on 2
>different servers, one saves this date as 11th Sep the other saves it as 9t
h
>Nov. Both servers have the same regional settings, language settings, defau
lt
>language - both are SQL Server 2000 (SP4). Can someone suggest why the date
s
>are being stored differently?
>thanks, John
Hi John,
They are not stored differently, the string constant is interpreted in
different ways.
To prevent this in the future, always use one of the unambiguous
formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
Hugo Kornelis, SQL Server MVP|||>> (i.e. '20060825' for 11th Sep) <<
This may be non-ambiguous, but it's also very non-intuitive.
:-)
HTH
Kalen Delaney, SQL Server MVP
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:t1uue21q4qa741hvpb898lnv55ennsehs3@.
4ax.com...
> On Fri, 25 Aug 2006 02:20:01 -0700, John wrote:
>
> Hi John,
> They are not stored differently, the string constant is interpreted in
> different ways.
> To prevent this in the future, always use one of the unambiguous
> formats. For date only, use yyyymmdd (i.e. '20060825' for 11th Sep). For
> date plus time, use yyyy-mm-ddThh:mm:ss, optionally followed by .mmm for
> milliseconds (i.e. '2006-08-25T02:20:01' or '2006-08-25T02:20:01.373').
> --
> Hugo Kornelis, SQL Server MVP|||On Mon, 28 Aug 2006 15:14:35 -0700, Kalen Delaney wrote:
>This may be non-ambiguous, but it's also very non-intuitive.
>:-)
Ouch!
Good catch, Kalen.
Hugo Kornelis, SQL Server MVP
Wednesday, March 7, 2012
Date format - need help!
Hi Guys,
Need some help.
I have a sql script generated in oracle consisting of insert into values and
I'm trying to run that script in SQL Server 2k, but it has a TO_Date
function and I'm unable to run it. I looked on the web for a work around and
didn't come up with anything.
below is a sample script which I'm trying to run in SQL Server. Thanks.
INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME, ATTEND_NAME,
BIRTH_DATE,
MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
SECURITY_LVL ) VALUES (
504210, 2031806529, 1, '361384843', LINDA KAY ', CAST( '03/09/1992 12:00:00
AM', 'MM/DD/YYYY HH:MI:SS AM')
, CAST ( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M', CAST(
'04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 79807613, 9, CAST( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 0);oops sorry, I copied the script I was working around, here is the original
script.
INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME, ATTEND_NAME,
BIRTH_DATE,
MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
SECURITY_LVL ) VALUES (
504210, 2031806529, 1, '361384843', 'LINDA KAY', TO_Date( '03/09/1952
12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M',
TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 79807613, 9, TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS
AM'), 0, 0);
"CoolHandSid" <sid@.
handluke.com> wrote in message
news:eca5bMpGFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hi Guys,
> Need some help.
> I have a sql script generated in oracle consisting of insert into values
and
> I'm trying to run that script in SQL Server 2k, but it has a TO_Date
> function and I'm unable to run it. I looked on the web for a work around
and
> didn't come up with anything.
> below is a sample script which I'm trying to run in SQL Server. Thanks.
> INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME,
ATTEND_NAME,
> BIRTH_DATE,
> MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
> SECURITY_LVL ) VALUES (
> 504210, 2031806529, 1, '361384843', LINDA KAY ', CAST( '03/09/1992
12:00:00
> AM', 'MM/DD/YYYY HH:MI:SS AM')
> , CAST ( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M', CAST(
> '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
> , 79807613, 9, CAST( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM'),
> 0, 0);
>
>|||Use one of the following forms for dates.
'20050131' -- Just the date
'2005-01-31T17:59:00' -- Date/hours/minutes/seconds
'2005-01-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
Note that the 'T' is required with the date and time forms otherwise
the result is sensitive to regional date format settings.
David Portas
SQL Server MVP
--|||David,
Thanks for the reply.
The problem is I have a file with 800,000 lines with TO_Date which was
geneated from a oracle DBA. I'm trying to get that into a table in SQL
Server. Can you suggest some user defined function so I can run it faster?
Thanks.
Again the sample script is as follows,
INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME, ATTEND_NAME,
BIRTH_DATE,
MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
SECURITY_LVL ) VALUES (
504210, 2031806529, 1, '361384843', 'LINDA KAY ', TO_Date( '03/09/1952
12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M',
TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 79807613, 9, TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS
AM'), 0, 0);
--
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109265282.722910.17120@.f14g2000cwb.googlegroups.com...
> Use one of the following forms for dates.
> '20050131' -- Just the date
> '2005-01-31T17:59:00' -- Date/hours/minutes/seconds
> '2005-01-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
> Note that the 'T' is required with the date and time forms otherwise
> the result is sensitive to regional date format settings.
> --
> David Portas
> SQL Server MVP
> --
>|||Not fully tested but it seems to work with what you posted.
CREATE FUNCTION dbo.to_date
(@.dt VARCHAR(50), @.dt_format VARCHAR(50))
RETURNS DATETIME
AS
BEGIN
RETURN
CONVERT(DATETIME,
CONVERT(CHAR(12),
CAST(
SUBSTRING(@.dt,PATINDEX('%YYYY%',@.dt_form
at),4)+
SUBSTRING(@.dt,PATINDEX('%MM%',@.dt_format
),2)+
SUBSTRING(@.dt,PATINDEX('%DD%',@.dt_format
),2) AS DATETIME)
,0)+
SUBSTRING(@.dt,PATINDEX('%HH%',@.dt_format
),2)+':'+
SUBSTRING(@.dt,PATINDEX('%MI%',@.dt_format
),2)+':'+
SUBSTRING(@.dt,PATINDEX('%SS%',@.dt_format
),2)+' '+
SUBSTRING(@.dt,PATINDEX('%AM%',@.dt_format
),2),9)
END
You'll need to search and replace "TO_DATE" with "dbo.to_date" first.
David Portas
SQL Server MVP
--|||David,
Thanks a lot, that worked like a charm.
Thanks!!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109268237.382512.109560@.o13g2000cwo.googlegroups.com...
> Not fully tested but it seems to work with what you posted.
> CREATE FUNCTION dbo.to_date
> (@.dt VARCHAR(50), @.dt_format VARCHAR(50))
> RETURNS DATETIME
> AS
> BEGIN
> RETURN
> CONVERT(DATETIME,
> CONVERT(CHAR(12),
> CAST(
> SUBSTRING(@.dt,PATINDEX('%YYYY%',@.dt_form
at),4)+
> SUBSTRING(@.dt,PATINDEX('%MM%',@.dt_format
),2)+
> SUBSTRING(@.dt,PATINDEX('%DD%',@.dt_format
),2) AS DATETIME)
> ,0)+
> SUBSTRING(@.dt,PATINDEX('%HH%',@.dt_format
),2)+':'+
> SUBSTRING(@.dt,PATINDEX('%MI%',@.dt_format
),2)+':'+
> SUBSTRING(@.dt,PATINDEX('%SS%',@.dt_format
),2)+' '+
> SUBSTRING(@.dt,PATINDEX('%AM%',@.dt_format
),2),9)
> END
> You'll need to search and replace "TO_DATE" with "dbo.to_date" first.
> --
> David Portas
> SQL Server MVP
> --
>
Need some help.
I have a sql script generated in oracle consisting of insert into values and
I'm trying to run that script in SQL Server 2k, but it has a TO_Date
function and I'm unable to run it. I looked on the web for a work around and
didn't come up with anything.
below is a sample script which I'm trying to run in SQL Server. Thanks.
INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME, ATTEND_NAME,
BIRTH_DATE,
MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
SECURITY_LVL ) VALUES (
504210, 2031806529, 1, '361384843', LINDA KAY ', CAST( '03/09/1992 12:00:00
AM', 'MM/DD/YYYY HH:MI:SS AM')
, CAST ( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M', CAST(
'04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 79807613, 9, CAST( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 0);oops sorry, I copied the script I was working around, here is the original
script.
INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME, ATTEND_NAME,
BIRTH_DATE,
MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
SECURITY_LVL ) VALUES (
504210, 2031806529, 1, '361384843', 'LINDA KAY', TO_Date( '03/09/1952
12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M',
TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 79807613, 9, TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS
AM'), 0, 0);
"CoolHandSid" <sid@.
handluke.com> wrote in messagenews:eca5bMpGFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hi Guys,
> Need some help.
> I have a sql script generated in oracle consisting of insert into values
and
> I'm trying to run that script in SQL Server 2k, but it has a TO_Date
> function and I'm unable to run it. I looked on the web for a work around
and
> didn't come up with anything.
> below is a sample script which I'm trying to run in SQL Server. Thanks.
> INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME,
ATTEND_NAME,
> BIRTH_DATE,
> MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
> SECURITY_LVL ) VALUES (
> 504210, 2031806529, 1, '361384843', LINDA KAY ', CAST( '03/09/1992
12:00:00
> AM', 'MM/DD/YYYY HH:MI:SS AM')
> , CAST ( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M', CAST(
> '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
> , 79807613, 9, CAST( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM'),
> 0, 0);
>
>|||Use one of the following forms for dates.
'20050131' -- Just the date
'2005-01-31T17:59:00' -- Date/hours/minutes/seconds
'2005-01-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
Note that the 'T' is required with the date and time forms otherwise
the result is sensitive to regional date format settings.
David Portas
SQL Server MVP
--|||David,
Thanks for the reply.
The problem is I have a file with 800,000 lines with TO_Date which was
geneated from a oracle DBA. I'm trying to get that into a table in SQL
Server. Can you suggest some user defined function so I can run it faster?
Thanks.
Again the sample script is as follows,
INSERT INTO FOLDER ( FLDR_ID, PARENT_ID, COMPANY_ID, FLDR_NAME, ATTEND_NAME,
BIRTH_DATE,
MATRIC_DATE, GENDER, CREATE_DT, USER_ID, STAT_ID, UPDT_DT, PRIVACY_LOCK,
SECURITY_LVL ) VALUES (
504210, 2031806529, 1, '361384843', 'LINDA KAY ', TO_Date( '03/09/1952
12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '05/14/1990 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'M',
TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 79807613, 9, TO_Date( '04/19/1996 09:55:19 AM', 'MM/DD/YYYY HH:MI:SS
AM'), 0, 0);
--
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109265282.722910.17120@.f14g2000cwb.googlegroups.com...
> Use one of the following forms for dates.
> '20050131' -- Just the date
> '2005-01-31T17:59:00' -- Date/hours/minutes/seconds
> '2005-01-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
> Note that the 'T' is required with the date and time forms otherwise
> the result is sensitive to regional date format settings.
> --
> David Portas
> SQL Server MVP
> --
>|||Not fully tested but it seems to work with what you posted.
CREATE FUNCTION dbo.to_date
(@.dt VARCHAR(50), @.dt_format VARCHAR(50))
RETURNS DATETIME
AS
BEGIN
RETURN
CONVERT(DATETIME,
CONVERT(CHAR(12),
CAST(
SUBSTRING(@.dt,PATINDEX('%YYYY%',@.dt_form
at),4)+
SUBSTRING(@.dt,PATINDEX('%MM%',@.dt_format
),2)+
SUBSTRING(@.dt,PATINDEX('%DD%',@.dt_format
),2) AS DATETIME)
,0)+
SUBSTRING(@.dt,PATINDEX('%HH%',@.dt_format
),2)+':'+
SUBSTRING(@.dt,PATINDEX('%MI%',@.dt_format
),2)+':'+
SUBSTRING(@.dt,PATINDEX('%SS%',@.dt_format
),2)+' '+
SUBSTRING(@.dt,PATINDEX('%AM%',@.dt_format
),2),9)
END
You'll need to search and replace "TO_DATE" with "dbo.to_date" first.
David Portas
SQL Server MVP
--|||David,
Thanks a lot, that worked like a charm.
Thanks!!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109268237.382512.109560@.o13g2000cwo.googlegroups.com...
> Not fully tested but it seems to work with what you posted.
> CREATE FUNCTION dbo.to_date
> (@.dt VARCHAR(50), @.dt_format VARCHAR(50))
> RETURNS DATETIME
> AS
> BEGIN
> RETURN
> CONVERT(DATETIME,
> CONVERT(CHAR(12),
> CAST(
> SUBSTRING(@.dt,PATINDEX('%YYYY%',@.dt_form
at),4)+
> SUBSTRING(@.dt,PATINDEX('%MM%',@.dt_format
),2)+
> SUBSTRING(@.dt,PATINDEX('%DD%',@.dt_format
),2) AS DATETIME)
> ,0)+
> SUBSTRING(@.dt,PATINDEX('%HH%',@.dt_format
),2)+':'+
> SUBSTRING(@.dt,PATINDEX('%MI%',@.dt_format
),2)+':'+
> SUBSTRING(@.dt,PATINDEX('%SS%',@.dt_format
),2)+' '+
> SUBSTRING(@.dt,PATINDEX('%AM%',@.dt_format
),2),9)
> END
> You'll need to search and replace "TO_DATE" with "dbo.to_date" first.
> --
> David Portas
> SQL Server MVP
> --
>
Date Format
Hi,
I have a datetime field in my table and whose values are stored as "2004-07-29 00:00:00.000".
I am trying to write a sql statement in query analizer to bring up date as "29/07/2004".
Can anybody please help me.
My sql statement is like this...
select convert(datetime,from_date) from table1.
Thanks in Advance.
JP
hi jp,
use convert function and, convert it to varchar with the formatting number.
in your case it will be 103
Ex:
select convert(Varchar(10), getdate(), 103) 'dt'
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"JP" <john_p@.yahoo.com> wrote in message
news:872CB9EB-188E-43D8-B313-E7160B1E7C84@.microsoft.com...
> Hi,
> I have a datetime field in my table and whose values are stored as
"2004-07-29 00:00:00.000".
> I am trying to write a sql statement in query analizer to bring up date as
"29/07/2004".
> Can anybody please help me.
> My sql statement is like this...
> select convert(datetime,from_date) from table1.
> Thanks in Advance.
> JP
I have a datetime field in my table and whose values are stored as "2004-07-29 00:00:00.000".
I am trying to write a sql statement in query analizer to bring up date as "29/07/2004".
Can anybody please help me.
My sql statement is like this...
select convert(datetime,from_date) from table1.
Thanks in Advance.
JP
hi jp,
use convert function and, convert it to varchar with the formatting number.
in your case it will be 103
Ex:
select convert(Varchar(10), getdate(), 103) 'dt'
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"JP" <john_p@.yahoo.com> wrote in message
news:872CB9EB-188E-43D8-B313-E7160B1E7C84@.microsoft.com...
> Hi,
> I have a datetime field in my table and whose values are stored as
"2004-07-29 00:00:00.000".
> I am trying to write a sql statement in query analizer to bring up date as
"29/07/2004".
> Can anybody please help me.
> My sql statement is like this...
> select convert(datetime,from_date) from table1.
> Thanks in Advance.
> JP
Saturday, February 25, 2012
Date Format
Hi,
I have a datetime field in my table and whose values are stored as "2004-07-29 00:00:00.000".
I am trying to write a sql statement in query analizer to bring up date as "29/07/2004".
Can anybody please help me.
My sql statement is like this...
select convert(datetime,from_date) from table1.
Thanks in Advance.
JPhi jp,
use convert function and, convert it to varchar with the formatting number.
in your case it will be 103
Ex:
select convert(Varchar(10), getdate(), 103) 'dt'
--
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"JP" <john_p@.yahoo.com> wrote in message
news:872CB9EB-188E-43D8-B313-E7160B1E7C84@.microsoft.com...
> Hi,
> I have a datetime field in my table and whose values are stored as
"2004-07-29 00:00:00.000".
> I am trying to write a sql statement in query analizer to bring up date as
"29/07/2004".
> Can anybody please help me.
> My sql statement is like this...
> select convert(datetime,from_date) from table1.
> Thanks in Advance.
> JP
I have a datetime field in my table and whose values are stored as "2004-07-29 00:00:00.000".
I am trying to write a sql statement in query analizer to bring up date as "29/07/2004".
Can anybody please help me.
My sql statement is like this...
select convert(datetime,from_date) from table1.
Thanks in Advance.
JPhi jp,
use convert function and, convert it to varchar with the formatting number.
in your case it will be 103
Ex:
select convert(Varchar(10), getdate(), 103) 'dt'
--
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"JP" <john_p@.yahoo.com> wrote in message
news:872CB9EB-188E-43D8-B313-E7160B1E7C84@.microsoft.com...
> Hi,
> I have a datetime field in my table and whose values are stored as
"2004-07-29 00:00:00.000".
> I am trying to write a sql statement in query analizer to bring up date as
"29/07/2004".
> Can anybody please help me.
> My sql statement is like this...
> select convert(datetime,from_date) from table1.
> Thanks in Advance.
> JP
Date Format
Hi,
I have a datetime field in my table and whose values are stored as "2004-07-
29 00:00:00.000".
I am trying to write a sql statement in query analizer to bring up date as "
29/07/2004".
Can anybody please help me.
My sql statement is like this...
select convert(datetime,from_date) from table1.
Thanks in Advance.
JPhi jp,
use convert function and, convert it to varchar with the formatting number.
in your case it will be 103
Ex:
select convert(Varchar(10), getdate(), 103) 'dt'
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"JP" <john_p@.yahoo.com> wrote in message
news:872CB9EB-188E-43D8-B313-E7160B1E7C84@.microsoft.com...
> Hi,
> I have a datetime field in my table and whose values are stored as
"2004-07-29 00:00:00.000".
> I am trying to write a sql statement in query analizer to bring up date as
"29/07/2004".
> Can anybody please help me.
> My sql statement is like this...
> select convert(datetime,from_date) from table1.
> Thanks in Advance.
> JP
I have a datetime field in my table and whose values are stored as "2004-07-
29 00:00:00.000".
I am trying to write a sql statement in query analizer to bring up date as "
29/07/2004".
Can anybody please help me.
My sql statement is like this...
select convert(datetime,from_date) from table1.
Thanks in Advance.
JPhi jp,
use convert function and, convert it to varchar with the formatting number.
in your case it will be 103
Ex:
select convert(Varchar(10), getdate(), 103) 'dt'
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"JP" <john_p@.yahoo.com> wrote in message
news:872CB9EB-188E-43D8-B313-E7160B1E7C84@.microsoft.com...
> Hi,
> I have a datetime field in my table and whose values are stored as
"2004-07-29 00:00:00.000".
> I am trying to write a sql statement in query analizer to bring up date as
"29/07/2004".
> Can anybody please help me.
> My sql statement is like this...
> select convert(datetime,from_date) from table1.
> Thanks in Advance.
> JP
date format
Hello
I m using a database with MS SQL SERVER 2000, in which I insert
SMALLDATETIME values.While using the query browser, with the option "Use
local setting for display date, money" unchecked , I get results like :
2005-09-24 00:00:00
I m using a database with MS SQL SERVER 2000, in which I insert
SMALLDATETIME values.While using the query browser, with the option "Use
local setting for display date, money" unchecked , I get results like :
2005-09-24 00:00:00
With the option checked : 24/sept./05
I get access to the DB with PHP scripts and I only get values like
"24/sept./05", but I d like to get SMALLDATETIME style values. How could I
do this ?
thanks in advanceDatetime values are stored in a binary format, so you have to decide
how they should be formatted when you retrieve the data. Although you
can use CONVERT() in a query to get a specific format (see Books
Online), it's usually a better idea to do the formatting on the client
- presentation and formatting are really client-side tasks, and it's
easier to handle different client locales etc. See here for more
information about working with datetime data:
http://www.karaszi.com/sqlserver/info_datetime.asp
Simon|||Yes, I understand well what you explain, and this is because I want to
format on the client side that I want the "raw" values. But, in PHP (or
maybe the problem comes from elsewhere, I don't know), the values is
automatically transformed following the local & area settings.("09"
become => "september" or "septembre" or "septembria" or anything else
following the local language )
I didn't found the way to do the same that in "Query Browser , while not
checking the option " use local settings to translate datetime, money
values", so I used the CONVERT() function, but for my usage, it s just
compute time that could have be saved if it was possible to get "raw" data.
I think it s even better than parse and transform the string
"25/sept./05" that I get with PHP function. This script will be used in
several servers and I can't trust the local settings (country ,
languages) of these servers, since, "sept" could be anything else in
other languages.
Finally, I found that it s very very annoying that it is not possible to
get raw values, without having then translated or converted automatically.
Thanks you very much for your answer.
Friday, February 24, 2012
Date Field problem
Hi All,
I have the following query , it is getting values of 6th months also
Actually i want only values between the dates...
nt_date is datetime field
select CONVERT(varchar(10), nt_date, 103) from empdate
where CONVERT(varchar(10), nt_date, 103) between '01/05/2003' and '10/05/2003'
my idea of the above query is that it has to display only
records between 01/05 to 10/05
Thanksyou should do the WHERE comparison in date format, not in string format --
select CONVERT(varchar(10), nt_date, 103)
from empdate
where nt_date between '2003-05-01' and '2003-05-10'
the reason yours wasn't working is because using a string comparison, '09/03/1937' would fall between '01/05/2003' and '10/05/2003'
I have the following query , it is getting values of 6th months also
Actually i want only values between the dates...
nt_date is datetime field
select CONVERT(varchar(10), nt_date, 103) from empdate
where CONVERT(varchar(10), nt_date, 103) between '01/05/2003' and '10/05/2003'
my idea of the above query is that it has to display only
records between 01/05 to 10/05
Thanksyou should do the WHERE comparison in date format, not in string format --
select CONVERT(varchar(10), nt_date, 103)
from empdate
where nt_date between '2003-05-01' and '2003-05-10'
the reason yours wasn't working is because using a string comparison, '09/03/1937' would fall between '01/05/2003' and '10/05/2003'
Tuesday, February 14, 2012
Date between 12 and 12
Hi
I am trying to create a daily report that will only select the values between 12 o' clock last night and 12 o'clock the previous night. I want to see all the data for the previous day. If someone will please be able to help me with this i would be very greatful.
Here is my query, i even tried to use the NOW() function:
I'm pasting a few queries that i tried, maybe just a slight adjustment will do the trick
Code Snippet
Select distinct c.clientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime, SUBSTRING(CONVERT(VARCHAR(25),m.SentDateTime),0,15) as 'NewDate'
from Members m, client c
WHERE c.clientID = m.clientID
AND m.SentDateTime < {fn NOW()}
AND m.Active = 1
Group By c.ClientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime
Order By c.ClientName
Another one i tried
Code Snippet
Select distinct c.clientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime, DateDiff(hh,({fn NOW()}-1),MAX(m.SentDateTime)) as 'NewDate'
from Members m, client c
WHERE c.clientID = m.clientID
AND m.SentDateTime > (GETDATE() - DAY(0.5))
AND m.Active = 1
Group By c.ClientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime
Order By c.ClientName
And this one too. (Although this is from a seperate report.
Code Snippet
SELECT v.ContentId, v.ContentType, COUNT(h.Description) AS Hits, h.Description, DATEPART(yy, v.RenderDate) AS y, DATEPART(dy, v.RenderDate) AS d,
CONVERT(CHAR(12), v.RenderDate, 106) AS date
FROM ViewerPaneRenderHistory AS v INNER JOIN
Members AS M ON v.MemberId = M.MemberID INNER JOIN
HealthBytes AS h ON v.ContentId = h.HealthbyteID
WHERE (v.ContentType = 1)
GROUP BY h.Description, v.ContentType, v.ContentId, DATEPART(yy, v.RenderDate), DATEPART(dy, v.RenderDate), CONVERT(CHAR(12), v.RenderDate, 106)
HAVING (CONVERT(CHAR(12), v.RenderDate, 106) > { fn NOW() } - 1)
ORDER BY Date, COUNT(v.MemberId) DESC
I'v tries a few different ways, but just can't seem to get it right.
Any help would be greatly appreciated.
Kind Regards
Carel Greaves
use the following contion on the where clause...
Code Snippet
ColumnName < Cast(Convert(varchar,@.EndDate,101) as datetime)
and ColumnName >= Cast(Convert(varchar,@.EndDate,101) as datetime) - 1
or
ColumnName < Cast(Convert(varchar,GetDate(),101) as datetime)
and ColumnName >= Cast(Convert(varchar,GetDate(),101) as datetime) - 1
|||
Thanks
Hi,carel:
I am not sure if i known your really means from the upstair threes samples,Following is my reply;
About figure 1:
i think the correct sql is :
Code Snippet
select c.clientname,m.memberid,m.name,m.surname,m.email,m.sentdatetime,SUBSTRING(CONVERT(VARCHAR(25),m.SentDatetime),0,15) as 'NewDate'
from Members as m
inner join client as c
on m.clientID=m.clientID
where m.Active=1
and m.SentDateTime>=Convert(varchar(10),dateadd(dd,-1,getdate()),120)
and m.SentDateTime<Convert(varchar(10),getdate(),120)
i don't know why you use "group by" in the first example.
if you only want previous day's data, i think you could use the condition (Marked with green) only.
NOTE:
the dateformat i used in this example Convert(varchar(10),getdate(),120) returns Date fromat 'YYYY-MM-DD'. and it length is 10
please correct this as your local format, meanwhile you should notice the length.
Subscribe to:
Posts (Atom)