Converting and Assigning a Date Variable

• vr3357

  SSC Enthusiast

  Points: 187

  More actions

  December 23, 2015 at 11:17 am

  #310438

  Hello,

  I have the following script:

  execute sp_operations123 '2015/12/23'

  If I declare a variable called @today, how can I assign that variable with value shown in the above format?

  Here is example variable declaration.

  DECLARE @today as date = CURRENT_TIMESTAMP

  Here is the date conversion:

  SELECT CONVERT(VARCHAR(10), @today, 111)

  So, my final script execution should look something like this? Also, the current data assignment shown above requires a quote. Do I need the quote as well?

  execute sp_operations123 @today

  Will greatly appreciate your early response.

  Thanks.

  Victor

• ScottPletcher

  SSC Guru

  Points: 101200

  More actions

  December 23, 2015 at 11:28 am

  #1847820

  The internal format of date (or datetime or other temporal data type) is not the same as the character format we humans use. In fact, it's just an integer. SQL converts into a human-favored date string by default in a SELECT statement.

  Thus, if your stored procedure accepts a "date" or "datetime" parameter, you don't need a specific string format, although you can use.

  If you do, the safest format to use is 'YYYYMMDD' because that always works correctly, no matter what the SQL settings are.

  execute dbo.sp_operations123 '20151223'

  Or:

  DECLARE @today date

  SET @today = CURRENT_TIMESTAMP

  execute dbo.sp_operations123 @today

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  December 23, 2015 at 2:09 pm

  #1847843

  vr3357 (12/23/2015)

  ---

  Hello,

  I have the following script:

  execute sp_operations123 '2015/12/23'

  If I declare a variable called @today, how can I assign that variable with value shown in the above format?

  Here is example variable declaration.

  DECLARE @today as date = CURRENT_TIMESTAMP

  Here is the date conversion:

  SELECT CONVERT(VARCHAR(10), @today, 111)

  So, my final script execution should look something like this? Also, the current data assignment shown above requires a quote. Do I need the quote as well?

  execute sp_operations123 @today

  Will greatly appreciate your early response.

  Thanks.

  Victor

  What is the data type of the parameter in sp_operations123 ?

  _____________
  Code for TallyGenerator