Declaring a date with a format for prompting

[ghbeers]

I have the below stored procedure to which I want to add a date variable (@LAST_DTE) with a format of 'mm/dd/yy'. These @variables are user prompts and I want the user to enter the date in this format.

What is the proper way to format this declared @variable? Thank you.


USE [TmsEPly]
GO
/****** Object: StoredProcedure [dbo].[TEST2_CUST_LOI_BASIC] Script Date: 7/26/2017 8:32:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TEST2_CUST_LOI_BASIC]
@YR_CDE CHAR(4) = NULL,
@TRM_CDE CHAR(2) = NULL,
@LAST_DTE <<<<<<<<<< need formatting for user prompting
AS
if @YR_CDE is not NULL and @TRM_CDE is not null ad @LAST_DTE is not null
SELECT
SPU.id_num,
NM.last_name,
NM.first_name,
etc.,

 

[bborissov]

This is job for your front end, not for SQL Server SP.
Just declare it as Date or Datetime.

Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

I agree with Boris.

To clarify, when thinking about dates in SQL Server, there should not be any concept of "formatting". Internally, sql server stores DateTime as 2 byte integers. This is how the data is stored. Whenever you see a Date or DateTime value, it is always formatted in the display layer. For example, if you are using SQL Server Management Studio and you look at the values in a Date or DateTime column, SQL Server Management Studio will format that date in a certain way. Other applications (including yours) may format the date is other ways.

There are a lot of ways to format dates, and there's lots of ways to interpret dates. The best way to handle dates in SQL Server (and most other languages) is to store the data in a DateTime data type. When passing values to a stored procedure from your front end, you should use a command object. The stored procedures parameter data type should be Date (or DateTime).

By the way, it's usually up to the command object (on the front end) that interprets dates correctly. Presumably, it uses the language settings to do this. For example 1/2/2017 in the UK is interpreted as Feb 1 but in the US, it's interpreted as Jan 2. The command object handles this correctly. You should not try to do this in the stored procedure.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

There's not much to add, so to solve your problem you would rather consult a forum about the frontend you use.

Ideally you can take the user input as a date type in your front end language already, then pass that in as date type parameter and the point to care for the formatting then is when in your frontend the input string in 'mm/dd/yy' format is converted to a C# date variable, for example in a scenario you use an HTML form within an ASP.NET website.

If your front end doesn't even have a date type or isn't capable of parameterizing an ad-hoc SQL requests to MSSQL you'll write an SQL command as a string literal, then you best format your parameter as 'yyyymmdd', this is a string format MSSQL will implicitly (automatically) convert to a date, if you define @LAST_DTE as a T-SQL variable with date as data type.

So something like commandstring="exec [dbo].[TEST2_CUST_LOI_BASIC]('abcd','ab','20170727')" would work, when used as the core command to send to MSSQL for execution. Note: abcd and ab are just reflecting the data type definition of your first two parameters, I don't know their meaning, the advice is just about the string formatting of what to pass to the third parameter @LAST_DTE: '20170727' as 'yyyymmdd' will mean today's date, and that will work no matter how the locale of your frontend or SQL Server is configured. Any frontend language will be capable to reformat user input strings in 'mm/dd/yy' format to 'yyyymmdd'. The ttrickiest part is to turn the two digit year to a date with century, you'll have a turnover value like 50 or above meaning 19yy and 49 or below meaning 20yy or you simply ask for 'mm/dd/yyyy'.

Bye, Olaf.