This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.
convert text type yyyymmdd to date type
In Cr 2008 I have main report (just container for sub reports) and two sub reports that are based on same command.
I am passing Run_Date parameter from main report to those two sub reports.
In each sub I created Run_Date parameter same name as in main report.
Report run but no showing any records empty.
I assume that problem is with my date filed in command:
Part of SQL command script:
Declare @Run_date Date
Declare @Edge_date Date
Set @Run_date ={?Run_Date}
Set @Edge_Date = DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY,0, @Run_Date),0))
-- Range 1:
-- Range1 is range from Run_Date parameter back to previous YTD beging January 01. To Current Parameter full month date e.g. Sep.26.2018
-- Range will be from 2017-01-01 to 2018-09-31.
--Range 2:
-- Range 2 is range from laset YTD relating to Current Run_Date Parameter full month e.g. if Run_Date parameter is Sep.26.2018
-- Range2 will be from 2017-01-01 to 2017-09-31.
Declare @Start_date_Range1 Date
Set @Start_date_Range1 = DATEADD(YEAR, DATEDIFF(YEAR,0,@Run_date)
Declare @End_date_Range1 Date
Set @End_date_Range1 = DateAdd(mm, DateDiff(mm, 0, @Run_date) +1, 0) -- End of Run_Date month this year
Declare @Start_date_Range2 Date
Set @Start_date_Range2 = DATEADD(YEAR, DATEDIFF(YEAR,0,@Run_date)
Declare @End_date_Range2 Date
Set @End_date_Range2 = DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY,0,@Run_date),
In my Selct statement I have
.......
Where convert(datetime,convert(v
in SQL Server database [InvoiceDate] field is (int , not null) type and in this format 20180223
20170904
20171228
..........
I assume that this conversion is not proper one.
I am passing Run_Date parameter from main report to those two sub reports.
In each sub I created Run_Date parameter same name as in main report.
Report run but no showing any records empty.
I assume that problem is with my date filed in command:
Part of SQL command script:
Declare @Run_date Date
Declare @Edge_date Date
Set @Run_date ={?Run_Date}
Set @Edge_Date = DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY,0, @Run_Date),0))
-- Range 1:
-- Range1 is range from Run_Date parameter back to previous YTD beging January 01. To Current Parameter full month date e.g. Sep.26.2018
-- Range will be from 2017-01-01 to 2018-09-31.
--Range 2:
-- Range 2 is range from laset YTD relating to Current Run_Date Parameter full month e.g. if Run_Date parameter is Sep.26.2018
-- Range2 will be from 2017-01-01 to 2017-09-31.
Declare @Start_date_Range1 Date
Set @Start_date_Range1 = DATEADD(YEAR, DATEDIFF(YEAR,0,@Run_date)
Declare @End_date_Range1 Date
Set @End_date_Range1 = DateAdd(mm, DateDiff(mm, 0, @Run_date) +1, 0) -- End of Run_Date month this year
Declare @Start_date_Range2 Date
Set @Start_date_Range2 = DATEADD(YEAR, DATEDIFF(YEAR,0,@Run_date)
Declare @End_date_Range2 Date
Set @End_date_Range2 = DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY,0,@Run_date),
In my Selct statement I have
.......
Where convert(datetime,convert(v
in SQL Server database [InvoiceDate] field is (int , not null) type and in this format 20180223
20170904
20171228
..........
I assume that this conversion is not proper one.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can cast the int to varchar and as long as it is yyyymmdd then it will cast to date
select cast(cast( 20171228 as varchar) as date)
if you have try_cast, use that:
select try_cast(cast( 20171228 as varchar) as date)
or, with convert style 112 for yyyymmdd
select convert(date,cast( 20171228 as varchar),112)
select try_convert(date,cast( 20171228 as varchar),112)
select cast(cast( 20171228 as varchar) as date)
if you have try_cast, use that:
select try_cast(cast( 20171228 as varchar) as date)
or, with convert style 112 for yyyymmdd
select convert(date,cast( 20171228 as varchar),112)
select try_convert(date,cast( 20171228 as varchar),112)
Hi Paul I tried your suggestion select cast the same as it was before report run but no data. I will try now convert to 112
if run this in SQL Server and put hard coded date as;
Set @Run_date = '2018-09-25'
Set @Edge_Date = DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY,0, @Run_Date),0))
-- Range 1:........
....
It run ok and give me records out.
Set @Run_date = '2018-09-25'
Set @Edge_Date = DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY,0, @Run_Date),0))
-- Range 1:........
....
It run ok and give me records out.
You state that the column is an integer, so here it is with an integer column:
with sample as (
select cast(20171228 as int) intcol
)
select try_cast(cast( intcol as varchar) as date)
from sample
This works (and all of them will work)
with sample as (
select cast(20171228 as int) intcol
)
select try_cast(cast( intcol as varchar) as date)
from sample
This works (and all of them will work)
IF you are asking for the REVERSE of this, then calculate the dates for you date ranges and convert those to integers
declare @Run_date date = '2018-09-25'
declare @Edge_Date date = DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY,0, @Run_Date),0))
select cast(convert(varchar,@Edge
declare @Run_date date = '2018-09-25'
declare @Edge_Date date = DATEADD(YEAR, -2, DATEADD(DAY, DATEDIFF(DAY,0, @Run_Date),0))
select cast(convert(varchar,@Edge
To go back to beginning to clarify things.
If I run my sql command without and selection criteria on SQL Server Management Studio I got the “Date” out as 20180928 as date is “int” data type in my Sql Server table.
In my sub report based on the same command I am passing date parameter from main report ?Run_Date.
That date parameter user get by date picker I assume this date is date/time format.
So I assume I have to change one of those types that they could match in sql command – sub reports.
Is your suggestions to change parameter date type to integer (int) like this?
cast(convert(varchar,@Edge
If I run my sql command without and selection criteria on SQL Server Management Studio I got the “Date” out as 20180928 as date is “int” data type in my Sql Server table.
In my sub report based on the same command I am passing date parameter from main report ?Run_Date.
That date parameter user get by date picker I assume this date is date/time format.
So I assume I have to change one of those types that they could match in sql command – sub reports.
Is your suggestions to change parameter date type to integer (int) like this?
cast(convert(varchar,@Edge
Is your suggestions to change parameter date type to integer (int) like this?
cast(convert(varchar,@Edge
Yes.
Typically if dates are stored as integers it will be much more efficient to filter using integers (so that you avoid converting each tow vakue to date).
I dont know what data type is returned by your datepicker.
cast(convert(varchar,@Edge
Yes.
Typically if dates are stored as integers it will be much more efficient to filter using integers (so that you avoid converting each tow vakue to date).
I dont know what data type is returned by your datepicker.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial